@@ IDENTITY之后的INSERT语句总是返回0 [英] @@IDENTITY after INSERT statement always returns 0

问题描述

我需要一个对数据库执行INSERT语句并返回Auto_Increment主键的函数。我有以下C＃代码，但是，虽然INSERT语句工作正常（我可以看到数据库中的记录，PK正确生成和行== 1），id值始终为0。任何想法可能会发生错误？

I need a function which executes an INSERT statement on a database and returns the Auto_Increment primary key. I have the following C# code but, while the INSERT statement works fine (I can see the record in the database, the PK is generated correctly and rows == 1), the id value is always 0. Any ideas on what might be going wrong?

    public int ExecuteInsertStatement(string statement)
    {
        InitializeAndOpenConnection();
        int id = -1;


        IDbCommand cmdInsert = connection.CreateCommand();
        cmdInsert.CommandText = statement;
        int rows = cmdInsert.ExecuteNonQuery();

        if (rows == 1)
        {
            IDbCommand cmdId = connection.CreateCommand();
            cmdId.CommandText = "SELECT @@Identity;";
            id = (int)cmdId.ExecuteScalar();
        }

        return id;
    }
    private void InitializeAndOpenConnection()
    {
        if (connection == null)
            connection = OleDbProviderFactory.Instance.CreateConnection(connectString);

        if(connection.State != ConnectionState.Open)                 
            connection.Open();
    }

为了回答问题，我尝试了：

In response to answers, I tried:

public int ExecuteInsertStatement(string statement, string tableName)
    {
        InitializeAndOpenConnection();
        int id = -1;
        IDbCommand cmdInsert = connection.CreateCommand();
        cmdInsert.CommandText = statement + ";SELECT OID FROM " + tableName + " WHERE OID = SCOPE_IDENTITY();";
        id = (int)cmdInsert.ExecuteScalar();

        return id;
    }

但我现在得到错误SQL语句结束后找到的字符

but I'm now getting the error "Characters found after end of SQL statement"

我使用带有OleDb连接的MS Access数据库，Provider = Microsoft.Jet.OLEDB.4.0

I'm using an MS Access database with OleDb connection, Provider=Microsoft.Jet.OLEDB.4.0

推荐答案

1）结合INSERT和SELECT语句（使用;连接）到1 db命令中

1) combine the INSERT and SELECT statement (concatenate using ";") into 1 db command

2）使用SCOPE_IDENTITY （）而不是@@ IDENTITY

2) use SCOPE_IDENTITY() instead of @@IDENTITY

INSERT INTO blabla ...; SELECT OID FROM table WHERE OID = SCOPE_IDENTITY（）

INSERT INTO blabla... ; SELECT OID FROM table WHERE OID = SCOPE_IDENTITY()

- 更新：

问题与MS ACCESS有关，我发现这篇文章，这表明只需重复使用第一个命令并将其CommandText设置为SELECT @@ IDENTITY应该就足够了。

as it turned out that the question was related to MS ACCESS, I found this article which suggests that simply reusing the first command and setting its CommandText to "SELECT @@IDENTITY" should be sufficient.

这篇关于@@ IDENTITY之后的INSERT语句总是返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！