[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ADO and Command Prameter

Posted on 2002-06-17
4
Medium Priority
?
238 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 400 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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
Course of the Month19 days, 6 hours left to enroll

834 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