I am not using a stored procedure and I am having a couple of problems for some reason. I have a autogenerated primary key and I am trying to get the primary key as a return value. I have
first after ur insert query use SCOPE_IDENTITY() to get auto generated id...
like this...
CREATE PROCEDURE [dbo].[Customer_Insert]
@Name VARCHAR(255),
@Email VARCHAR(255),
@Phone VARCHAR(255),
@CustomerID INT OUTPUT
AS
BEGIN
INSERT INTO dbo.Customer ([Name], Email, Phone)
VALUES (@Name,@Email,@Phone)
SET @CustomerID = CAST(SCOPE_IDENTITY() AS INT)
END
u can receive this....with ur output value..add after command.ExecuteNonQuery();
dim return as string
return =cmd.Parameters("@ORDER_ID").Value
or ...
command.ExecuteNonQuery(); remove and use result =Cmd.ExecuteScalar
A.Venkatesan
Microsoft Certified Professional
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact venkatmca008@gmail.com
seamus1982
Participant
936 Points
375 Posts
SQL Server SQl Statement Return Value
Feb 19, 2012 10:52 AM|LINK
Hi,
I am not using a stored procedure and I am having a couple of problems for some reason. I have a autogenerated primary key and I am trying to get the primary key as a return value. I have
string sqlStatement = "INSERT INTO [CUSTOMER_ORDER] (USER_ID, ORDER_TIME) VALUES (@USER_ID, @ORDER_TIME, @ORDER_ID)";
Then I try to add the orderID which is the primary key but this is where
SqlParameter paramOrderID = new SqlParameter("@ORDER_ID", SqlDbType.Int);
paramOrderID.Direction = ParameterDirection.Output;
command.Parameter.Add(paramOrderID);
conneciton.Open();
command.ExecuteNonQuery();
Any help would be appreciated.
venkatmca008
Participant
1810 Points
341 Posts
Re: SQL Server SQl Statement Return Value
Feb 19, 2012 11:05 AM|LINK
hi....
first after ur insert query use SCOPE_IDENTITY() to get auto generated id...
like this...
CREATE PROCEDURE [dbo].[Customer_Insert] @Name VARCHAR(255), @Email VARCHAR(255), @Phone VARCHAR(255), @CustomerID INT OUTPUT AS BEGIN INSERT INTO dbo.Customer ([Name], Email, Phone) VALUES (@Name,@Email,@Phone) SET @CustomerID = CAST(SCOPE_IDENTITY() AS INT) END u can receive this....with ur output value..add after command.ExecuteNonQuery(); dim return as string return =cmd.Parameters("@ORDER_ID").Value or ... command.ExecuteNonQuery(); remove and use result =Cmd.ExecuteScalaror u can use this..
http://www.dreamincode.net/forums/topic/32421-how-to-obtain-scope-identity-without-using-a-stored-procedure/
Microsoft Certified Professional
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact venkatmca008@gmail.com
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: SQL Server SQl Statement Return Value
Feb 21, 2012 12:36 AM|LINK
Hello seamus1982：）
In my mind，I think you have two ways：
1）Just use select @@identity from xxx and use SqlDataReader+SqlCommand to finish reading the specific value。
2）Since your Order_Id is auto-generated，so plz use select top 1 order_id from xxx order by order_id to read out the single value。
Reguards！