locked
MS Access & Select @@Identity RRS feed

  • Question

  • User-323804624 posted

    Hi,

    I'm used MS Access,

    after insert, i want get the Identity value,

    My code's Insert commend success,

    but ID value always 0.

            Dim query As String = "Insert Into Shippers (CompanyName) Values (?)"
            Dim query2 As String = "Select @@Identity"
            Dim ID As Integer
            Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Nwind.mdb"
            Using conn As New OleDbConnection(connect)
                Using cmd As New OleDbCommand(query, conn)
                    cmd.Parameters.AddWithValue("", "test")
                    conn.Open()
                    cmd.ExecuteNonQuery()
                    cmd.CommandText = query2
                    ID = cmd.ExecuteScalar()
                End Using
            End Using
            Me.LabelMessage.Text = String.Format("@@Identity : {0}", ID)


    Monday, January 18, 2010 5:05 AM

Answers

  • User-1659704165 posted

    Hi,

    \http://support.microsoft.com/kb/815629

    chk the abov elink

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2010 5:26 AM
  • User-821857111 posted

    My code's Insert commend success,

    but ID value always 0.

    I don't know why this is. I have just copied and pasted your code as-is, and it works fine for me. Just to be on the safe side, add cmd.Parameters.Clear() before the line where you set the CommandText to query2.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2010 6:02 AM
  • User-1199946673 posted

    The default Northwind sample database does
     

     

    Ah, I missed that is was the Northwind database. Then its should work, maybe a permission problem, but then I would expect an exception!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2010 2:33 PM

All replies

  • User-1659704165 posted

    Hi,

    \http://support.microsoft.com/kb/815629

    chk the abov elink

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2010 5:26 AM
  • User-821857111 posted

    My code's Insert commend success,

    but ID value always 0.

    I don't know why this is. I have just copied and pasted your code as-is, and it works fine for me. Just to be on the safe side, add cmd.Parameters.Clear() before the line where you set the CommandText to query2.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2010 6:02 AM
  • User-1199946673 posted

    It looks good to me also. Just to be sure: Does the Shippers table contain an Identity (autonumber) column? 

    Monday, January 18, 2010 10:43 AM
  • User-821857111 posted

    Does the Shippers table contain an Identity (autonumber) column? 

    The default Northwind sample database does - first thing I checked. But that's a good question.


    Monday, January 18, 2010 2:10 PM
  • User-1199946673 posted

    The default Northwind sample database does
     

     

    Ah, I missed that is was the Northwind database. Then its should work, maybe a permission problem, but then I would expect an exception!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2010 2:33 PM
  • User-323804624 posted

    actuality, this code can work,

    I change a new access,

    it's work!!!

    maybe is northwind database question...

    Tuesday, January 19, 2010 3:40 AM