Solved

ADO and Command Prameter

Posted on 2002-06-17
4
194 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
  • 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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 …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
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…

747 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

11 Experts available now in Live!

Get 1:1 Help Now