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) con.Open() dr = checkValSQL.ExecuteScalar con.Close() 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.