trouble running stored proc

[belovedcej]

I have a stored proc (abbreviated here) that works in the query analyzer:

ALTER Procedure dbo.CSF_UPDATE_Address_SP
(
	@Address_ID Integer,
	@Line_1_VC30 Varchar(60),
	@ReturnID1 Int OUTPUT)
AS

exec sco_db.dbo.address_update_sp
        @address_id,
	@Line_1_VC30,
	@ReturnID = @ReturnID1 OUTPUT

Select @returnID1

When I run it, I use the same style I have used for dozens of other procs with return parameters:

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

With cmd
    .ActiveConnection = CurrentProject.BaseConnectionString
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.csf_update_address_SP"
    .Parameters.Refresh
    .Parameters(1) = AddressID
    .Parameters(2) = Address1
   Set rst = .Execute
    
End With
saveAddress = cmd.Parameters(3)

Set rst = Nothing
Set cmd = Nothing

Problem is, when it gets to the execute line, it gives me an "invalid use of null." I assume it is because it wants me to pass a value into the return parameter. I tried that, too, but it told me I was passing in too many parameters.

Does anyone have any thoughts?

 

[PWise]

try

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

With cmd
    .ActiveConnection = CurrentProject.BaseConnectionString
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.csf_update_address_SP"
    .Parameters.Refresh
    [COLOR=red][b].Parameters(0) = AddressID
    .Parameters(1) = Address1
    .Parameters(2) = 0[/b][/color]
   Set rst = .Execute
    
End With
saveAddress = cmd.Parameters(3)

Set rst = Nothing
Set cmd = Nothing

 

[belovedcej]

Thanks - I was able to adapt what you gave me. Changing the index of the parameters won't work - they begin at 1, not zero, but simply adding that one extra worked just fine:

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

With cmd
    .ActiveConnection = CurrentProject.BaseConnectionString
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.csf_update_address_SP"
    .Parameters.Refresh
    .Parameters(1) = AddressID
    .Parameters(2) = Address1
    .Parameters(3) = 0
   Set rst = .Execute
    
End With
saveAddress = cmd.Parameters(3)

Set rst = Nothing
Set cmd = Nothing

 

[belovedcej]

I spoke too soon.

It runs and updates the table now. But the return ID is always 0. I can't get it to actually return the right record.

Do you have any other ideas? Thanks.

 

[belovedcej]

I just did something that works.

Instead of saveAddress = cmd.Parameters(3)
I did saveAddress = rst.Fields(0)

No problem no.

Thanks again!