Start New Discussion within our Software Development Community

Hello, I was trying to view all the data from a single from a database after the button "search" is clicked and the output will be inserted into corresponding textboxes for future editing. After I entered the ID(1) I get an "OleDbException was unhandled" " Data type mismatch in criteria expression." error. I can't figure out the problem at all. All the type for my database is text except for total_payment which is in currency.

Any help is greatly appreciated.

Thank You.

Dim connStr As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=cars.accdb"
    Dim conn As New OleDbConnection(connStr)
    Dim command As OleDbCommand
    Dim dt As DataTable
    Dim da As OleDbDataAdapter
    Dim sqlStr As String

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim searchStr As String = CStr(txtSearch.Text)
        sqlStr = "select * from car where Renter_ID = '" & searchStr & "'"

        dt = New DataTable()
        da = New OleDbDataAdapter(sqlStr, connStr)

        da.Fill(dt) <<< This is where I got the error

        If dt.Rows.Count = 0 Then
            MessageBox.Show("Not Found")
            txtID.Text = dt.Rows(0)("Renter_ID")
            txtName.Text = dt.Rows(0)("Renter_Name")
            txtIC.Text = dt.Rows(0)("Renter_IC")
            txtPhoneNumber.Text = dt.Rows(0)("Renter_Phone")
            txtCarType.Text = dt.Rows(0)("Rented_Car")
            txtTotal.Text = dt.Rows(0)("Total_Payment")

        End If

    End Sub

Your dim statements are out of the button click event.I also can't seew here you open the connection. You would perform these steps:

 'Define connection string
 'Define the query
   oQuery =
   'Instantiate the connectors
    oConn = New OleDbConnection(oConnect)
    oComm = New OleDbCommand(oQuery, oConn)
    'Open the connection
     'Perform the Non-Query
      'Close the connection

I have created a sub class

Sub AddEditDelete()
        command = New OleDbCommand(sqlStr, conn)
    End Sub

But still getting the same error

When dimensioning your variables put all the database stuff you need into the event where you need it.

Try the following in your code. If it doesn't work take the single quotes from around searchStr it is probably Type integer in the Database Table

Dim cmd As New OleDbCommand

        Dim da As OleDbDataAdapter
        With cmd
            .CommandText = "select * from car where Renter_ID = '" & searchStr & "'"
            .CommandType = CommandType.Text
            .Connection = conn
        End With
        da = New OleDbDataAdapter()
        With da
            .SelectCommand = cmd
        End With

is the field Renter_ID in the database definately a string type? and not a numeric type? The reason I ask is that you say the error occurs when you fill your adaptor i.e. when you execute the query.

This article has been dead for over six months. Start a new discussion instead.