?
Solved

ADO Stored Procedures

Posted on 2001-06-14
7
Medium Priority
?
203 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

Technology Partners: 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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 Month7 days, 19 hours left to enroll

765 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