Solved

Command Parameter Question.

Posted on 2000-04-18
6
168 Views
Last Modified: 2013-12-25
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
Comment
Question by:BALAJI
  • 3
  • 2
6 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 100 total points
ID: 2728958
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
 

Author Comment

by:BALAJI
ID: 2731344
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2731379
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Expert Comment

by:Johnkumar
ID: 2753737

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
 

Author Comment

by:BALAJI
ID: 2756050
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
 

Author Comment

by:BALAJI
ID: 2756052
thanks.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question