Stored procedure, no return code returned

[sharonc]

I'm using SQL 7.0 with VB 6.0, Service Pack 5. I'm calling a stored procedure from SQL with VB. I want the stored procedure to return the return code, but the return code comes back empty. I can walk through the stored procedure and see that it is creating a -1 for the return code, but the VB program isn't showing a value of -1 for the return code. How do I retrieve the value of @RC?

My Code:
Dim strConnString As New ADODB.Connection
Dim cmdTemp As New ADODB.Command
Dim params As ADODB.Parameters
Dim objErr As ADODB.Error

Set strConnString = New ADODB.Connection

strConnString.Open _
"Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Hazard;Data Source=SEIMS", txtUser, txtPass
strConnString.CursorLocation = adUseServer

Set cmdTemp.ActiveConnection = strConnString
cmdTemp.CommandText = "sp_LoadDMRMisc"
cmdTemp.CommandType = adCmdStoredProc
Set params = cmdTemp.Parameters

params.Append cmdTemp.CreateParameter("RC", adInteger, adParamReturnValue)
params.Append cmdTemp.CreateParameter("@Month", 2, adParamInput, 0)
params.Append cmdTemp.CreateParameter("@Year", 2, adParamInput, 0)

params("@Month&quot = iMthSelect
params("@Year&quot = txtYear

cmdTemp.Execute

If cmdTemp(&quot;RC&quot <> 0 Or iErrorCode <> 0 Or iMthSelect = 0 Then
Msg = &quot;Error... Record not added to the database.&quot;
Style = vbOKOnly
Title = &quot;Record NOT Added&quot;
Response = MsgBox(Msg, Style, Title)
End If

Procedure_Exit:
Set cmdTemp = Nothing
strConnString.Close
Set strConnString = Nothing
Exit Sub

My stored procedure:
create procedure sp_LoadDMRMisc
@Month SmallInt, @Year SmallInt
as

Declare @RC Integer

Begin
Begin Transaction
Select @RC = 0
insert into DMRMisc (Month, Year)
values (@Month, @Year)

if @@error <> 0
Begin
RAISERROR (50002,10,1) -- record not added to the database
Rollback Tran
Select @RC = -1
Return @RC
end
Else
Begin
Select @RC = 0
Return @RC
End

Commit Transaction
End

 

[mukund]

Try this out.

create procedure sp_LoadDMRMisc
@RC, @Month SmallInt, @Year SmallInt
as

Don't Declare @RC as a local parameter.
It is a output parameter, in the declaration of the
stored proc. itslelf.

Also, just assign the value to @RC, no need of

Return @RC statement.

Mukund.

 

[sharonc]

Thank you for the information. I will give it a try.

 

[saj]

Just change the
strConnString.CursorLocation = adUseServer to
adUseclient.

Might work!!

 

[sharonc]

I tried both Mukund's way and changing to aduseclient. Neither give me the value of the return code. Any other suggestions?