Solved

ADO and Command Prameter

Posted on 2002-06-17
4
210 Views
Last Modified: 2013-11-23
Dearest Expert,

Any one have idea on how can i get the output parameter from my store procedure by using command parameter and recordset at the same time?
can refer to the MSDN article where article Id = Q167908
Currently, i m using this method :
'=========================================================
'Return parameters from the stored procedures.......
'Error Code and Error Message......
adoCommand.Parameters.Append adoCommand.CreateParameter("io_err_code", adVarChar, adParamOutput, 20)
adoCommand.Parameters.Append adoCommand.CreateParameter("io_err_msg", adVarChar, adParamOutput, 200)

'Execute the command
Set adoRs = adoCommand.Execute

sErrCode = adoCommand.Parameters("io_err_code")
sErrMsg = adoCommand.Parameters("io_err_msg")
//PS: my sErrCode and ErrMsg is keep return me empty record.
If im using

adoCommand.Execute

Then is able to return me the ErrMsg and ErrCode.
But, the problem is, i also need to view out all the record and display it one by one frm SQL Database. Without Recordset, i cant view the record.

here the slictly description of my error encountered:
'*******************************************************
PRB: Output Parameters Wrong after ADO Command.Execute Call
Last reviewed: May 30, 1997
Article ID: Q167908  
The information in this article applies to:
ActiveX Data Objects (ADO) included with: - Microsoft Active Server Pages, versions 1.0, 1.0b - Microsoft Transaction Server 1.0 - Microsoft Visual C++, 32-bit Editions, versions 4.2, 5.0 - Microsoft Visual InterDev, version 1.0 - Microsoft Visual Basic Professional and Enterprise Editions for
Windows, versions 4.0, 5.0

- Microsoft Visual J++, versions 1.0, 1.1


SYMPTOMS
When calling a stored procedure on SQL Server 6.5 using the ADO Command.Execute method, output parameters aren't returned.



CAUSE
The current version of the SQL Server ODBC driver (2.65.0240) returns output parameters as the last packet it sends back to the client. The ODBC driver is implemented such that an application must process through all of the result sets returned by the procedure before the output parameters are filled. For more information, see the following article in the Microsoft Knowledge Base:


ARTICLE-ID: Q152174
TITLE:      
INFO: Output Parameters, Return Codes and the ODBC Driver


RESOLUTION
If you are using Visual Basic and you are receiving a recordset back from the Execute call, set the recordset to "Nothing" and then retrieve the results of the output parameters. Here is a Visual Basic example of what the code might look like:


   Dim Conn1 As Connection
   Dim Com1  As Command
   Dim Param1 As Parameter
   Dim rs As Recordset

   Set Conn1 = CreateObject("ADODB.Connection")
   Set Com1 = CreateObject("ADODB.Command")

   Conn1.ConnectionString = "Data Source=MyDataSource;
        PWD=;UID=sa;Database=pubs"
   Conn1.Open

   Com1.ActiveConnection = Conn1
   Com1.CommandText = "{call ParamTest(?)}"

   Set Param1 = Com1.CreateParameter(, adInteger, adParamOutput)
   Com1.Parameters.Append Param1
   Set Param1 = Nothing

   Set rs = Com1.Execute()

   'Free the recordset
   Set rs = Nothing

   ' display result
   Debug.Print Com1.Parameters(0)
'*********************************************************
I try the above example, but is still failed...
any idea?




 
0
Comment
Question by:babygirls
[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
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 7083816
The simple answer is there is no practical way to do this.  In my futile attempts at doing this (including setting the CursorLocation to adUseClient), it was either one or the other.  I ended up rewriting the StoredProcedure to return multiple recordsets.

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7083822
By the way, this may not be the answer you expected or wanted, however do not shoot the messenger.  For the record:
Last 10 Grades Given B A B B B A B A B A  

Anthony
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7911063
Hi babygirls,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept acperkins@devx's comment(s) as an answer.

babygirls, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.
==========
DanRollins -- EE database cleanup volunteer
0
 

Expert Comment

by:SpideyMod
ID: 7967646
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

688 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