locked
The best way to Select @@Identity with Access?? RRS feed

  • Question

  • User-1060718374 posted

    Hi, I am running around in circles reading entries regarding Select @@Identity . 

    Does anyone have resouces that explains some of the issues around it?
    How to code it correctly and incorrectly? 

    When you open a connection, run an insert statement, then run the Select @@Identity, how does it understand which table I just inserted a record in?
    Is it possible to retrieve the wrong identity ( aka is Select @@Identity  a solid approach) if so what scenario would this happen in?

    Another question regarding code for this, as indicated below, after the Insert, what is the difference (othey than syntax ;)  ) between setting
    cmd.CommandText = "Select @@Identity"
    versus re-initializing cmd ..
    cmd = New OleDbCommand("Select @@Identity", conn)   ...(same as when the Insert statement is set)

    Would cmd.Parameters.Clear be wise to use in the event that params are used?

    Thanks in advance

    B

     

     

    Dim query As String = "Insert Into User (Name) Values (?)"
    Dim ID As Integer
    Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users.mdb"  
    Dim conn As OleDbConnection = New OleDbConnection(connect)
    Dim cmd As OleDbCommand = New OleDbCommand(query, conn)
    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("", "TEST")
    conn.Open()
    cmd.ExecuteNonQuery()

    '*********
    cmd.CommandText = "Select @@Identity"

    ID = cmd.ExecuteScalar()

     -VS-

    cmd = New OleDbCommand("Select @@Identity", conn)
    ID = cmd.ExecuteScalar()

     

     

     

    Friday, May 7, 2010 12:55 PM

Answers

  • User-821857111 posted

    Does anyone have resouces that explains some of the issues around it?
    How to code it correctly and incorrectly? 

    I wrote this: Getting the identity of the most recently added record

    When you open a connection, run an insert statement, then run the Select @@Identity, how does it understand which table I just inserted a record in?

    It picks up the most recently incremented autonumber that occurred on your connection. It cannot pick the wrong value. Access will only accept one statement per command, so it has to be the right value.

    Another question regarding code for this, as indicated below, after the Insert, what is the difference (othey than syntax ;)  ) between setting
    cmd.CommandText = "Select @@Identity"
    versus re-initializing cmd ..
    cmd = New OleDbCommand("Select @@Identity", conn)   ...(same as when the Insert statement is set)

    Would cmd.Parameters.Clear be wise to use in the event that params are used?

    The second one unnecessarily instantiates a new command object. You only need to use Clear() with parameters if you plan to reuse the command object.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 7, 2010 1:52 PM

All replies

  • User-821857111 posted

    Does anyone have resouces that explains some of the issues around it?
    How to code it correctly and incorrectly? 

    I wrote this: Getting the identity of the most recently added record

    When you open a connection, run an insert statement, then run the Select @@Identity, how does it understand which table I just inserted a record in?

    It picks up the most recently incremented autonumber that occurred on your connection. It cannot pick the wrong value. Access will only accept one statement per command, so it has to be the right value.

    Another question regarding code for this, as indicated below, after the Insert, what is the difference (othey than syntax ;)  ) between setting
    cmd.CommandText = "Select @@Identity"
    versus re-initializing cmd ..
    cmd = New OleDbCommand("Select @@Identity", conn)   ...(same as when the Insert statement is set)

    Would cmd.Parameters.Clear be wise to use in the event that params are used?

    The second one unnecessarily instantiates a new command object. You only need to use Clear() with parameters if you plan to reuse the command object.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 7, 2010 1:52 PM
  • User-1060718374 posted

    Thanks Mike, that clears up alot of questions and concerns. Your article is prety much what I have been trying to create myself .. a code comparison between Access and SQL server

    so given all of this info can I pick your brain on this one ??

    http://forums.asp.net/p/1554396/3824281.aspx#3824281

     

    B

     

     

    Friday, May 7, 2010 8:50 PM