Visual Basic : Data type mismatch in criteria expression.

[Pickletronic]

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
        da.Dispose()

        If dt.Rows.Count = 0 Then
            MessageBox.Show("Not Found")
        Else
            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

        txtSearch.Clear()
    End Sub

[Minimalist]

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
 oConnect=
 'Define the query
   oQuery =
   'Instantiate the connectors
    oConn = New OleDbConnection(oConnect)
    oComm = New OleDbCommand(oQuery, oConn)
    'Open the connection
     oConn.Open()
     'Perform the Non-Query
      oComm.ExecuteNonQuery()
      'Close the connection
       oConn.Close()

[Pickletronic]

I have created a sub class

Sub AddEditDelete()
        command = New OleDbCommand(sqlStr, conn)
        command.Connection.Open()
        command.ExecuteNonQuery()
        conn.Close()
    End Sub

But still getting the same error

[Minimalist]

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

[kRod]

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
            .Fill(dt)
            .Dispose()
        End With

[G_Waddell]

Hi,
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.