HELP: stored procedure (query) from out vb6 does not work?!

MS Office Access


  • 1. How to track my inventory
    I've searched and searched, can't find what I need. I'm a very basic user, but am willing to write code if it's an easy walkthrough, or cut & paste. Here's my dilemma: I am trying to track our inventory for a wholesale nursery. Plants come in, plants go out, and somehow I'm not able to see what I've got currently in inventory. I've got my Invoices form tied to my products list. I've tried performing a query that would add up all the times a particular item was sold, then subtract that from what I've brought in. I actually know what I'm doing wrong (for a change) but don't know how to do it right. What I've got now is a report that's basically adding every instance of a sale (for example: 100 - 4 sold PLUS 100 - 5 sold). My answer should be 91, but of course, it's adding them up, so I show 191 in inventory. Very frustrating. I'm also trying to decide if I should do an append query to update my total available in the products table? If anyone's got a template they use/like, or can just walk me through how to make the query I'm looking for, I'd greatly appreciate. Thanks.
  • 2. Birthday Query - Find Birthdays for Today
    I have a list of names and birthdays. I'd like to query for who's birthday is today where the database automatically knows today's date - i.e. I don't have to change the query date every day. Can I format the Date() function to only look for day and month?
  • 3. Multiple sort query
    I am working on a database that is basically a paper event form that gets filled out by staff that they want to enter so they can track different events that occur. For ease of them to use it, it is basically a table with a bunch of yes/no boxes in front of different issues, such as med error, accident, etc. They are wanting to be able to sort this report by either a Team Leader, Supervisor, by staff, by individual it occurred too, by type of event, or a combo of these. Is there a simple way to make a form that the query can pull from that you can choose say sort by this team leader and this location, etc (basically being able to choose all your sorting from that one form), and run a query off of it, or is it going to take multiple queries? Thanks
  • 4. Make table query to update other password-protected database
    When I set up a make table query to update a table on another database, I am required to enter the password for the receiving database. However, when I go to run the query, I can't get it to run without first turning off the password on the receiving database. Is there a way to make it unnecessary to turn off the password on the database on which the table is being created?

HELP: stored procedure (query) from out vb6 does not work?!

Postby G.Esmeijer » Wed, 20 Apr 2005 06:12:08 GMT


I would like to access a a query from out a vb6 funtion.
The query in Acees delivers the right result.
however I do not get it working fromout vb6
Who can help?
What is wrong in the code?

The follwoing contains the code and the query

functie findLocations(ByVal locatie as string)

    Dim locatieCode As String

    Dim prm As New ADODB.Parameter
    Dim m_rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command

    locatieCode = UCase(locatie)

    m_rs.CursorLocation = adUseClient

    prm.Direction = adParamInput
    prm.Type = adChar
    prm.Size = 7
    prm.Value = locatieCode
    cmd.Parameters.Append prm

    cmd.ActiveConnection = cnnLOC    '// active connection
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "ZoekLocatie"

    Set m_rs = cmd.Execute

    If m_rs.RecordCount > 0 Then
       ' do something with it
       ' ....

    End If


End  Function

'// this is the query in Access
SELECT Id, Locatie, PinCode, Soort
WHERE LOCATIE Like  [@locatie]
ORDER BY Locatie;

Similar Threads:

1.Update Stored Procedure do not work in Vb but with Query Analyzer

2.Variable not being passed to stored procedure or query

I have been trying to debug parameters on a stored procedure in
classic ASP using VB6 and ADO and as part of this process reduced to
checking on a simple query, but continue to get an error though it
varies depending on the circumstances. I've got similar queries
elsewhere in my code without any errors so not sure where the problem

Anyway, this is one example of when an error is being returned.  Any

    Dim oCmd
    Dim ID
    Set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.CommandText = "select * from els_alias where als_id_num =
    oCmd.ActiveConnection = Application("conn")
    ID = 7379
    oCmd.Parameters.Append oCmd.CreateParameter("@ID", adInteger,
adParamInput, 4, ID)
    oCmd.Parameters.Append oCmd.CreateParameter("@ID", adInteger,
adParamInput, 4, ID)

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Must declare the scalar variable "@ID".

3.Query Analyser and VB6 ADO Stored Procedure UPDATE Difference

Hi All

For the last few days I have been trying to figure out this problem I have
been having.
I have posted a question last week If you would like to read the background
to the problem the subject is below. (Sorry I haven't figured out how to
insert a link to it yet)

