Solved

ADO Stored Procedures

Posted on 2001-06-14
7
196 Views
Last Modified: 2010-05-02
I am having issues running Sp.

Say a basic SP..

MySP(@Parameter varchar(15) as
Declare @ID int

Set @ID = (SELECT ID From myTable where MyField = @Parameter)

Insert LogTable(AField) Values (AValue)

Select @ID as returnValue




Now.. all is fine.. i CAN get the return value.. but i have to do something like this..

oRec.Open "MySP 'Test'"
Set oRec = oRec.NextRecordset
Set oRec = oRec.NextRecordset

Then at this point..i can Reference oRec!ReturnValue.


So the behavor is odd.. but i CAN get results.. the
only issue is that if there is any branching in the SP.. <if then statements>
and there arent equal number of Selects on both sides..then it will crap out my Code.. because I might not have as many Recordsets to scroll through...


I tried just using Command object.. and executing..
and using Parameters.. and just doing a Return @ID. at the end of the SP..  but I never could  get a value back from the Return Parameter.. most of the time it would say.. Parameter doesnt match description.. or somthing..
maybe number of parameters not correct.  

Is there funny behavior to be careful with with the command object?  or Cursor issues?>  Client Cursor.
0
Comment
Question by:TheAnswerMan
7 Comments
 
LVL 5

Accepted Solution

by:
gbaren earned 50 total points
ID: 6191942
This behaviour is by design. Each select returns a recordset, even if it is a record count.

You can change this behaviour using the SET NOCOUNT like this:

SET NOCOUNT ON

select @ID = (SELECT ID From myTable where MyField = @Parameter)

Insert LogTable(AField) Values (AValue)

Select @ID as returnValue

SET NOCOUNT OFF

0
 
LVL 4

Expert Comment

by:nutwiss
ID: 6193194
correction to gbaren's code:

SET NOCOUNT ON

select @ID = (SELECT ID From myTable where MyField = @Parameter)

Insert LogTable(AField) Values (AValue)

SET NOCOUNT OFF

Select @ID as returnValue


because you DO want the complete recoredset of the last select....




0
 
LVL 4

Expert Comment

by:vindevogel
ID: 6193228
I use an ADO.command and use the @Return_value ....

This is a cut & paste from my ExecuteSQL method on a DataClass I build

Basically, the SP takes some parameters and always returns the @Return_Value + the Recordset

Hope you can find what you need in here, because c&pasting a class's code is not that clear.



    On Error GoTo Handler
    Dim vCmdSP As New ADODB.Command
   
    Set vCmdSP.ActiveConnection = mConnection
    vCmdSP.CommandText = aSQL
    vCmdSP.CommandType = aCommandType
   
    vCmdSP.Parameters.Refresh
   
    If Not vCmdSP.Parameters.Count = 0 Then
        Object2Parameters aPRMObject, vCmdSP.Parameters
    End If
   
    vCmdSP.ActiveConnection.Errors.Clear
   
    vCmdSP.Execute
   
    If Not vCmdSP.Parameters.Count = 0 Then
        Parameters2Object aPRMObject, vCmdSP.Parameters
       
        clsIData_ExecuteSQL = vCmdSP.Parameters("@RETURN_VALUE").Value
    End If
   
    Set vCmdSP = Nothing
   
    Exit Function
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 2

Expert Comment

by:TravisHall
ID: 6193277
Regardless of the NOCOUNT option, OUTPUT and RETURN parameters are not returned by the provider until after the last recordset is returned from the stored procedure. Or at least, this is the case for the SQL Server ODBC provider. It may be different for other providers - I haven't seen documentation stating otherwise.

This means you need to keep using the NextRecordset method until there aren't any more recordsets, and only then check your parameters - a most annoying feature, especially if you want to use your parameters to tell you how to interpret your recordsets.

However, you aren't completely reliant on knowing exactly how many recordsets your stored procedure will return. When you get the last recordset, and then do one more .NextRecordset, you will end up with your Recordset variable set to Nothing, and you know that your parameters are then available. So:

oRec.Open "MySP 'Test'"
Do Until oRec Is Nothing
  Set oRec = oRec.NextRecordset
Loop

And now you should be able to get your return value.

Of course, you may want to do something with those recordsets, too, in the loop. When you do, make sure you check the State property of each recordset, because you might get a closed recordset before you get the Nothing. I've been doing something like this recently, but I've been doing it in C++, and I'm not quite certain of the translation to VB at the moment.



0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6331703
It's time to clean up this topic area and that means taking care of this question. Your options at this point are:
 
1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.
 
2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.
 
3.  Ask Community Support to help split points between participating experts.  Just comment here with details.
 
4.  Delete the question because it is of no value to you or to anyone else.  To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.
 
If you elect for option 2, 3 or 4, just post comment with details here and I'll take it from there.  We also request that you review any other open questions you might have and update/close them.  Display all your question history from your Member Profile to view details.
 
PLEASE DO NOT AWARD THE POINTS TO ME.
 
____________________________________________
 
 
 
Hi Experts:
 
In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question.  Likewise, you can also suggest that I PAQ or delete the question.
 
Experts, please do not add further "answer" information to this question.  I will be back in about one week to finalize this question.
 
Thank you everyone.
 
Moondancer :)
Community Support Moderator @ Experts Exchange
 
P.S.  Engineering has been advised about the error in the comment date/time sort order.
0
 
LVL 4

Expert Comment

by:vindevogel
ID: 6471736
Jee, an Answer C ....
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6646019
Grade modified by:
Moondancer :)
Community Support Moderator @ Experts Exchange
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

758 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

18 Experts available now in Live!

Get 1:1 Help Now