C＃+ SQL服务器的ExecuteScalar（）最后插入的ID不返回 [英] C# + SQL Server ExecuteScalar() not returning last inserted id

问题描述

我有以下函​​数执行一个查询，并在成功时返回真，失败假的。不，我想延长该方法，使每插入查询被解雇，该类变种 insertId 包含最近插入的行的ID。

问题是， insertId 总是0，因此不知何故的ExecuteScalar（）是不返回ID。

任何想法？或其他解决方案得到最后插入查询....

 公众诠释insertId的ID; 
 
公共BOOL executeCommand（串Q）{
&Q + =SELECT @@ IDENTITY AS LastID 
布尔传译= TRUE; 
的SqlConnection myCon =新的SqlConnection（Settings.DSN）; 
的SqlCommand CMD =新的SqlCommand（Q，myCon）; 
尝试{
 cmd.Connection.Open（）; 
 insertId =（INT）cmd.ExecuteScalar（）; 
如果（insertId大于0）{
 MessageBox.Show（insertId.ToString（））; 
} 
 
 myCon.Close（）; 
}赶上（例外前）{
 this.error = ex.Message; 
传译= FALSE; 
} 
返回传译; 
} 
  

解决方案

您应该改变你的插入来插入的那个ID回到你马上（在输出条款）！这从SQL Server 2005上的工作 - 在输出条款不可用在SQL Server 2000或更早版本（没有指定其中的版本 SQL Server的你用你的问题......）。阅读MSDN联机丛书了解 输出条款< 。/ A>

更改您的插入是这样的：

  INSERT INTO dbo.YourTable（COL1，col2的，......，科隆）
输出Inserted.ID 
值（VAL1，VAL2，...，VALN）; 
  

，然后当你执行从C＃INSERT语句中，你应该能够做到：

 使用（的SqlCommand cmdInsert =新的SqlCommand（INSERT .....，myCon））
 {
 myCon.Open（）; 
 VAR的结果= cmdInsert.ExecuteScalar（）; 
 myCon.Close（）; 
} 
  

和您的结果可变现在应该包含正确的，刚插入的值！

I have the following function that executes an query and returns true on success and false on failure. No I wanted to extend the method so that with every insert query that is fired, the class var insertId contains the ID of the last inserted row.

The problem is that insertId is always 0, so somehow the executeScalar() is not returning the ID.

Any ideas? Or other solutions to get the ID of the last insert query....

    public int insertId;        

    public bool executeCommand(string q) {
        q += "; SELECT @@IDENTITY AS LastID";
        bool retour = true;
        SqlConnection myCon = new SqlConnection(Settings.DSN);
        SqlCommand cmd = new SqlCommand(q, myCon);
        try {
            cmd.Connection.Open();
            insertId = (int)cmd.ExecuteScalar();
            if (insertId > 0) {
                MessageBox.Show(insertId.ToString());
            }

            myCon.Close();
        } catch (Exception ex) {
            this.error = ex.Message;
            retour = false;
        }
        return retour;
    }

解决方案

You should change your INSERT to return that inserted ID to you right away (in an OUTPUT clause)! This works from SQL Server 2005 on - the OUTPUT clause isn't available in SQL Server 2000 or earlier (you didn't specify which version of SQL Server you're using in your question...). Read more about the OUTPUT clause on MSDN Books Online.

Change your insert to be something like:

INSERT INTO dbo.YourTable(Col1, Col2, ..., ColN)   
OUTPUT Inserted.ID
VALUES(Val1, Val2, ..., ValN);

and then when you execute your insert statement from C#, you should be able to do:

using(SqlCommand cmdInsert = new SqlCommand("INSERT.....", myCon))
{
   myCon.Open();
   var result = cmdInsert.ExecuteScalar();
   myCon.Close();
}

and your result variable should now contain the proper, freshly inserted value!

这篇关于C＃+ SQL服务器的ExecuteScalar（）最后插入的ID不返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！