?
Solved

ADO Stored Procedures

Posted on 2001-06-14
7
Medium Priority
?
206 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
[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
7 Comments
 
LVL 5

Accepted Solution

by:
gbaren earned 200 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
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.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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 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…
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…
Suggested Courses

649 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