How do I get the value of a recently insterted autonumber in the database

Question

madao 0 Newbie Poster

14 Years Ago

I would like to retrieve the ID number for that row that was just inserted into the database.

Dim Enrollment As New Enrollments  
        With Enrollment  
            .Student_ID = CInt(lvwStudents.FocusedItem.Text)  
            .Enrollment_Date = CDate(dtpDate.Text)  
        End With  
        EnrollmentBAL.Save(Enrollment)

I have this code to save it from the database, what I want to do is after saving it I want to get the value of its ID and pass it to another table. can anyone help me on how to this or whats the best way to this. Thanks

.net:vb.net mysql

6 Contributors
11 Replies
278 Views
23 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by kingsonprisonic

Dani AI

Generated 9 Months Ago

Short answer: have the data-access layer return the generated ID immediately after the INSERT (or populate the Enrollment instance and return it). That makes the intent explicit and avoids fragile workarounds like SELECT MAX(...). and showed MAX/TOP techniques — they can appear to work but are race-prone. was right to push parameterized commands; ensure the correct parameter type class is used for the provider (OleDb uses OleDbType, SqlClient uses SqlDbType).

For MySQL (Connector/NET) the cleanest options are either read the command's LastInsertedId property after ExecuteNonQuery, or run SELECT LAST_INSERT_ID() on the same connection immediately. Both are connection-scoped and safe for multi-client use; note LAST_INSERT_ID() behavior with multi-row inserts (it returns the first generated value). Example (MySql.Data):

Using conn As New MySqlConnection(connString)
  conn.Open()
  Using cmd As New MySqlCommand("INSERT INTO Enrollments (Student_ID, Enrollment_Date) VALUES (@s,@d);", conn)
    cmd.Parameters.AddWithValue("@s", Enrollment.Student_ID)
    cmd.Parameters.AddWithValue("@d", Enrollment.Enrollment_Date)
    cmd.ExecuteNonQuery()
    Dim newId As Long = cmd.LastInsertedId
  End Using
End Using

(mysqlconnector.net)

For SQL Server, capture the ID in the same statement/scope — either append "SELECT SCOPE_IDENTITY()" and ExecuteScalar, or use the INSERT ... OUTPUT clause for multi-row scenarios. Both avoid the race conditions that MAX suffers from and are the recommended approaches for identity retrieval. Example (System.Data.SqlClient):

Dim sql = "INSERT INTO Enrollments (Student_ID,Enrollment_Date) VALUES (@s,@d); SELECT CAST(SCOPE_IDENTITY() AS int);"
Using conn As New SqlConnection(connString)
  conn.Open()
  Using cmd As New SqlCommand(sql, conn)
    cmd.Parameters.AddWithValue("@s", Enrollment.Student_ID)
    cmd.Parameters.AddWithValue("@d", Enrollment.Enrollment_Date)
    Dim newId As Integer = CInt(cmd.ExecuteScalar())
  End Using
End Using

(learn.microsoft.com)

For Jet/Access via OleDb, use the same open connection and run "SELECT @@IDENTITY" immediately after the insert (or use the DataAdapter RowUpdated pattern to set the Autonumber back into the DataRow). Be aware that very old Access formats may behave differently, so test on the exact engine/version in use. Example (OleDb):

Using conn As New OleDbConnection(connString)
  conn.Open()
  Using insertCmd As New OleDbCommand("INSERT INTO Enrollments (Student_ID,Enrollment_Date) VALUES (?,?)", conn)
    insertCmd.Parameters.AddWithValue("?", Enrollment.Student_ID)
    insertCmd.Parameters.AddWithValue("?", Enrollment.Enrollment_Date)
    insertCmd.ExecuteNonQuery()
  End Using
  Using idCmd As New OleDbCommand("SELECT @@IDENTITY", conn)
    Dim newId As Integer = Convert.ToInt32(idCmd.ExecuteScalar())
  End Using
End Using

(learn.microsoft.com)

Troubleshooting checklist: always use the same open connection for insert+readback, prefer parameterized commands, wrap related operations in a transaction if you must do follow-up writes, and prefer DB-supported retrieval (LAST_INSERT_ID, SCOPE_IDENTITY, OUTPUT, @@IDENTITY for Jet) over MAX/TOP.

All 11 Replies

poojavb 29 Junior Poster

14 Years Ago

I would like to retrieve the ID number for that row that was just inserted into the database.
Dim Enrollment As New Enrollments  
        With Enrollment  
            .Student_ID = CInt(lvwStudents.FocusedItem.Text)  
            .Enrollment_Date = CDate(dtpDate.Text)  
        End With  
        EnrollmentBAL.Save(Enrollment)
I have this code to save it from the database, what I want to do is after saving it I want to get the value of its ID and pass it to another table. can anyone help me on how to this or whats the best way to this. Thanks

try this

select last(id) from tablename

Phasma 11 Junior Poster in Training

14 Years Ago

The following should give you the exact ID of the Record that you last last Inserted.

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                                      My.Computer.FileSystem.SpecialDirectories.Desktop & _
                                      "\access.mdb;")

