×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Retrieving a recordset from a stored procedure

Retrieving a recordset from a stored procedure

Retrieving a recordset from a stored procedure

(OP)
I am having a problem retrieving a record from a stored procedure.  I am new to this, and have had success retrieving an output parameter, but now I want to retrieve an entire record.

The procedure I am trying to use is defined as...

CODE


ALTER PROCEDURE dbo.aspnet_Membership_GetUserByUserId
    @UserId               uniqueidentifier,
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    IF ( @UpdateLastActivity = 1 )
    BEGIN
        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        FROM     dbo.aspnet_Users
        WHERE    @UserId = UserId

        IF ( @@ROWCOUNT = 0 ) -- User ID not found
            RETURN -1
    END

    SELECT  m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate, m.LastLoginDate, u.LastActivityDate,
            m.LastPasswordChangedDate, u.UserName, m.IsLockedOut,
            m.LastLockoutDate
    FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   @UserId = u.UserId AND u.UserId = m.UserId

    IF ( @@ROWCOUNT = 0 ) -- User ID not found
       RETURN -1

    RETURN 0
END

The VB code within MS-Access looks something like...

CODE

Dim adoCmd As New ADODB.Command
Dim rsReturned As New ADODB.Recordset
With adoCmd
      .ActiveConnection = CurrentProject.Connection
      .CommandText = "aspnet_Membership_GetUserByUserId"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@UserID", adGUID, adParamInput, , Me.aspnet_UserID)
      .Parameters.Append .CreateParameter("@CurrentTimeUtc", adDate, adParamInput, , Now())
      .Parameters.Append .CreateParameter("@UpdateLastActivity", adBoolean, adParamInput, , 1)
      Set rsReturned = .Execute
End With

The recordset rsReturned seems to be closed after the procedure is executed (like maybe no records are returned), but I am pretty sure with the data I feed it at run-time a record should be returned.

I would also like to know how to gain acccess to the value that is RETURNed at the end of the stored procedure (the 0 or -1 depending on @@ROWCOUNT).  That might help me know that a record is being SELECTED inside the stored procedure.

RE: Retrieving a recordset from a stored procedure

The Default properties of a ADODB.Command are the parameters
so you can use

adoCmd(0)'RETURN
adoCmd(1)'UserId  
...

or
adoCmd("@RETURN_VALUE")'RETURN
adoCmd("@UserId")'UserId
....
              

RE: Retrieving a recordset from a stored procedure

(OP)
There are 3 available parameters which I can access.  They are...

adoCmd(0) which is the same as adoCmd("@UserId")
adoCmd(1) which is the same as adoCmd("@CurrentTimeUtc")
adoCmd(2) which is the same as adoCmd("@UpdateLastActivity")

But those are the parameters I submitted to the procedure, so being able to retrieve them back, doesn't do me much good.

That still leaves me with the same question I had before.  I don't seem to be getting back a recordset, and I don't know how to access the RETURN value.

I can tell from the updated record in the dbo.aspnet_Users table that the first half of the stored procedure is running properly and my input paramaters are being accepted properly.

If I call the stored procedure in Query Analyzer (rather than through my application) I get back a record for my input UserID.

If I try to access adoCmd("@RETURN_VALUE") as suggested above, it produces a run-time error saying the item cannot be found in the collection.

RE: Retrieving a recordset from a stored procedure

this is code that i use and it returns cmd.("@RETURN_VALUE")

CODE

Option Compare Database
Option Explicit
Public cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Function InitializeAdo()
If cnn.State = adStateClosed Then
    
    cnn.ConnectionTimeout = 0
    cnn.Open CurrentProject.Connection
End If
End Function

Function ExecuteAdoRS(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Recordset
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim a As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
    
    cmd.Parameters.Item(Prams) = AdoPrams(Prams)
    'Debug.Print cmd.Parameters.Item(Prams).Name; cmd.Parameters.Item(Prams).Value
Next Prams

Set ExecuteAdoRS = cmd.Execute(a)
End Function

this is how i call this function

CODE

Sub sss()
Dim rs As ADODB.Recordset

Set rs = ExecuteAdoRS("test", 4, 0)
End Sub

RE: Retrieving a recordset from a stored procedure

what version of sql are you using

RE: Retrieving a recordset from a stored procedure

(OP)
It says version 8 (which I think is 2000).

Your example doesn't show the code for the stored procedure, but I would guess it has a line something to the effect...

@RETURN_VALUE    nvarchar(256) OUTPUT

Which would define why you have access to a return value called @RETURN_VALUE

The stored procedure that I have to deal with in this case does not have such a parameter defined, but still uses the lines "RETURN -1" and "RETURN 0" towards the end, so I was wondering if I could access those values somehow.  It looks like if there should be a recordset returned by the procedure the returned value would = 0, otherwise it would = -1.

Is it possible... that since the RETURN 0 and RETURN -1 lines come after the SELECT statement, that what is actually being returned to my application is not the result from the SELECT statment (a recordset) but rather
the integer value 0 or -1?

For other stored procedures that I have, there is an OUTPUT parameter defined, which then I can access.  This stored procedure does not have such an OUTPUT parameter defined.  I would prefer not to modify the procedure as it is not mine.

RE: Retrieving a recordset from a stored procedure

this is my stored procedure

CODE

CREATE proc test
as
begin
Select * from Students
IF ( @@ROWCOUNT > 0 ) -- User ID not found
       RETURN -1

    RETURN 0
end
i am also using sql 2000 every sp has a bulit in return value that has a default value of 0

RE: Retrieving a recordset from a stored procedure

did yuo try my code and still
"get item cannot be found in the collection. "

RE: Retrieving a recordset from a stored procedure

(OP)
Well..... If I add the line...

SET NOCOUNT ON

to the stored procedure, it appears to now return a recordset... which is all I was trying to do in the first place.  So now my question is, what does SET NOCOUNT ON do to the stored procedure that otherwise that line should not be there.

FYI... even though it works (as far as returning a recordset), I still can't access a parameter called @RETURN_VALUE.  But, when I create a simple stored procedure that does not define any parameters (like your example) then there is such a parameter returned.
 

RE: Retrieving a recordset from a stored procedure

i changed the procedure  to and still can access the @RETURN_VALUE
[code]
ALTER proc test
@testpram int=0
as
begin
Select * from Students
IF ( @@ROWCOUNT > 0 ) -- User ID not found
       RETURN -1

    RETURN 0
end
[/code

RE: Retrieving a recordset from a stored procedure

changed to

CODE

CREATE proc test
@testpram int=0
as
begin
set nocount off
Select * from Students
IF ( @@ROWCOUNT > 0 ) -- User ID not found
       RETURN -1

    RETURN 0
end

still returns record set and still can access  @RETURN_VALUE

RE: Retrieving a recordset from a stored procedure

The return parameter must be the first one in the parameters collection and indicate the type.

 .Parameters.Append .CreateParameter("@ret", adInteger, adParamReturnValue)

Also, you may need to process and close the recordset before referencing the Return Value.

rsReturned.Close
Print.Debug "return value = "; @ret

The insert statement in your stored procedure will generate informational recordset(s) which can be suppressed with the "Set nocount on". This is the standard way to get rid of the extra recordsets.

You can actually loop through the recordsets in ADO, but this is a lot of extra work and unnecessary.
rsReturned = rsReturned.NextRecordset




Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close