SQL STORED PROC中的scope_identity()函数返回0 [英] scope_identity()function in SQL STORED PROC returns a 0

问题描述

请帮助我重温此问题.我在sql-express 2008中使用c#4.0.

我的查询是:为什么SQL存储过程中的scope_identity()函数返回0?
代码:

< pre>公共局部类myfolder_Default3:System.Web.UI.Page
{
SqlConnection conn;
SqlCommand cmd;

受保护的void Page_Load(对象发送者，EventArgs e)
{
conn = new SqlConnection("server = .; database = shikhar; integrated security = true");
cmd =新的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>

存储过程:
< pre>
创建proc insertmycommand(@acc_no int，@amt int)
如
开始
插入帐户值(@ acc_no，@ amt)
返回scope_identity()
结束
</pre>

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>

推荐答案

应为:

Should be:

select scope_identity()

应为

It should be

select scope_identity()

选项1:
@acc_no 不是OUTPUT参数，请使其成为OUTPUT参数，然后在您的SP中执行此操作.

Option 1:
@acc_no is not an OUTPUT parameter, make it an OUTPUT parameter, and do this in your SP.

SET @acc_no = SCOPE_IDENTITY()

还要调整您的C#代码，使其成为输出参数.

选项2:
代替return语句，使用

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

Option 2:
Instead of a return statement, use

SELECT SCOPE_IDENTITY()

在存储过程中，并在C#代码中使用

in your Stored Procedure and use

cmd.ExecuteScalar() 

.

这篇关于SQL STORED PROC中的scope_identity()函数返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！