SPROC, Output parameter

[sogibear]

Hi, I'm using a stored procedure that outputs an @@IDENTITY value like so :

ALTER Procedure spClientInsert
@Soscis int,
@firstname varchar(50),
@lastname varchar(50),
@address1 varchar(50),
@address2 varchar(50),
@address3 varchar(50),
@PostCode varchar(50),
@HomePhone varchar(50),
@EmergencyPhone varchar(50),
@MobilePhone varchar(50),
@Fax varchar(50),
@Email varchar(50),
@Gender varchar(50),
@Religion varchar(50),
@EthnicOrigin varchar(50),
@ReferredBy varchar(50),
@FundingAuthority varchar(50),
@Centre varchar(50),
@WheelChair varchar(50),
@Section varchar(50),
@KeyworkerID int,
@ClientID INT OUTPUT
As
SET NOCOUNT ON
INSERT tblClientDetails(Soscis,firstname,lastname, address1,address2,address3, PostCode, Homephone, EmergencyPhone, MobilePhone, Fax, Email, Gender, Religion, EthnicOrigin, ReferredBy,
FundingAuthority, Centre, WheelChair, Section, KeyworkerID)
values (@Soscis,@firstname,@lastname, @address1,@address2,@address3, @PostCode, @Homephone, @EmergencyPhone, @MobilePhone, @Fax, @Email, @Gender, @Religion, @EthnicOrigin,

@ReferredBy, @FundingAuthority, @Centre, @WheelChair, @Section, @KeyworkerID)
SELECT @ClientID = @@IDENTITY

This sproc saves just fine, proving that there is nothing wrong with its syntax, however when i call it from within my VB program, i get this error message :

'-2147217900, [Microsoft][ODBC SQL SERVER DRIVER][SQL SERVER]Procedure 'spClientInsert' expects parameter '@ClientID', which was not supplied '

I have no idea why it seems to think that @ClientID is an INPUT parameter as opposed to an OUTPUT One. Any suggestions would be greatly appreciated

Thanks

 

[SQLSister]

You can try allowing it to have a null initial value:
@ClientID INT = Null OUTPUT

ALso you don't want to use @@identity because it will give you the last identity even if it is out of the scope of this stored procedure. If you have concurrent users this can create problems. You want to use SCOPE_IDENTITY( ) instead which will limit itself to the last identity created by this stored procedure during this particular execution of it.

 

[JamesLean]

Even though it's an output parameter, you still have to add it to the command object's parameters collection from the VB code so it has somewhere to put the returned value. Have you done this?

--James

 

[sogibear]

Hello,
thanks for your help
I'm using the sproc in VB like so ?

Private Sub LoadParametersForInsert(objCommand As ADODB.Command, objClient As clsClient)
On Error GoTo ErrorHandler

Dim objParam As ADODB.Parameter

With objClient
'Load the parameters for the Client object
Set objParam = objCommand.CreateParameter("@Soscis", adInteger, adParamInput, , .Soscis)
objCommand.Parameters.Append objParam
Set objParam = objCommand.CreateParameter("@Firstname", adVarChar, adParamInput, 50, .Firstname)
objCommand.Parameters.Append objParam
Set objParam = objCommand.CreateParameter("@Lastname", adVarChar, adParamInput, 50, .Lastname)
objCommand.Parameters.Append objParam

Set objParam = objCommand.CreateParameter("ClientID", adInteger, adParamOutput)
End With

Exit Sub

ErrorHandler:

Err.Raise 2001, , "Error whilst loading Client Parameters !"
'MsgBox Err.Number, Err.Description

End Sub

 

[JamesLean]

I assume you have included all the other parameters in your actual code? Apart from that, try adding an @ to the output parameter name.

--James

 

[sogibear]

yes i've included all the other parameters in my code, i tried changing the output parameter name to @ClientID but still i get the same error.

The function is actually being called from a COM/.dll object, do you think this might have something to do with it ?

Thanks