Databases
StackOverflow2013
Postsdbo
PO@@IDENTITY after INSERT statement always returns 0
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO@@IDENTITY after INSERT statement always returns 0
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAnthony Potts
    1. USGillian
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTDuplicate
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PO@@IDENTITY after INSERT statement always returns 0
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO@@IDENTITY after INSERT statement always returns 0
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO@@IDENTITY after INSERT statement always returns 0
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCould you clarify what database server you're using and possibly inline the references to InitializeAndOpenConnection / connection.CreateCommand as they may affect our answers to you? :)
      singulars
      1. PO@@IDENTITY after INSERT statement always returns 0
      1. USRob
    2. COAlso - the whole "SELECT OID FROM x where OID = SCOPE_IDENTITY()" thins is somewhat over complex as you're saying (for a record inserted with an identity value of 3): "SELECT 3 FROM table_x WHERE 3 = 3" - kinda redundant
      singulars
      1. PO@@IDENTITY after INSERT statement always returns 0
      1. USRob
    3. CO@Rob: it may seem redundant, but OID is typed int, where scope_identity() is not, so you can directly cast (int)cmd.ExecuteScalar()
      singulars
      1. PO@@IDENTITY after INSERT statement always returns 0
      1. USdevio
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload