Click here to Skip to main content
15,917,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Please help me to resole this issue. I am using c# 4.0 with sql-express 2008.

My query is: why the scope_identity() function in a SQL Stored Procedure returns a 0?

Code:

<pre>public partial class myfolder_Default3 : System.Web.UI.Page
{
SqlConnection conn;
SqlCommand cmd;

protected void Page_Load(object sender, EventArgs e)
{
conn = new SqlConnection("server=.;database=shikhar;integrated security=true");
cmd = new SqlCommand("insertmycommand", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters[1].Value = 10311;
cmd.Parameters[2].Value = 10000;

cmd.ExecuteNonQuery();
Response.Write(cmd.Parameters[0].Value);
}</pre>

Stored procedure:
<pre>
create proc insertmycommand(@acc_no int, @amt int)
as
begin
insert into account values(@acc_no,@amt)
return scope_identity()
end
</pre>
Posted
Updated 18-Jul-11 2:04am
v3
Comments
Manas Bhardwaj 18-Jul-11 8:03am    
What is scope_identity()? How do we guess what are you doing in there?
R. Giskard Reventlov 18-Jul-11 8:05am    
From MSDN: (scope_identity) Returns the last identity value inserted into an identity column in the same scope.
Manas Bhardwaj 18-Jul-11 8:11am    
ok. thanks!

Option 1:
@acc_no is not an OUTPUT parameter, make it an OUTPUT parameter, and do this in your SP.
SQL
SET @acc_no = SCOPE_IDENTITY()

Also tweak your C# code to make it an output parameter.

Option 2:
Instead of a return statement, use
SQL
SELECT SCOPE_IDENTITY()
in your Stored Procedure and use
C#
cmd.ExecuteScalar() 
in your C# code.
 
Share this answer
 
v2
Comments
shikhar gilhotra 18-Jul-11 8:09am    
NO MY FRIEND , I dont want to use the 'output' keyword in my stored proc....
[no name] 18-Jul-11 14:56pm    
Use Option 2 then.
It should be

select scope_identity()
 
Share this answer
 
Should be:

select scope_identity()
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900