@@identity

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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

Bhavna said:
Hi,

I am having problems with my stored procedure. i keep getting a message
Click to expand...
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)
Click to expand...
VALUES (@Surname, @Forename, @OrgName, @address, @Postcode, @PhoneNo);
 
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!
**********************************************************************
Bhavna said:
Hi,

I am having problems with my stored procedure. i keep getting a message
Click to expand...
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)
Click to expand...
VALUES (@Surname, @Forename, @OrgName, @Address, @Postcode, @PhoneNo);
 
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
 
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

Bhavna said:
Hi Miha,

I have managed to retrieve the @Identity value and place it into the
Click to expand...
current datatable i inserted the new row into.
Dim myIdentity As SqlParameter =
Click to expand...
daClient.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0,
"ClientID")
 
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.
__________________________________

Bhavna said:
Im Sorry Miha i did not fully understand what u meant.
Did u mean that i should create and additional column in the datatable
Click to expand...
that i want to add this Identity value to?
 
Back
Top