Private Sub btnExecuteQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteQuery.Click
    Dim sql1 As String = "INSERT INTO Inventory_1(Item_Name,Item_Category_ID,Item_Storage_ID,Item_Package_Size_ID,Item_Count,Item_Price) VALUES('BEAR MEAT',1,2,1,75,4.69);"
    Dim sql2 As String = "Select Max(Item_ID) FROM Inventory_1 Where Item_Name='BEAR MEAT' AND Item_Category_ID=1 AND Item_Storage_ID=2 AND Item_Package_Size_ID=1 AND Item_Count=75 AND Item_Price=4.69;"

    Dim cmd As New OleDb.OleDbCommand
    With cmd
      .CommandText = sql1 'Set Insert Command
      .CommandType = CommandType.Text
      .Connection = conn
    End With
    Dim id As Integer = 0


    Try
      conn.Open()
      cmd.ExecuteNonQuery() 'Execute the Insert
      cmd.CommandText = sql2 'Set the Select Command
      id = CInt(cmd.ExecuteScalar) 'Execute The Select
      lblID.Text = "Last Inserted Record ID - " & CStr(id) 'Display the Last Record Inserted ID
    Catch ex As Exception
      MsgBox(ex.ToString)
    Finally
      conn.Close()
    End Try
  End Sub

Hope this helps

madao commented: Thank you +1

Mitja Bonca 557 Nearly a Posting Maven

14 Years Ago

kingsonprisonic:
STOP ANSWERING ONLY FOR GETTING POINTS ON THE SALVED THREAD. You didnt have nothing to do here - and in many other threads.
AND STOP doing a mess around. We dont need ppl like you here with this kind of attitude. Got it?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

M.Waqas Aslam 67 Posting Pro in Training Featured Poster · Answer 1 · 2012-02-25T12:59:07+00:00

well if you are using auto increment in your id then try max function to get the max id , or if your auto generated id is not a number then there are two options 1- use top clause and the 2nd option is to make a new field name insertTime, and save the inserted time in it , then you will be able to access any id according to your required time period .

Regards

M.Waqas Aslam 67 Posting Pro in Training Featured Poster · Answer 2 · 2012-02-25T13:07:18+00:00

please check these links if you want to use max or top clauses.
for top
http://msdn.microsoft.com/en-us/library/ms189463.aspx
for max
(v=sql.80).aspx

If you need any more help please post here .

Regards

madao 0 Newbie Poster · Answer 3 · 2012-02-25T16:49:21+00:00

The following should give you the exact ID of the Record that you last last Inserted.

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                                      My.Computer.FileSystem.SpecialDirectories.Desktop & _
                                      "\access.mdb;")

Private Sub btnExecuteQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteQuery.Click
    Dim sql1 As String = "INSERT INTO Inventory_1(Item_Name,Item_Category_ID,Item_Storage_ID,Item_Package_Size_ID,Item_Count,Item_Price) VALUES('BEAR MEAT',1,2,1,75,4.69);"
    Dim sql2 As String = "Select Max(Item_ID) FROM Inventory_1 Where Item_Name='BEAR MEAT' AND Item_Category_ID=1 AND Item_Storage_ID=2 AND Item_Package_Size_ID=1 AND Item_Count=75 AND Item_Price=4.69;"

    Dim cmd As New OleDb.OleDbCommand
    With cmd
      .CommandText = sql1 'Set Insert Command
      .CommandType = CommandType.Text
      .Connection = conn
    End With
    Dim id As Integer = 0


    Try
      conn.Open()
      cmd.ExecuteNonQuery() 'Execute the Insert
      cmd.CommandText = sql2 'Set the Select Command
      id = CInt(cmd.ExecuteScalar) 'Execute The Select
      lblID.Text = "Last Inserted Record ID - " & CStr(id) 'Display the Last Record Inserted ID
    Catch ex As Exception
      MsgBox(ex.ToString)
    Finally
      conn.Close()
    End Try
  End Sub

Hope this helps

I have tried applying that code with mine. But I got a bunch of error after running it..
heres my insert code. how do i apply that with it

Dim sql As String
        sql = "INSERT INTO Enrollments" _
        & "(Student_ID,Enrollment_Date)VALUES(" _
        & "'" & Enrollment.Student_ID & "'," _
        & "'" & Enrollment.Enrollment_Date & "')"
        db = dbs.Connect
        db.Open()
        Dim cmd = New OleDbCommand(sql, db)
        cmd.ExecuteNonQuery()
        db.Close()
    End Sub

Mitja Bonca 557 Nearly a Posting Maven · Answer 4 · 2012-02-25T16:57:09+00:00

Dim sql As String = "INSERT INTO Enrollments (Student_ID,Enrollment_Date) VALUES(@param1, @param2)"
db = dbs.Connect
db.Open()
Dim cmd As dynamic = New OleDbCommand(sql, db)
cmd.Parameters.Add("@param1", SqlDbType.Int).Value = "Some id number"
'change this to int!!
cmd.Parameters.Add("@param2", SqlDbType.DateTime).Value = "Some date"
'change this to date!!
cmd.ExecuteNonQuery()
db.Close()

madao 0 Newbie Poster · Answer 5 · 2012-02-25T20:17:16+00:00

I have finally solved it with some modifcation. ty guys for the help

M.Waqas Aslam 67 Posting Pro in Training Featured Poster · Answer 6 · 2012-02-25T22:42:26+00:00

M.Waqas Aslam 67 Posting Pro in Training

14 Years Ago

then please mark this thread solved ,

kingsonprisonic 42 Posting Whiz in Training · Answer 7 · 2012-02-26T04:40:25+00:00

kingsonprisonic 42 Posting Whiz in Training

14 Years Ago

This thread is solved....

M.Waqas Aslam commented: there is no reason to say this , this is not your thread ,just posted here to get one more solved thread -1

Naruse commented: . -1

kingsonprisonic 42 Posting Whiz in Training · Answer 8 · 2012-02-26T05:01:08+00:00

You didnt have nothing to do here - and in many other threads.

In which thread....

How do I get the value of a recently insterted autonumber in the database

Dani AI

Recommended Answers Collapse Answers

All 11 Replies

Recommended Answers