-
Feb 7th, 2018, 01:48 PM
#1
Thread Starter
New Member
Get last record value from Access data base
Hi,
Im building a little app in VB.net to help me on my daily job, where i use some combo boxes to record some value to access.
But i don´t know how to get the last ID(Automatic Number) to a msgbox.
My code:
Simple 2 comboBox´s insert value to 2 columns pressing one button, but after that i want a msgbox to say the last recorded value from ID
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)))
Sorry guys im trying to learning VB.net
-
Feb 7th, 2018, 02:17 PM
#2
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
-
Feb 7th, 2018, 04:37 PM
#3
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
-
Feb 7th, 2018, 04:41 PM
#4
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
-
Feb 7th, 2018, 05:30 PM
#5
Re: Get last record value from Access data base
dday9,
I don't believe MS Access will except double commands like SQL Server does. At least it didn't use to.
rubin,
jmc has an example in the code bank for both Typed and UnTyped datasets.
http://www.vbforums.com/showthread.p...ert&highlight=
-
Feb 8th, 2018, 10:16 AM
#6
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
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|