capturing value returned from stored procedure 2

[belovedcej]

I have a stored procedure which is essentially a select query with a single parameter.

It is returning a single record in a single field.

I want to take that value and put it into my local variable in Access. Can someone tell me how to do that?

This is how I am executing the stored procedure:

Dim strPermissions As String
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

strPermissions = "CSF"

With cmd
        .ActiveConnection = CurrentProject.BaseConnectionString
        .CommandType = adCmdStoredProc
        .CommandText = "dbo.CSF_get_person_id_sp"
        .Parameters.Refresh
        .Parameters(1) = strPermissions
        

Set rst = .Execute

End With

 

[belovedcej]

To add to this, the stored procedure actually does have a return value. I just don't know how to capture it into my local variable.

 

[danvlas]

Looks like the sp does not return a parameter, but a recordset.

YourVariable=rst.Fields(0)


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[belovedcej]

thanks for that. I knew it was something simple.

How would it be different if my stored procedure had an output parameter? I have a few that do, but have never made use of them.

 

[danvlas]

With cmd
.Execute
YourVariable=.Parameters("ParameterName")
End With

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[belovedcej]

thank you so much! Short, simple, and effective.

 

[belovedcej]

Okay - tried this today, but must be doing something wrong.

here's what I have:

intAddressID = .Parameters("ReturnID")

in my stored procedure, my ReturnID is declared at the beginning like this:

@ReturnID int OUTPUT

Could you advise me? Thanks!

 

[belovedcej]

Nevermind - I got it - I was supposed to include the index of the parameter, not the parameter name.

 

[danvlas]

.Parameters("@ReturnID")

should do...

Sorry, I forgot to add the @ in my previous post...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ronspree]

I've been following this post and I am so glad to learn about passing parameters to and from stored procedures using the command object. What I previously learned from a book was a very long version. My question now is: Is there a more compact version of the execute command? Perhaps something like:

cmd.execute ,@param1IN = 1001 @param2IN = 20, adCmdStoredProc

Will this necessarily return a recordset? Or can I retrieve the output parameter using:

someVariable = cmd.Parameters("@param3OUT")

Thanks.

 

[danvlas]

conn.Execute "SPName(" & Param1 & ", '" & Param2 & "')"

conn is a Connection variable.

SPName is the name of the stored procedure.

Param1 refers to a numeric value of a parameter, Param2 is a string value.

This will return a recordset, I don't know if it can return output parameters.

It is shorter than the 'normal' way, but you lose the error handling.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ronspree]

Dan, I think I'll stick to the command object method. Thanks so much for your replies.