ADO and stored procedure, pass return value back to ado

[giovi2002]

I would like to pass the return value of a proc back to my ado code and have the value evaluated.
See the following proc and ado code, I can't get the point, Access tells me object missing. My proc works well from query analyzer so i'll first pass the ado code

ADO

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Load


Dim myado As ADODB.Command
Dim rec As Single

Dim intUserID As Integer
Dim strUserObject As String
Dim intUserlevelId As Integer
Dim intSchrijventoegestaan As Integer


Dim stDocName As String

Set myado = New ADODB.Command
myado.ActiveConnection = CurrentProject.Connection
myado.CommandType = adCmdStoredProc
myado.CommandText = "dbo.Qry_Objectuserautorisatie_schrijven"

strUserObject = "subfrmGebruikersbeheer"
intUserID = basLogOn.User.UserID
intUserlevelId = basLogOn.User.UserLevel


myado.Parameters.Append myado.CreateParameter("Return", adInteger, adParamReturnValue)
myado.Parameters.Append myado.CreateParameter("Userid", adInteger, adParamInput, 4, intUserID)
myado.Parameters.Append myado.CreateParameter("Objectnaam", adChar, adParamInput, 50, strUserObject)
myado.Parameters.Append myado.CreateParameter("Userlevelid", adInteger, adParamInput, 4, intUserlevelId)
''myado.Parameters.Append myado.CreateParameter("Schrijventoegestaan", adInteger, adParamOutput, 4, Schrijventoegestaan)

' Execute command
Set rs = cmd.Execute

' Need to close recordset before getting return
' and output parameters.
rs.Close

intSchrijventoegestaan = myado.Parameters("Return").Value

cn.Close

If intSchrijventoegestaan = 1 Then
Me.AllowEdits = True
Me.AllowAdditions = True
End If

Exit_Form_Load:
Exit Sub

Err_Form_Load:

Call Errorhandling.LogError(Err.Number, Err.Description, Me.Name & " " & "subfrmGebruikersbeheer", basLogOn.UserName, True)
Resume Exit_Form_Load

End Sub


--------
CREATE PROCEDURE dbo.Qry_Objectuserautorisatie_schrijven
@Userid integer,
@Objectnaam varchar(50),
@Userlevelid integer
--@Schrijventoegestaan integer OUTPUT

CREATE PROCEDURE dbo.Qry_Objectuserautorisatie_schrijven
AS

IF
(SELECT dbo.Vw_Objectuserautorisatie.schrijven
FROM dbo.Vw_Objectautorisaties INNER JOIN
dbo.Vw_Objectuserautorisatie ON dbo.Vw_Objectautorisaties.Objectid = dbo.Vw_Objectuserautorisatie.objectid
WHERE ((dbo.Vw_Objectuserautorisatie.userid = @Userid) AND
(dbo.Vw_Objectautorisaties.Objectnaam = @Objectnaam)) Or
((dbo.Vw_Objectuserautorisatie.userlevelid = @Userlevelid) And
(dbo.Vw_Objectautorisaties.Objectnaam = @Objectnaam))
) =1
--If statement is true return 1
Begin
RETURN 1
Goto einde_procedure
End

--statement is false return 0
RETURN 0

--Error handling
If @@Error<>0

Begin
raiserror('De database is niet in staat om uw gebruikersrechten voor het schrijven op het formulier te controleren ',16,1)
Goto Einde_Procedure

End

Einde_Procedure:

GO
-------------------

 

[danvlas]

First... your sp does not return a recordset, so this has no meaning:

set rs=cmd.Execute

Then, your command is myado and you do not execute it anywhere (you have cmd.Execute, not myado.Execute)

Then, the parameter names in ADO should contain the @ sign

And finally, if you use the output parameter and execute the sp then you should get your result:

myado.Execute

intSchrijventoegestaan = myado.Parameters("@Schrijventoegestaan")

HTH



Daniel Vlas
Systems Consultant

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

 

[giovi2002]

Thanks daniel for the hint.

In the mean time i found this piece of code from a guy called vadim rapp which is nicer than i posted. I don't have to use the output parameter (because i'll be using a return which is 0 or 1).
Here you see the snippet
>
> For an s.p like this
>
> create PROCEDURE sptest (@p1 int) AS return @p1*2
>
> the VBA code would be
>
> Dim c As New ADODB.Command, Result As Integer
> c.CommandText = "{? = CALL dbo.sptest(?) }"
> c.ActiveConnection = CurrentProject.Connection
> c.Parameters.Refresh
> c.Parameters("@p1") = 3
> c.Execute
> Result = c.Parameters("@return_value")


The nice thing about the call you can extend it to multiple parameters like this:
c.CommandText = "{? = CALL dbo.sptest(?,?,?) }"

 

[danvlas]

Yes, but the error handling in this case is awful.




Daniel Vlas
Systems Consultant

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

 

[giovi2002]

Yes Danvlas but I didn't add the error code in my example to keep the code snippet as small as possible.