Resolved System.Data.OleDb.OleDbException: 'Data type mismatch in criteria expression.'

[g4naq]

I have an SQL query which was provided by one of the forum members which works fine.. I need to enhance this by making 2 literals which are part of the SQL query as variables.

   '                        sqlSelect1 = "SELECT L_CALL, L_QSONR FROM LOGBOOK WHERE L_CALL = @callsign AND L_DATE= @qsodate AND L_BANDID = @bandid AND L_MODEID = @modeid
                        'AND TimeSerial(Hour(L_TIME), Minute(L_TIME), Second(L_TIME)) >= DateAdd(""n"", -5, TimeValue(@qsotime1))
                        'AND TimeSerial(Hour(L_TIME), Minute(L_TIME), Second(L_TIME)) <= DateAdd(""n"", 5, TimeValue(@qsotime2))"

                        sqlSelect1 = "SELECT L_CALL, L_QSONR FROM LOGBOOK WHERE L_CALL = @callsign AND L_DATE= @qsodate AND L_BANDID = @bandid AND L_MODEID = @modeid
AND TimeSerial(Hour(L_TIME), Minute(L_TIME), Second(L_TIME)) >= DateAdd(""n"", @tolerance1, TimeValue(@qsotime1))
AND TimeSerial(Hour(L_TIME), Minute(L_TIME), Second(L_TIME)) <= DateAdd(""n"", @tolerance2, TimeValue(@qsotime2))"
                        cmd1.Connection = DBconnection
                        cmd1.CommandText = sqlSelect1
                        cmd1.Parameters.AddWithValue("@callsign", txtCALL)
                        cmd1.Parameters.AddWithValue("@qsodate", txtSLDate)
                        cmd1.Parameters.AddWithValue("@bandid", intBANDID)
                        cmd1.Parameters.AddWithValue("@modeid", intMODEID)
                        cmd1.Parameters.AddWithValue("@qsotime1", txtQSOTime)
                        cmd1.Parameters.AddWithValue("@qsotime2", txtQSOTime)
                        cmd1.Parameters.AddWithValue("@tolerance1", intSLtoleranceNeg)
                        cmd1.Parameters.AddWithValue("@tolerance2", intSLtolerance)
                        Dim SLdbReader As OleDbDataReader = cmd1.ExecuteReader()

the -5 & 5 within the TimeSerial function I am trying to pass in as variables but currently this is failing at runtime.. what type of variable type should I use or is the above syntax incorrect?

Apologies I have resolved this!

     cmd1.Parameters.AddWithValue("@callsign", txtCALL)
                        cmd1.Parameters.AddWithValue("@qsodate", txtSLDate)
                        cmd1.Parameters.AddWithValue("@bandid", intBANDID)
                        cmd1.Parameters.AddWithValue("@modeid", intMODEID)
                        cmd1.Parameters.AddWithValue("@tolerance1", intSLtoleranceNeg)
                        cmd1.Parameters.AddWithValue("@qsotime1", txtQSOTime)
                        cmd1.Parameters.AddWithValue("@tolerance2", intSLtolerance)
                        cmd1.Parameters.AddWithValue("@qsotime2", txtQSOTime)

 

[jmcilhinney]

In future, if you solve your own problem then please don't edit the question but, rather, add an answer. Add a new post that contains the answer and then select it as the best solution. You should also then mark your thread Resolved, so that we can see that your problem is resolved without having to open the thread.

Your solution is correct but, in case you're not sure why, you need to add the parameters to the command in the same order as they appear in the SQL. That isn't the case for some databases, e.g. SQL Server, which do support named parameters. While you can use names for clarity with the Jet and ACE OLE DB providers, those names are ignored and only the positions matter.