如何在 Visual Studio 2005 中使用scope_identity

Question

如果

我想获取最后插入的身份，将SELECT SCOPE_IDENTITY()语句放在哪里，我将其放入查询中的其他插入中，但一无所获。

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
connection.Open();
string insACmd = "insert into admin (userName, password) values (@userName, @password) SELECT SCOPE_IDENTITY();";
SqlCommand insertAdmin = new SqlCommand(insACmd, connection);
insertAdmin.Parameters.AddWithValue("@userName", TextBox1.Text);
insertAdmin.Parameters.AddWithValue("@password", TextBox2.Text);
string insertedID = insertAdmin.ExecuteScalar().ToString();
connection.Close(); 

谢谢。

Answer 1

试试这个'

选择用户 ID=SCOPE_IDENTITY（）

也就是说，如果您使用用户 ID 作为键

Answer 2

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString))
{
    connection.Open();
    string insACmd = "insert into admin (userName, password) values (@userName, @password); DECLARE @ID INT OUTPUT; SET @ID=SCOPE_IDENTITY()";
    using (SqlCommand insertAdmin = new SqlCommand(insACmd, connection))
    {
        insertAdmin.Parameters.AddWithValue("@userName", TextBox1.Text);
        insertAdmin.Parameters.AddWithValue("@password", TextBox2.Text);
        SqlParameter p = new SqlParameter();
        p.ParameterName = "@ID";
        p.Size = 4;
        p.Direction = ParameterDirection.Output;
        insertAdmin.Parameters.Add(p);
        insertAdmin.ExecuteNonQuery();
        int idOut = (int)p.Value;
        connection.Close();
    }
}