title: @@IDENTITY after INSERT statement always returns 0
body: <p>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?</p>
  
  <pre><code>    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();
      }
  </code></pre>
  
  <p>In response to answers, I tried:</p>
  
  <pre><code>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;
      }
  </code></pre>
  
  <p>but I'm now getting the error "Characters found after end of SQL statement"</p>
  
  <p>I'm using an MS Access database with OleDb connection, Provider=Microsoft.Jet.OLEDB.4.0</p>