i have this code to check and make sure that I wont be inserting duplicate records into my table. i take whatever is in the dr variable and if it is 0 then I go ahead, if not I don't do anything with the variables, I am using Excel, and VB 2005 Express (pretty sure that is .NET)

'val holds the column name, table holds the table name, and variable holds the data
Function checkVals(ByVal val, ByVal table, ByVal variable)

        Dim con As New OleDb.OleDbConnection
        Dim checkValueSQL As String
        'Dim checkIDSQL As String
        Dim dr As Integer

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DB.mdb"

        checkValueSQL = "SELECT COUNT(" & val & ")" & _
                        "FROM " & table & _
                        " WHERE " & val & " = """ & variable & """"

        Dim checkValSQL As New OleDb.OleDbCommand(checkValueSQL, con)


        dr = checkValSQL.ExecuteScalar


        Return dr
    End Function

Ok, so my problem is that this runs fine for the first 9 records, on the 10th it throws an error "Data type mismatch in criteria expression" Although the variables hold the same data types as they have the other 9 times. Im not sure what is going on and I have been staring at it for to long probably - so a fresh pair of eyes and a new perspective may be just the thing I need.

I figured it out, apparently I have been looking at it for to long, SQL statements that you want to compare and are Integers do not have quotes around them. So i checked the type in an if statement and wrote the query accordingly.

Be a part of the DaniWeb community

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