如何从SQL服务器后插入记录的标识值 [英] How to get Identity value from SQL server after insert record

问题描述

我在与身份价值数据库中添加一条记录。我想在插入之后获得标识值。我不希望做到这一点的存储过程。

这是我的code：

 的SqlString =INSERT INTO mytable的;
的SqlString + =（会把Cal1，CAL2，CAL3，CAL4）;
的SqlString + =VALUES（N'{0}'，N'{1}'，N'{2}'，N'{3}'）;
的SqlString =的String.Format（的SqlString，VAL1，VAL2，VAL3，VAL4）;
的SqlString + =声明@ _Identity code INT SET @ _Identity code = @@ IDENTITY返回@ _Identity code;

INT结果;

公众诠释DoCommand2（SQL字符串）
{
        CON =新的SqlConnection（）;
        CMD =新的SqlCommand（）;
        DA =新的SqlDataAdapter（）;
        cmd.Connection = CON;
        da.SelectCommand = CMD;

        字符串CS = GlobalConstants.SqlConnectionString;
        con.ConnectionString = CS;

        cmd.CommandText = SQL;
        INT I = cmd.ExecuteNonQuery（）;
        返回我;
}
 

但我得到这个错误：

  

有返回值return语句不能在这种情况下使用。

解决方案

追加 SELECT SCOPE_IDENTITY（）; 到正常的INSERT语句：

通过将最后串联：

 的SqlString + =SELECT SCOPE_IDENTITY（）;
 

然后检索ID：

 内部ID = Convert.ToInt32（command.ExecuteScalar（））;
 

I add a record in my database with an identity value. I want to get the identity value after inserting. I don't want to do that by stored procedure.

This is my code:

SQLString = " INSERT INTO myTable ";
SQLString += " (Cal1, Cal2, Cal3, Cal4) ";
SQLString += " VALUES(N'{0}',N'{1}',N'{2}',N'{3}') ";
SQLString = string.Format(SQLString, Val1, Val2, Val3, Val4);
SQLString += " Declare @_IdentityCode INT SET @_IdentityCode = @@IDENTITY RETURN @_IdentityCode";

int result;

public int DoCommand2(string sql)
{
        con = new SqlConnection();
        cmd = new SqlCommand();
        da = new SqlDataAdapter();
        cmd.Connection = con;
        da.SelectCommand = cmd;

        string cs = GlobalConstants.SqlConnectionString;
        con.ConnectionString = cs;

        cmd.CommandText = sql;
        int i = cmd.ExecuteNonQuery();
        return i;
}

but I get this error:

A RETURN statement with a return value cannot be used in this context.

解决方案

Append SELECT SCOPE_IDENTITY(); to your normal INSERT statement:

Replace the last concatenation with:

SQLString += "; SELECT SCOPE_IDENTITY();"

Then to retrieve the ID:

int ID = Convert.ToInt32(command.ExecuteScalar());

这篇关于如何从SQL服务器后插入记录的标识值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！