Data type mismatch in criteria expression.

[Nebil]

Hi guys,
I just figured out the normalization problem.And i'm moving on to the application.
But i'm facing a problem,i needed to check if an id exists in the db when an item is inserted.
It's saying the title i just wrote on the article.Here's the code i used.

'Tocheck if a pay item number exists in the database
    Public Function CheckID(ByVal str)
        Dim conn As New OleDbConnection
        Dim cmd As New OleDbCommand
        Dim data_reader As OleDbDataReader
        conn = GetDbConnection()
        Dim s As String = "SELECT * FROM pay_item_description WHERE [pay item]='" & str & "'"
        cmd = New OleDbCommand(s, conn)
        data_reader = cmd.ExecuteReader
        While data_reader.Read
            If data_reader.Item("pay item") = str Then
                Return True
            End If
        End While
        Return False
    End Function

This is the code i used to check if the number exists in the db.

If oPayItem.CheckID(payItem) = True Then
            MsgBox("Pay item number " + payItem + " already exists", vbCritical, "Bridge Construction Cost Estimate")

payItem is declared as double.

[Begginnerdev]

Sounds like your problem lies here:

Dim s As String = "SELECT * FROM pay_item_description WHERE [pay item]='" & str & "'"

If you change the code to :

Dim s As String = "SELECT * FROM pay_item_description WHERE [pay item]=" & Cint(str)

Or

Dim s As String = "SELECT * FROM pay_item_description WHERE [pay item]=" & CDbl(str)

What happens?

[gusano79]

Also, at some point you should look into using parameterized queries.

[Nebil]

I just found the problem,it's at the SQL statement where variable str is enclosed with ''.I removed it or else it considers it like a string variable while being another type.Plus you may change the data_reader.item line of code to double.Thank's guys.