Command Parameter Question.

If I have stored procedure that returns a value as an output parameter, that value is only stored in the command object if I use the command's .Execute method. But if I open a recordset that uses the command object for it's parameters and command, only the input parameters are passed to the procedure and the output parameter of the command object never gets filled with the output from the stored procedure.

Is it possible to return an output parameter when using the .Open method of a recordset that uses a command object as its source?
BALAJIAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
The answer is simple: Sorry but you can't!

The Command object is there for that particular reason. Sure you can use the recordset to execute SP that takes input parameters in the SP can also output a recordset. But to have access to an output variable of a Stored Proc, you definetly need a Command object.
0
 
BALAJIAuthor Commented:
But I am using the command object as the source for the recordset, so shouldn't it return the output parameters to the the command object?
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JohnkumarCommented:

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command

Dim objPara As ADODB.Parameter

objConn.Open "DSN", "system", "manager"
'— Indicate that we are using a stored procedure
 objCmd.CommandType = adCmdStoredProc
 '— Specify the name of the stored
 '- procedure
 
objCmd.CommandText = " COUNT_TABLES"
Set objCmd.ActiveConnection = objConn
 
Set objPara = objCmd.CreateParameter ("Return_Value", adInteger, adParamOutput, 2, 0)

 objCmd.Parameters.Append objPara
 Set objPara = Nothing


 Set objPara = objCmd.CreateParameter("puser", adVarChar, adParamInput, 30, txtuser)
 objCmd.Parameters.Append objPara
 Set objPara = Nothing
 
 objCmd.Execute
 
MsgBox "The Number of tables : " & objCmd.Parameters(0).Value

Set objCmd = Nothing
Set objConn = Nothing

I have user ADO 2.0.

0
 
BALAJIAuthor Commented:
Although your example does work, That was the method i currently use to retrive parameters, specificly I asked how do it using the .Open method of a recordset not the .Execute of a Command Object.
0
 
BALAJIAuthor Commented:
thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.