我需要一个在数据库上执行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”就足够了.
