SELECT @@ IDENTITY在访问总是返回0 [英] SELECT @@IDENTITY in Access always returning 0

问题描述

我一直在试图找到一个解决这个问题，但至今毫无效果。

 私人无效插入（）
    {
        字符串ConnectionStringAccess =提供程序= Microsoft.ACE.OLEDB.12.0;数据源= ###喷气OLEDB：数据库密码= ###;
        字符串QUERY2 =选择@@标识;        INT ID = -1;        字符串查询=INSERT INTO tblTable（EMPNO，名称）VALUES（132，'测试名称'）;;        OleDbConnection的CON =新的OleDbConnection（ConnectionStringAccess）;        OleDbCommand的CMD =新的OleDbCommand（查询，CON）;
        尝试
        {
            con.Open（）;
            如果（cmd.ExecuteNonQuery（）== 1）//插入succeded
            {
                cmd.CommandText = QUERY2;
                ID = Convert.ToInt32（cmd.ExecuteScalar（））;
            }
        }
        赶上（异常前）
        {
            //记录前
        }
        最后
        {
            con.Dispose（）;
            con.Close（）;
        }
    }
 

每次我用上面的方法我总是得到0一回在ID。我究竟做错了什么？我尝试使用不同的连接字符串或另一种方式来获得最新的标识符：

• 提供商= Microsoft.Jet.OLEDB.4.0;


• SCOPE_IDENTITY（）

但同样一无所获。在Access数据库是2003或以上（不知道到底）。

解决方案

  

在MS Access数据库是2003或以上（不知道确切）

我能够与Access 97数据库重新创建您的问题。 SELECT @@ IDENTITY 与Access 2000数据库文件（来自同一个OleDbCommand对象的INSERT运行，即使）工作正常，但它总是返回为零时，对一个Access 97运行数据库。

看样子，你需要，如果你想 SELECT @@ IDENTITY 来工作，你的数据库文件升级到新的版本。

I have been trying to find a solution to this problem but so far nothing worked.

private void Insert()
    {
        string ConnectionStringAccess = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=###Jet OLEDB:Database Password=###;
        string query2 = "Select @@Identity";

        int id = -1;

        string Query = "INSERT INTO tblTable (EmpNo, Name) VALUES (132, 'TestName');";

        OleDbConnection con = new OleDbConnection(ConnectionStringAccess);

        OleDbCommand cmd = new OleDbCommand(Query, con);
        try
        {
            con.Open();
            if (cmd.ExecuteNonQuery() == 1)//the insert succeded
            {
                cmd.CommandText = query2;
                id = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
        catch (Exception ex)
        {
            //log the ex
        }
        finally
        {
            con.Dispose();
            con.Close();
        }
    }

Each time I use the above method I always get a return of 0 in "id". What am I doing wrong? I tried using a different connection string or another way to get latest identifier:

• Provider=Microsoft.Jet.OLEDB.4.0;
• SCOPE_IDENTITY()

but again nothing. The Access db is 2003 or older (not sure exactly).

解决方案

The ms access db is 2003 or older (not sure exactly)

I was able to recreate your issue with an Access 97 database. SELECT @@IDENTITY worked correctly with an Access 2000 database file (even when run from the same OleDbCommand object as the INSERT), but it always returned zero when run against an Access 97 database.

It appears that you will need to upgrade your database file to a newer version if you want SELECT @@IDENTITY to work.

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