@@identity

[Guest]

Hi

I am having problems with my stored procedure. i keep getting a message stating that the stored procedure 'InsClientAndSelIdentity' expects a parameter @Identity

the thing is i have declared it in my sproc below!!! I dont understand what i am doin wrong

Do i need to write anything in my code as well? i.e assign a value to the Identity
I thought that as it is an output parameter i will not have to

Can anyone plz help me

ALTER PROCEDURE dbo.InsClientAndSelIdentit

@Surname char(50)
@Forename char(50)
@OrgName varchar(50)
@Address varchar(300)
@Postcode varchar(50)
@PhoneNo varchar(25)
@Identity int OU

A
SET NOCOUNT OFF
INSERT INTO Client(Surname, Forename, OrgName, Address, Postcode, PhoneNo) VALUES (@Surname, @Forename, @OrgName, @Address, @Postcode, @PhoneNo)
SET @Identity = SCOPE_IDENTITY()
Return

 

[Miha Markic]

Hi,

You have to add a parameter named "@Identity" of direction
ParameterDirection.Output to Parameters collection.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi,

I am having problems with my stored procedure. i keep getting a message

stating that the stored procedure 'InsClientAndSelIdentity' expects a
parameter @Identity.

the thing is i have declared it in my sproc below!!! I dont understand what i am doin wrong?

Do i need to write anything in my code as well? i.e assign a value to the Identity?
I thought that as it is an output parameter i will not have to.

Can anyone plz help me?


ALTER PROCEDURE dbo.InsClientAndSelIdentity
(
@Surname char(50),
@Forename char(50),
@OrgName varchar(50),
@Address varchar(300),
@Postcode varchar(50),
@PhoneNo varchar(25),
@Identity int OUT
)
AS
SET NOCOUNT OFF;
INSERT INTO Client(Surname, Forename, OrgName, Address, Postcode, PhoneNo)

VALUES (@Surname, @Forename, @OrgName, @address, @Postcode, @PhoneNo);

 

[Cowboy \(Gregory A. Beamer\)]

Add an out parameter to the call.

SqlParameter outParam = new SqlParameter("@identity", SqlType.Int);
outParam.Direction = ParameterDirection.Output;

Or, even easier, use the RETURN param automagically added by SQL Server
(parameter 0) by using

RETURN SCOPE_IDENTITY()

in the sproc.

Either way will work. If you are only returning a single numeric, the RETURN
value works rather well. Of course, it is generally used to return error
numbers by many, so the first method may be better if the sproc gets more
complex (uses transactions, et al).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

Hi,

I am having problems with my stored procedure. i keep getting a message

stating that the stored procedure 'InsClientAndSelIdentity' expects a
parameter @Identity.

the thing is i have declared it in my sproc below!!! I dont understand what i am doin wrong?

Do i need to write anything in my code as well? i.e assign a value to the Identity?
I thought that as it is an output parameter i will not have to.

Can anyone plz help me?


ALTER PROCEDURE dbo.InsClientAndSelIdentity
(
@Surname char(50),
@Forename char(50),
@OrgName varchar(50),
@Address varchar(300),
@Postcode varchar(50),
@PhoneNo varchar(25),
@Identity int OUT
)
AS
SET NOCOUNT OFF;
INSERT INTO Client(Surname, Forename, OrgName, Address, Postcode, PhoneNo)

VALUES (@Surname, @Forename, @OrgName, @Address, @Postcode, @PhoneNo);

 

[Guest]

Hi Miha

I have managed to retrieve the @Identity value and place it into the current datatable i inserted the new row into

Dim myIdentity As SqlParameter = daClient.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ClientID"
myIdentity.Direction = ParameterDirection.Outpu

what i want to do now is insert this value into another datatable of mine. How would i do this

 

[Miha Markic]

Hi,

You might add a column named "Identity" to your DataTable (or with another
name and take care of tablemapping).
I think adapter will put there all values from @identity.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi Miha,

I have managed to retrieve the @Identity value and place it into the

current datatable i inserted the new row into.

Dim myIdentity As SqlParameter =

daClient.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0,
"ClientID")

 

[William \(Bill\) Vaughn]

Take a look at my article on handling identity issues
http://www.betav.com/msdn_magazine.htm


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Im Sorry Miha i did not fully understand what u meant.
Did u mean that i should create and additional column in the datatable

that i want to add this Identity value to?