Results 1 to 6 of 6

Thread: Get last record value from Access data base

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2016
    Posts
    5

    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

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    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
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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

  4. #4
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    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=

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    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
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

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
  •  



Click Here to Expand Forum to Full Width