我需要一个在数据库上执行INSERT语句并返回Auto_Increment主键的函数.我有以下C#代码,但是当INSERT语句工作正常(我可以看到数据库中的记录,PK正确生成,行== 1),id值始终为0.任何想法可能会发生错误？
- 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();
- }
为了回答答案,我试过：
- 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;
- }
我正在使用具有OleDb连接的MS Access数据库,Provider = Microsoft.Jet.OLEDB.4.0
解决方法
1)将INSERT和SELECT语句(使用“;”连接)组合成1个db命令
2)使用SCOPE_IDENTITY()而不是@@ IDENTITY
INSERT INTO blabla …; SELECT OID FROM table WHERE OID = SCOPE_IDENTITY()
– 更新：
因为事实证明这个问题与MS ACCESS相关,所以我发现this article表明只需重新使用第一个命令并将其CommandText设置为“SELECT @@ IDENTITY”就足够了.
