scope_identity()function in SQL STORED PROC returns a 0

Question

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 18-Jul-11 2:01am

shikhar gilhotra

Updated 18-Jul-11 2:04am

Ramalinga Koushik

v3

Add a Solution

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!

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Shameel · Answer 1 · 2011-07-18T02:07:00

Solution 3

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.

Posted 18-Jul-11 2:07am

Shameel

Updated 18-Jul-11 2:08am

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.

Ramalinga Koushik · Answer 2 · 2011-07-18T02:05:00

Solution 2

It should be

select scope_identity()

Posted 18-Jul-11 2:05am

Ramalinga Koushik

**R. Giskard Reventlov** · Answer 3 · 2011-07-18T02:04:00

Solution 1

Should be:

select scope_identity()

Posted 18-Jul-11 2:04am

R. Giskard Reventlov