Runnigs a Stored Procedure with ADO

Question

Runnigs a Stored Procedure with ADO

zoul

SSC Veteran

Points: 204
More actions
March 17, 2004 at 6:26 am

#86294

I have a headache...
Why?
Because i have a stored procedure that I am trying to run a stored procedure with ADO and keep getting this error:
"Procedure 'procUpdateInvAddress' expects parameted '@Site', which was not supplied."
I've run it throught the debugger in SQL Server and it works as advertised. I also should point out that this is the first time that I am trying to do two operations with a single stored proceedure. It checks to see if a record exists, if it doesn't it adds it, then it updates a different table with the new key value.
Here's the Procedure:
CREATE PROCEDURE dbo.procUpdateInvAddress
(
@Site int,
@Contact int,
@Address int,
@InvAddressPK int=NULL OUTPUT,
@RetCode int=NULL OUTPUT
)
AS
SELECT @InvAddressPK = tblInvAddressPK
FROM         tblInvAddress
WHERE     (tblContactFK = @Contact) AND (tblAddressFK = @Address)
IF @InvAddressPK IS NULL
BEGIN
INSERT INTO dbo.tblInvAddress
                      (tblContactFK, tblAddressFK)
VALUES     (@Contact, @Address)
SELECT @InvAddressPK=@@IDENTITY
UPDATE    dbo.tblSite
SET              tblInvestigatorFK = @InvAddressPK
WHERE     (tblSitePK = @Site)
END
GO
And here's the VB code:
Private Sub UpdateInvAddress()
        Set cmdP = New ADODB.Command
        With cmdP
            .ActiveConnection = ADOCon
            .CommandType = adCmdStoredProc
            .CommandText = "procUpdateInvAddress"
            '@Site int,
            .Parameters.Append .CreateParameter( _
                "@Site", adInteger, adParamInput, gintSite)
            '@Contact int,
            .Parameters.Append .CreateParameter( _
                "@Contact", adInteger, adParamInput, , gintCoInvKey)
            '@Address int,
            .Parameters.Append .CreateParameter( _
                "@Address", adInteger, adParamInput, , intAddress)
            '@InvAddressPK int=NULL OUTPUT,
            .Parameters.Append .CreateParameter( _
                "@InvAddressPK", adInteger, adParamOutput)
            '@RetCode int=NULL OUTPUT
            .Parameters.Append .CreateParameter( _
                "@RetCode", adInteger, adParamOutput)
            .Execute
        End With
        Set cmdP = Nothing

UpdateInvAddress_Exit:
    Exit Sub
UpdateInvAddress_Err:
    If Err.Number = 2465 Then
        Resume Next
    Else
        MsgBox Err.Number & " - " & Err.Description, , "UpdateInvAddress Error"
        GoTo UpdateInvAddress_Exit
    End If
End Sub
TIA
Dennis

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65141 More actions · Answer 1

Check the contents of gintSite to make sure it contains a value. You will get this message if no value is put in the parameter. Can be a result of using a variant that has not been initialised (empty).

Far away is close at hand in the images of elsewhere.
Anon.

zoul SSC Veteran Points: 204 More actions · Answer 2

When I step through the code in VB, all of the parameters have values.

For some reason the value is not being passed to the procedure and I can't figure out why. I figure that it's something simple that I've missed.

zoul SSC Veteran Points: 204 More actions · Answer 3

DOH!

I found the issue. I was missing a comma when i was passing the parameter for @Site.

My Headache has passed....

DanaH1976 Ten Centuries Points: 1046 More actions · Answer 4

Zoul: Just a suggestion: if you use named parameters in VB you're less likely to miss a comma. Example:

.Parameters.Append .CreateParameter(name:="@Site", type:=adInteger, direction:=adParamInput, value:=gintSite)

Dana
Connecticut, USA
Dana