Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Command Parameter Question.

Posted on 2000-04-18
6
Medium Priority
?
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 400 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

721 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