i am inserting a row in an acces table but I also want to get the id of the last inserted row so that I can use that value. Any ideas on how I can go about it?

Dim iSql As String = "Insert into CompanyDetails (code,description)





02

  Connections.MyDataAdapter(iSql)





03

 





04

 Public Shared Function MyDataAdapter(ByVal iSql As String) As OleDb.OleDbDataAdapter





05

        Dim dt As New DataTable





06

        Dim da As New OleDb.OleDbDataAdapter(iSql, MyConnection)





07

        da.Fill(dt)





08

        Return da





09

        da.Dispose()





10

    End Function

you can use max() and top() to get the last inserted record in your db table

select max(id) as id from table
'-------------
select top(id) as from table
order by id desc

Hope this will help you

i tried the code but it is saying that it has syntax problems

select max(id) as id from table'-------------select top(id) as from tableorder by id descselect max(id) as id from table
'-------------
select top(id) as from table
order by id desc
Dim Extract As String = "select top(ID)  from CompanyDetails order by ID desc"
        Dim dTable As DataTable = New DataTable
        Dim da As New OleDbDataAdapter(Extract, Connections.MyConnection)
        da.Fill(dTable)
        id = dTable.Rows(0)("ID").ToString
        MsgBox(id.ToString)

i created the procedure above to see if the code will extract the max(id) but the code is refusing to work. it is saying there is a problem near from clause.

correct syntax of top() is

select top 1 idno from students
order by idno desc

sorry for above wrong code .

Regards

This article has been dead for over six months. Start a new discussion instead.