Get last column Access

2

I'm developing a small application that inserts data into a database in access. I am trying to insert the data however I wanted to get the last value of the inserted ID (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)))
    
asked by anonymous 07.02.2018 / 09:34

1 answer

1

In order to find out the last number entered, use SQL SELECT @@IDENTITY and in your code it looks like 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 this case every SQL must be executed separately, but use the same SQLCommand only changing the value of CommandText .

Reference MS- Access with ADO.NET

    
08.02.2018 / 16:02