Get last Access column value

Question

I’m developing a small application that inserts data into an access database. I am able to enter the data however I wanted to get the last ID value entered (automatic number) for a msgbox.

provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
    datafile = "C:\Users\RR\Documents\Database2.mdb"
    connString = provider & datafile
    myConnection.ConnectionString = connString
    myConnection.Open()
    Dim str As String
    str = "Insert into Guias([Remetente],[Destinatário]) Values (?,?)"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    cmd.Parameters.Add(New OleDbParameter("Remetente", CType(ComboBox1.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("Destinatário", CType(ComboBox2.Text, String)))

Answer 1

To know which was the last number inserted use the SQL SELECT @@IDENTITY and in your code it looks like this, example:

provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
datafile = "C:\Users\RR\Documents\Database2.mdb"
connString = provider & datafile
myConnection.ConnectionString = connString
myConnection.Open()
Dim str As String
str = "Insert into Guias([Remetente],[Destinatário]) Values (?,?)"
Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
cmd.Parameters.Add(New OleDbParameter("Remetente", CType(ComboBox1.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Destinatário", CType(ComboBox2.Text, String)))

//adicioando essas linhas
Dim result As Integer = 0
If (cmd.ExecuteNonQuery() > 0) Then
    cmd.Parameters.Clear()
    cmd.CommandText = "SELECT @@IDENTITY"
    result = Integer.Parse(cmd.ExecuteScalar())
End If
Console.WriteLine("Id inserido:" & result) // a variavel result tem o valor do id inserido

Note: in each case SQL must be executed separately, but use the same SQLCommand only by changing the value of CommandText.

_{Reference: MS-Access with ADO.NET}