Use VB.NET Manipulate Microsoft Access Database

Question

How can I make this work?

Private Sub ListView_MouseClick(sender As Object, e As MouseEventArgs) Handles ListView.MouseClick

    conndb = New OleDbConnection
    conndb.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb"
    Try
        conndb.Open()
        Dim str As String
        str = "Select * FROM customer WHERE CustomerID = '" & ListView.FocusedItem.Text & "'"
        COMMAND = New OleDbCommand(str, conndb)
        dr = COMMAND.ExecuteReader

        If dr.Read = True Then
            txtID.Text = dr("CustomerID")
            txtFirstName.Text = dr("FirstName")
            txtSurname.Text = dr("Surname")
            txtAddress.Text = dr("Address")
            txtCN1.Text = dr("ContactNo1")
            txtCN2.Text = dr("ContactNo2")
            txtEmail.Text = dr("EmailAddress")
            txtRemarks.Text = dr("Remarks")
            txtDebtStatus.Text = dr("DebtStatus")
            txtDownPay.Text = dr("DownPayment")
            txtDebtBal.Text = dr("DebtBal")
            txtCustomerDate.Text = dr("Date")
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        conndb.Dispose()
    End Try
End Sub

I need help on how can I make this run without errors, Im using ms access as my database source. There seems to be an error using this code, this code works perfectly fine with mysql but in ms access, it says data mistype error or something like that. Need your help, thanks

Pure guess as I don't know what "mistype error or something" is, but `|DataDirectory|` should probably the actual directory where you database file is — Matt Wilko, Aug 30 '16 at 12:36
Just a tip, don't YELL IN YOUR QUESTION TITLES. YELLING makes people less willing to help, even if you didn't realize you were yelling. — rory.ap, Aug 30 '16 at 12:37
*"data mistype error or something like that"* - You need to provide the exact message and the point at which its exception is raised. You should be using a parameterized command, if `CustomerID` is a number field lose the single quotes ans ensure `ListView.FocusedItem.Text` is returning a valid numeric value. — Alex K., Aug 30 '16 at 12:37

Martin Verjans · Answer 1 · 2017-09-22T16:08:00.253

Remove the ' surrounding the field CustomerID in your query :

str = "Select * FROM customer WHERE CustomerID = '" & ListView.FocusedItem.Text & "'"

becomes :

str = "Select * FROM customer WHERE CustomerID = " & ListView.FocusedItem.Text

MS Access sees a string when you put an apostrophe, so there is a Type Mismatch Exception, because it is expecting a number...

However, this is a pretty bad idea as Parametrized queries are a better way of doing this (see : Why should I create Parametrized Queries ?)

Also, Use Using

So all in all, it's just another brick in the wall :

Private Sub ListView_MouseClick(sender As Object, e As MouseEventArgs) Handles ListView.MouseClick

Using conndb As New OleDbConnection
    conndb.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb"
    Try
        conndb.Open()
        Dim str As String
        str = "Select * FROM customer WHERE CustomerID = @Customer"
        Using COMMAND As New OleDbCommand(str, conndb)
            COMMAND.Parameters.Add("@Customer", SqlDbType.Integer).Value = Integer.Parse(ListView.FocusedItem.Text)
            dr = COMMAND.ExecuteReader

            If dr.Read = True Then
                txtID.Text = dr("CustomerID")
                txtFirstName.Text = dr("FirstName")
                txtSurname.Text = dr("Surname")
                txtAddress.Text = dr("Address")
                txtCN1.Text = dr("ContactNo1")
                txtCN2.Text = dr("ContactNo2")
                txtEmail.Text = dr("EmailAddress")
                txtRemarks.Text = dr("Remarks")
                txtDebtStatus.Text = dr("DebtStatus")
                txtDownPay.Text = dr("DownPayment")
                txtDebtBal.Text = dr("DebtBal")
                txtCustomerDate.Text = dr("Date")
            End If
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Using
End Sub

While you are probably right proposing an answer that uses string concatenation to build sql commands is very bad — Steve, Aug 30 '16 at 12:42
@Rossu if this works, don't forget to mark this solution as the solution, so other with the same problem can solve it as well. — Martin Verjans, Aug 31 '16 at 06:02

score 0 · Answer 2 · answered Aug 31 '16 at 16:32

Take a look at this sample code that I put together a while back. You can probably learn a lot from this.

Private Sub TextBox1_TextChanged(sender As System.Object, e As System.EventArgs) Handles TextBox1.TextChanged, TextBox1.Click

            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\your_path\Desktop\Northwind_2012.mdb"
            Dim selectCommand As String
            Dim connection As New OleDbConnection(connectionString)

            'selectCommand = "Select * From MyExcelTable where Fname = '" & TextBox1.Text & "'"
            '"SELECT * FROM Customers WHERE Address LIKE '" & strAddressSearch & "%'"
            'or ending with:
            '"SELECT * FROM Customers WHERE Address LIKE '%" & strAddressSearch & "'"

            selectCommand = "Select * From MyExcelTable where Fname Like '" & TextBox1.Text & "%'"
            Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)

            With DataGridView1
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
            End With

            Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)
            Dim table As New DataTable()

            table.Locale = System.Globalization.CultureInfo.InvariantCulture

            Me.dataAdapter.Fill(table)
            Me.bindingSource1.DataSource = table

            Dim data As New DataSet()

            data.Locale = System.Globalization.CultureInfo.InvariantCulture

            DataGridView1.DataSource = Me.bindingSource1
            Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Aqua
            Me.DataGridView1.AutoResizeColumns( _
                DataGridViewAutoSizeColumnsMode.AllCells)

        End Sub

Not using Parametrized queries, not disposing anything, not using Using blocks. Where do you answer the question ? — Martin Verjans, Aug 31 '16 at 21:36

Use VB.NET Manipulate Microsoft Access Database

2 Answers2