Returned Stored Procedure Value Problem

[ryan]

I must be loosing my mind cause for the life of me I can't get this to work. Here's the deal ... I need to run a stored procedure that can also return a value but heck I can do that just fine, my issue comes when I want to run a stored procedure that takes normal AND output parameters, I can't get it to work ... argggg ;-). I've tried it a couple of ways and when I run a Parameters.Refresh the call will say a parameter is required but it's already there (which is weird). If I leave it out the call runs but I can't get the output parameter. Here's a sample set of some of the code:

Dim oDBCommand
Set oDBCommand = Server.CreateObject("ADODB.Command&quot
oDBCommand.ActiveConnection = Application("DBConnectionString&quot
oDBCommand.CommandType = &H0004
oDBCommand.CommandText = "CreateNewAccount"
oDBCommand.Parameters.Append oDBCommand.CreateParameter("@AccountPlanID", 3, &H0001, 4, iAccountID)
oDBCommand.Parameters.Append oDBCommand.CreateParameter("AccountID", 3, &H0004)

Any help would be great!
Ryan

 

[cmmrfrds]

Perhaps an example will help. Here is a stored procedure I am running with input and output parameters. I am sure there are many ways to do it but they works. You can add a 'return' as another parameter type.

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter
Dim param4 As Parameter, param5 As Parameter

pubCaseCount = 0
pubInvoiceCount = 0

' Connect
Set cnn = CurrentProject.Connection

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

' Set up input parameters.
Set param1 = cmd.CreateParameter("Input", adDouble, adParamInput)
cmd.Parameters.Append param1
param1.Value = pubEmployeeID
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = pubBegDate
Set param3 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param3
param3.Value = pubEndDate

' Set up output parameters.
Set param4 = cmd.CreateParameter("Output", adDouble, adParamOutput)
cmd.Parameters.Append param4
Set param5 = cmd.CreateParameter("Output", adDouble, adParamOutput)
cmd.Parameters.Append param5

' Execute command to run stored procedure
cmd.Execute
pubCaseCount = cmd(3)
pubInvoiceCount = cmd(4)

''Debug.Print " pubCaseCount = "; pubCaseCount

 

[ryan]

cmmrfrds, thanks for the reply. So is your last lines of:

pubCaseCount = cmd(3)
pubInvoiceCount = cmd(4)

actually storing the returned OUTPUT parameter? I'll try this and see if it works.

Thanks,
Ryan