Using Stored Procedure to Return a Value in VBA 1

[TheBull369]

What I am trying to do is allow a user to enter a order number into a form. When they click on Search the form will query the database and return a 1 if the order was placed and a 0 if the order was not placed.

What is the easiest way of doing this? I have a stored procedure that will see if the record exists or not but I can not get Access to see the returned value and do anything with it.

Thanks

John

 

[cmmrfrds]

I am assuming you have an output parameter in the stored procedure that you want to return. First, set "no count on" in the stored procedure otherwise it may generate an empty resultset. If it returns a resultset you need to read that before the output parameter becomes available, but the nocount on will take care of that. The value will be in the output parameter after you execute the stored procedure. If you need code to execute the SP then post back with that request.

 

[TheBull369]

Yes, I need the code to run the stored procedure and to receive the variable that comes back from the stored procedure. That would be great!

Thanks!

 

[cmmrfrds]

Here is the sample code.
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, param4 As Parameter
begDate = #10/1/2001#
endDate = #10/31/2001#

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn
Set param1 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = begDate
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate
Set param3 = cmd.CreateParameter("Input", adSmallInt, adParamInput)
cmd.Parameters.Append param3
param3.Value = 3
Set param4 = cmd.CreateParameter("Input", adVarchar, adParamOutput, 20)
cmd.Parameters.Append param4
param4.Value = "XX"

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_employeeRevenueProgress"
cmd.CommandType = adCmdStoredProc

' Execute command to run stored procedure
cmd.Execute

''Set rst = cmd.Execute
'' Can return a recordset if desired.

debug.print "4th parm = " cmd(3)
'' can reference by name or position. Also, text parm needs the length defined.

 

[TheBull369]

So with the following Stored Procedure (I am only sending one parameter), I can get a count of the number of records which meet the criteria and return that number to the adp.

In the adp I would basically say myvar = cmd(3) to retrieve the count I sent to the database?

CREATE PROCEDURE sp_cntCheese
@sanum char(10)
AS
Declare @var1 nvarchar(5)
Select @var1 = '0'
Select @var1 = (Select Count(*) From Cheese Where SalesNo = @sanum)

Return @var1
GO

 

[TheBull369]

Thank you for your help I got it working with that!

 

[cmmrfrds]

CREATE PROCEDURE sp_cntCheese
@sanum char(10), '- input parm default
@out1 varchar(10) output
AS
Declare @var1 nvarchar(5)
Select @var1 = '0'
Select @out1 = (Select Count(*) From Cheese Where SalesNo = @sanum)

------------
You can select directly into the in/out parameter and the values is available in the client.
If using "return code" then it must be the first parm in the parm list. That is why the example you show works.
In the above example @out1 is the 2nd parm and on the client side you could access by using
cmd(1) since this starts at relative address 0 in the list.