Click here to Skip to main content
15,921,028 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!

Should be:

select scope_identity()
 
Share this answer
 
It should be

select scope_identity()
 
Share this answer
 
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.

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