• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

ADO Stored Procedures

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
TheAnswerMan
Asked:
TheAnswerMan
1 Solution
 
gbarenCommented:
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
 
nutwissCommented:
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
 
vindevogelCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
TravisHallCommented:
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
 
MoondancerCommented:
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
 
vindevogelCommented:
Jee, an Answer C ....
0
 
MoondancerCommented:
Grade modified by:
Moondancer :)
Community Support Moderator @ Experts Exchange
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now