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

[madao]

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

[poojavb]

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]

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

[M.Waqas Aslam]

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]

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
http://msdn.microsoft.com/en-us/library/aa933240(v=sql.80).aspx

If you need any more help please post here .

Regards

[madao]

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]

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]

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

[M.Waqas Aslam]

then please mark this thread solved ,

[kingsonprisonic]

This thread is solved....

[Mitja Bonca]

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?

[kingsonprisonic]

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

In which thread....