STORED PEOC ERROR

[TRYP]

Hi all,

I'm trying to execute a stored procedure using ADODB from an acces ADP file.

here is the stored proc

CREATE PROCEDURE spInsert_People

@FNAME varchar(50),
@LNAME varchar(50),
@DATEADD datetime,
@RET bigint output

as

Insert into PEOPLE (FName,LName,DateAdded)
Values (@FNAME,@LNAME,@DATEADD)

SELECT @RET = scope_Identity()
GO


And here is my VBA Code

Public Sub mySub()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Dim cnStr As String

cnStr = "driver={sql server};" & _
"server=Ra;" & _
"Database=Hornet;UID=sa;PWD=;"

Set conn = New ADODB.Connection

conn.ConnectionString = cnStr

conn.Open

Set cmd = New ADODB.Command

cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spInsert_People"
cmd.Parameters.Refresh


cmd.Parameters.Append cmd.CreateParameter("@FNAME", adVarChar, adParamInput, 50, "SUPER")
cmd.Parameters.Append cmd.CreateParameter("@LNAME", adVarChar, adParamInput, 50, "DUPER")
cmd.Parameters.Append cmd.CreateParameter("@DATEADD", adDBTimeStamp, adParamInput, , Date)
cmd.Parameters.Append cmd.CreateParameter("@RET", adBigInt, adParamInputOutput, , 0)

Set rs = New ADODB.Recordset

rs.Open cmd, , adOpenDynamic, adLockOptimistic

conn.Close

Set rs = Nothing

Set conn = Nothing

Debug.Print "DONE"

End Sub


Code execution hangs on the rs.open line and gives me this message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Prepared statement '(@P1 int OUTPUT,@P2 varchar(50),@P3 varchar(50),@P4 datetime,@P5' expects parameter @P2, which was not supplied.


Any help is utterly appreciated....ive begun pulling my teeth out....

-Tryp

 

[100caratdiamond]

Could be you have declared the output variable as an inputoutput in your parameters you are passing, try adparamoutput

 

[TRYP]

I made that change....
...still get the same error

 

[vongrunt]

What happens if you comment out

[!]'[/!]cmd.Parameters.Refresh

?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[TRYP]

well vongrunt we're now halfway there...lol
the procedure ran without err'ing
however i got no return value..

hmmm...

any ideas uber coders?

thx,
-tryp

 

[vongrunt]

> however i got no return value..

How are you retrieving that output value from stored proc? I don't see related code above... only vanilla sproc call wrapped into ADO/VB stuff :>.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]