Solved

Command Parameter Question.

Posted on 2000-04-18
6
167 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 69

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 69

Expert Comment

by:Éric Moreau
ID: 2731379
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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.
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now