SUBJECT : Really tough ADO Stored Procedure Question. Please Help!!!

So after many hours of testing and saving of data and checking I finally
have pin pointed the problem to this one statement.

 UPDATE #tblBatchDataHolder
 SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
 FROM #tblNewData INNER JOIN #tblBatchDataHolder
 ON #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID

When run from Query Analyser works fine but when run through ADO doesn't
work at all.(But there is no error)
So I changed the Syntax to this.

  UPDATE #tblBatchDataHolder
   SET #tblBatchDataHolder.PlainText = #tblNewData.PlainText,
#tblBatchDataHolder.PlainText2 = #tblNewData.PlainText2
   FROM #tblNewData, #tblBatchDataHolder
   WHERE #tblNewData.InstructionID = #tblBatchDataHolder.InstructionID

And I get the same result as before. Fine from QA but Not fine from VB6 ADO
(No Error).

I cannot be sure if the statement is being run successfully and then rolled
back, but that is my impression of what is happening.
There are no errors to suggest other wise.

 So my question is this.

Can any one see any reason why these 2 statements would run successfully in
QA but not from VB6 ADO?
Or why they would not cause an error in the SP to force a rollback of the
entire SP rather than roll back just the last Update.



4.Stored Procedure works in Crystal Reports 8.5 but not in version 9

I am updating a set of reports that use SQL Stored Procedures as their
datasource.  All 10 of them worked well but there is one I cannot get
to work in Version 9.

I should be seeing about 10 fields for the stored procedure in
question but instead I only see one 'Settable Database Options:' which
seems to have no data in it.  I can't really proceed any further than

I am new to Crystal 9.  Has anyone had issues with stored procedures

Thanks ~JAN

5.Stored procedure works in Query Analyser, not ADP

I have the following Sproc that works in QA, but in my ADP as a report
recordsource I get 'provider command for child rowset does not produce
a rowset'.

Now this works if I take out the insert into RequestTracking.

ALTER Procedure [dbo].[usp_OpenClosed_CallRpts_48Hour_Updates]
	@CallID int = Null,
	@Status smallint = Null,
	@SubStatus smallint = Null,
	@DateOpened_Fr datetime = Null,
	@DateOpened_To datetime = Null,
	@DateResolved_Fr datetime = Null,
	@DateResolved_To datetime = Null,
	@ResolvedByEmployee smallint = Null,
	@OpenedBy smallint = Null,
	@AssignedTo smallint = Null,
	@ContactMethod smallint = Null,
	@ClientID smallint = Null,
	@ContactPrefID varchar(7) = Null,
	@BusLineName varchar(40) = Null,
	@ContactName varchar(40) = Null,
	@ActionType smallint = Null,
	@Dept smallint = Null,
	@SystemID smallint = Null,
	@FollowupRequired bit = Null

--=== Strip the time element from dates (just to be safe) by
--	  to whole days and back to a date.  Usually faster than CONVERT.
--	  0 is a date (01/01/1900 00:00:00.000)

	IF @DateOpened_Fr IS NOT NULL
	SELECT @DateOpened_Fr = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_Fr),0)

	IF @DateOpened_To IS NOT NULL
	SELECT @DateOpened_To = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_To),0)

	IF @DateResolved_Fr IS NOT NULL
	SELECT @DateResolved_Fr = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_Fr),0)

	IF @DateResolved_To IS NOT NULL
	SELECT @DateResolved_To = DATEADD(dd,DATEDIFF(dd,0,@DateOpened_To),0)

--=== Track Request
INSERT INTO dbo.RequestTracking
	(RanByUser, RanProc, RanDateTime, CallID, Status, SubStatus,
	DateOpened_To, DateResolved_Fr, DateResolved_To, ResolvedByEmployee,
	AssignedTo, ContactMethod, ClientID, ContactPrefID, BusLineName,
	ActionType, Dept, SystemID, FollowupRequired)
	User, OBJECT_NAME(@@PROCID), getdate(), @CallID, @Status, @SubStatus,
        @DateOpened_To, @DateResolved_Fr, @DateResolved_To,
@ResolvedByEmployee, @OpenedBy,
	@AssignedTo, @ContactMethod, @ClientID, @ContactPrefID, @BusLineName,
	@ContactName, @ActionType, @Dept, @SystemID, @FollowupRequired

--=== Return the results

6. Need help in executing the MySql Stored procedure in VB6

7. Form using stored procedure does not work in Access Runtime

8. Stored Procedure as RecordSource does not work in Access 2002

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 66 guest