If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Re: Get last record value from Access data base

Double up your query to return the last ID that was inserted:

Code:

'Declare the object to store the last inserted ID
Dim id As Integer = -1
'Declare the connection object
Dim con As OleDbConnection
'Wrap code in Try/Catch
Try
'Set the connection object to a new instance
'TODO: Change "My Connection String Here" with a valid connection string
con = New OleDbConnection("My Connection String Here")
'Create a new instance of the command object
Using cmd As OleDbCommand = New OleDbCommand("Insert into Guias([Remetente],[Destinatário]) Values (@remetente, @destinatario); SELECT @@Identity AS [id];", con)
'Parameterize the query
With cmd.Parameters
.AddWithValue("@remetente", ComboBox1.Text)
.AddWithValue("@destinatario", ComboBox2.Text)
End With
'Open the connection
con.Open()
'Use ExecuteScalar to return a single value
id = Convert.ToInt32(cmd.ExecuteScalar())
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try

Re: Get last record value from Access data base

I would push you toward using DataReaders and DataAdapters, but that's just me.

Code:

Private Sub ButtonGetLastID_Click(sender As Object, e As EventArgs) Handles ButtonGetLastID.Click
MsgBox(GetLastID.ToString)
End Sub
Private Function GetLastID() As Integer
Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\DATA\Access\AccessDB.accdb;")
conn.Open()
Using RDR As OleDbDataReader = New OleDbCommand("SELECT TOP 1 CustID FROM Customers ORDER BY CustID Desc", conn).ExecuteReader
If RDR.HasRows Then
RDR.Read()
Return RDR.GetInt32(0)
Else
Return Nothing
End If
End Using
conn.Close()
End Using
End Function

Re: Get last record value from Access data base

Here is a complete class example (including the above example) of using DataAdapter and binding. I am not sure why anyone is compelled to do it the way you are. To each their own.

Notice in this class we are loading the schema, so you always know the last ID, its just available.

Code:

Imports System.Data.OleDb
Public Class AccessInsertUpdate
Dim dt As New DataTable
Dim bs As New BindingSource
Private Sub AccessInsertUpdate_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\DATA\Access\AccessDB.accdb;")
Using DA As New OleDbDataAdapter("SELECT * FROM Customers", conn)
DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
DA.FillSchema(dt, SchemaType.Source)
DA.Fill(dt)
bs.DataSource = dt
DataGridView1.DataSource = bs
End Using
End Using
End Sub
Private Sub ButtonInsertUpdate_Click(sender As Object, e As EventArgs) Handles ButtonInsertUpdate.Click
Dim FindIdx As Integer = bs.Find("CustName", TextBoxCustName.Text)
If FindIdx >= 0 Then
'You has this customer lets update him
CType(bs(FindIdx), DataRowView)("CustInfos") = TextBoxInfos.Text
bs.EndEdit()
Else
'You do has this customer lets insert
bs.AddNew()
CType(bs.Current, DataRowView)("CustName") = TextBoxCustName.Text
CType(bs.Current, DataRowView)("CustInfos") = TextBoxInfos.Text
bs.EndEdit()
End If
Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\DATA\Access\AccessDB.accdb;")
Using DA As New OleDbDataAdapter("SELECT * FROM Customers", conn)
Dim cb As New OleDbCommandBuilder(DA)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
DA.Update(dt)
dt.Rows.Clear()
DA.Fill(dt)
End Using
End Using
End Sub
Private Sub ButtonGetLastID_Click(sender As Object, e As EventArgs) Handles ButtonGetLastID.Click
MsgBox(GetLastID.ToString)
End Sub
Private Function GetLastID() As Integer
Using conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\DATA\Access\AccessDB.accdb;")
conn.Open()
Using RDR As OleDbDataReader = New OleDbCommand("SELECT TOP 1 CustID FROM Customers ORDER BY CustID Desc", conn).ExecuteReader
If RDR.HasRows Then
RDR.Read()
Return RDR.GetInt32(0)
Else
Return Nothing
End If
End Using
conn.Close()
End Using
End Function
End Class

Re: Get last record value from Access data base

@kmpc - The reason why I would suggest using ExecuteScalar is because the OP is only concerned about returning a single value and this is exactly what the ExecuteScalar method does.

@wes4dbt - You were correct. I received an invalid character error when attempting to use a double command. Here is the updated (tested and confirmed) code:

Code:

'Declare the object to store the last inserted ID
Dim id As Integer = -1
'Declare the connection object
Dim con As OleDbConnection
'Wrap code in Try/Catch
Try
'Set the connection object to a new instance
'TODO: Change "My Connection String Here" with a valid connection string
con = New OleDbConnection("My Connection String Here")
'Create a new instance of the command object
Using cmd As OleDbCommand = New OleDbCommand("Insert into Guias([Remetente],[Destinatário]) Values (@remetente, @destinatario);",
'Parameterize the query
With cmd.Parameters
.AddWithValue("@remetente", ComboBox1.Text)
.AddWithValue("@destinatario", ComboBox2.Text)
End With
'Open the connection
con.Open()
'Insert the row
cmd.ExecuteNonQuery()
'Change the command to return the last insert ID
cmd.CommandText = "SELECT @@Identity AS [id];"
'Use ExecuteScalar to return a single value
id = Convert.ToInt32(cmd.ExecuteScalar())
'Close the connection
con.Close()
End Using
Catch ex As Exception
'Display the error
Console.WriteLine(ex.Message)
Finally
'Check if the connection object was initialized
If con IsNot Nothing Then
If con.State = ConnectionState.Open Then
'Close the connection if it was left open(exception thrown)
con.Close()
End If
'Dispose of the connection object
con.Dispose()
End If
End Try