Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

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?
0
BALAJI
Asked:
BALAJI
  • 3
  • 2
1 Solution
 
É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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now