Dim Cname As String
        Dim CID As Integer
        Dim Rmaterial As String

        con = New OleDbConnection("Provider=Microsoft.ace.Oledb.12.0;Data Source= C:\Users\Lenovo\Documents\Visual Studio 2010\Soft\Database\Login.accdb")

        cmd = New OleDbCommand("select * from Purchase where CompanyID='" & txtnum.Text & "'", con)
        dr = cmd.ExecuteReader()

        If txtname.Text = "" And txtnum.Text = "" And txtraw.Text = "" Then
            MessageBox.Show(" Enter All The Feilds", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information)

        ElseIf dr.Read() Then
            MessageBox.Show("Company ID already exist", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information)

            CID = Convert.ToInt32(txtnum.Text)
            Cname = txtname.Text
            Rmaterial = txtraw.Text
            cmd = New OleDbCommand("INSERT INTO Purchase VALUES(  '" & Cname & "'," & CID & ",'" & Rmaterial & "')", con)
            MessageBox.Show("Added successfully", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        cmd = New OleDbCommand("Select * from  Purchase", con)
        dr = cmd.ExecuteReader()
        Dim dt As New DataTable("Purchase")
        DataGridView1.DataSource = dt

    End Sub

Why do i get the error "Data type mismatch in criteria expression"

Recommended Answers

All 3 Replies

In line 22, 'CID' is an int perhaps using 'txtnum.Text' instead will fix it.

I think tinstaafl is on to something. What is the datatype for the CompanyID column in the Access database? Usually ID fields are auto-number integer values, but it looks like your SELECT query uses a text value in the WHERE clause (Line 9)
where CompanyID='" & txtnum.Text & "'"

The confusion might be because of a misinterpretation. You use single quotes around a field value when the database field is of type String, not when the VB variable is of type String. For example, if the CompanyID field in the database is an int then two similar (but using different VB variable type) queries would be

Dim CID As Integer = 12345
Dim query As String = "SELECT * FROM Purchase WHERE CompanyID = " & CID


Dim CID As String = "12345"
Dim query As String = "SELECT * FROM Purchase WHERE CompanyID = " & CID

Note that in both examples, the patched-in value does not use single quotes. For future reference, when you have a syntax problem with a query, use Debug.WriteLine (or something similar) to display the actual query string and post it with your question. The cause of the problem generally becomes more obvious (and easier to dignose).

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.