ADO and Command Prameter

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?




 
babygirlsAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
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
 
Anthony PerkinsCommented:
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
 
DanRollinsCommented:
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
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.