Thread: Error - Data type mismatch

I am getting the following error when I execute this code: "Data type mismatch in criteria expression." Thanks in advance.

Code:

Dim WhereUsed As Boolean = False
        Dim WhereString

        If Len(Me.cboCaller.Text) > 0 Then
            WhereString = "WHERE CALLER=?"
            WhereUsed = True
        Else
        End If

        Dim SQL As String
        SQL = "SELECT tblFirms.ORG_PK, tblFirms.ORG_NAME, tblStatus_Type.Status_TX, tblFirms.AA_NM, tblFirms.CONTACT_NM, tblFirms.CONTACT_PH, tblFirms.AA_PH, tblFirms.ASSESSED_AMOUNT, tblFirms.REN_BAL, tblFirms.DAILY_BAL, tblCallStatus.Call_Status, tblCallResults.Call_Result, tblCallDetails.caller, tblCallDetails.call_date " & _
            "FROM (((tblFirms INNER JOIN tblCallDetails ON tblFirms.ORG_PK = tblCallDetails.ORG_PK) INNER JOIN tblStatus_Type ON tblFirms.STATUS_ID = tblStatus_Type.Status_ID) INNER JOIN tblCallStatus ON tblCallDetails.CSTAT_ID = tblCallStatus.CStat_ID) INNER JOIN tblCallResults ON tblCallDetails.CRESULT_ID = tblCallResults.CResult_ID "

        Dim StartDt As Date = Now
        'start date is 7 days ago
        StartDt = StartDt.AddDays(-7)

        If WhereUsed = True Then
            SQL = SQL & WhereString & " and call_date>? "
        Else
            SQL = SQL & "where call_date>? "
        End If

        SQL = SQL & "ORDER BY tblCallDetails.call_date DESC "

        Dim myList As New List(Of String)
        Dim TryAttempt As Integer = 0

Retry:
        'Try
        Using conLocal As New OleDb.OleDbConnection(myConnString)
            Using commandLocal As New OleDb.OleDbCommand(SQL, conLocal)
                If WhereUsed = True Then
                    commandLocal.Parameters.AddWithValue("FirstName", Me.cboCaller.Text)
                    commandLocal.Parameters.AddWithValue("FirstName", StartDt)
                Else
                    commandLocal.Parameters.AddWithValue("FirstName", StartDt)
                End If
                conLocal.Open()
                Using dataReader As OleDb.OleDbDataReader = commandLocal.ExecuteReader
                    While dataReader.Read

Given that it's Access I'm not sure whether it matters or not but does it make sense to be using "FirstName" for all your parameters, especially given that in two cases that's not what the data represents? Fix that and see if the error remains, in which case we can look further.

Also, on a different note, you should basically never have the same code in both the If and Else block. You know that you're going to add the date parameter regardless so just do it once. You can then put just an If statement before it with no Else.

Well, firstly I would stick that horrid Select statement into a View/Query and make sure that works and then call that instead of sticking the whole SQL statement in your code (it's easier to debug that way and not significantly slower to execute...).

Secondly, I would look to your AddWithValue statements a little more closely...

@jmcilhinney: Thanks for the tip. I changed the parameter name and then edited my code, but I am still getting the same error.

Code:

Using conLocal As New OleDb.OleDbConnection(myConnString)
            Using commandLocal As New OleDb.OleDbCommand(SQL, conLocal)
                If WhereUsed = True Then
                    commandLocal.Parameters.AddWithValue("FirstName", Me.cboCaller.Text)
                End If
                commandLocal.Parameters.AddWithValue("StDate", StartDt)
                conLocal.Open()
                Using dataReader As OleDb.OleDbDataReader = commandLocal.ExecuteReader

@AndrewdAzotus: The select statement works. What do you mean by "calling that instead"?

The AddWithValue statements look okay to me, am I missing something..?

I meant calling the view rather than that horrid select statement.

Well, you've corrected the initial error that I noticed given that you were setting 'FirstName' to the date and the firstname.

I've not used VB.NET with Access yet, but I cannot see where firstname is in your where statement...

With Access it doesn't really matter what you use as the parameter name since it's just a kind of a placeholder. The "FirstName" just refers to the combobox, which contains a list of names

Well, when working in Access I've had an awful lot of trouble with dates with exactly this error.

I strongly recommend that you convert the date to a string. The trouble with Access is that is insists on american style dates regardless of the regional settings.

so convert the date to this format #mm/dd/yyyy# including the hashes (or sharpes if you prefer)

Originally Posted by AndrewdAzotus

Well, when working in Access I've had an awful lot of trouble with dates with exactly this error.

I strongly recommend that you convert the date to a string. The trouble with Access is that is insists on american style dates regardless of the regional settings.

so convert the date to this format #mm/dd/yyyy# including the hashes (or sharpes if you prefer)

If you've had trouble then you're doing it wrong. You should absolutely NOT convert the Date to a String. The Jet OLE DB provider requires the format you mention for date LITERALS. Unless you're hard-coding a specific value, you should not be using literals at
all. The OP is doing it exactly the right way. If you use parameters to insert variables then all data is stored in binary form at all times, so format of dates, times and numbers and single quotes, etc, in text is never an issue.

Having said that, it does beg the question, what data type is 'call_date'? That was going to be my next question anyway but you led me in nicely. Is it Date/Time or is it Text?

it is entirely possible that I was doing it wrong but (when working in Access VBA) given that:

dim when as date

"Select * from tbl where whendue=" & when

did not work

and

"Select * from tbl where whendue=#" & format(when,"MM/DD/YYYY") & "#"

did work, I gave up looking for another solution.

It was to do with not being in America and the date comparison wasn't working properly as I was using dates in dd/mm/yyyy format and Access was getting confused with 10/02/2012. Everywhere in my code it was recognised as 10th February but in Access it was recognising the date as Oct 2nd despite my regional settings.

(perhaps there are regional settings in Access that over ride the OS regional settings - I've only just thought of that and, frankly, I cannot be bothered to look...)

Originally Posted by AndrewdAzotus

it is entirely possible that I was doing it wrong but (when working in Access VBA) given that:

dim when as date

"Select * from tbl where whendue=" & when

did not work

and

"Select * from tbl where whendue=#" & format(when,"MM/DD/YYYY") & "#"

did work, I gave up looking for another solution.

It was to do with not being in America and the date comparison wasn't working properly as I was using dates in dd/mm/yyyy format and Access was getting confused with 10/02/2012. Everywhere in my code it was recognised as 10th February but in Access it was recognising the date as Oct 2nd despite my regional settings.

(perhaps there are regional settings in Access that over ride the OS regional settings - I've only just thought of that and, frankly, I cannot be bothered to look...)

VBA may well be a bit different as there may not be any way to use parameters, unless you can use ADO. As for the format for literals, it is always #M/dd/yyyy# for Microsoft development regardless of regional settings. Even in VB.NET you use that format for date literals regardless. For instance, if I wanted to hard-code my birthday:

Dim dateOfBirth = #6/19/1969#

even though I'm in Australia and the standard regional format here is d/MM/yyyy. That makes sense if you think about it because you wouldn;t want to have to go through all your source code and change all the date literals if you moved to a machine with different regional settings.

You're right, that does make sense. It would have made more sense if a proper big-endian date had been used for the standard date format. rather than the American mixed-endian date.

you know, like:

dim dob as date = #2012-02-10#

still, we're now way off topic - how is the OP doing?

Originally Posted by jmcilhinney

If you've had trouble then you're doing it wrong. You should absolutely NOT convert the Date to a String. The Jet OLE DB provider requires the format you mention for date LITERALS. Unless you're hard-coding a specific value, you should not be using literals at
all. The OP is doing it exactly the right way. If you use parameters to insert variables then all data is stored in binary form at all times, so format of dates, times and numbers and single quotes, etc, in text is never an issue.

Having said that, it does beg the question, what data type is 'call_date'? That was going to be my next question anyway but you led me in nicely. Is it Date/Time or is it Text?

Thanks for all the help guys. 'call_date' is a Date/Time data type. I just formatted the StartDt and it seems to be working now. Never had that happen before, but that's good to know.

Thanks again!

Code:

Dim StartDt As Date = Now
        'start date is 7 days ago
        StartDt = FormatDateTime(StartDt.AddDays(-7), DateFormat.GeneralDate)

Sorry to open an old wound, but going back to one of my earlier posts. Given that literals must be M/dd/yyyy (which, now that I understand why, is fine) that does not explain why

Originally Posted by AndrewdAzotus

dim when as date

"Select * from tbl where whendue=" & when

did not work since it's not a literal...

That actually doesn't make a lot of sense because you are converting the Date to a String and then that is being assigned to a Date variable and, therefore, implicitly converted back to a Date (which is bad, but that's for another day). What is the actual value of of the Date with and without that double conversion? There is undoubtedly a better way. You should NEVER convert any value to text unless you need to use the text specifically, e.g. for display or serialisation.

Fair enough, that also makes sense.

I should have been using the VBA equivalent to parameter.addwithvalue following a query.

It doesn't help with a DLOOKUP but I was only using them for laziness.

Perhaps it would have been even lazier to used a Select statement and passed the date in as a parm rather than the DLOOKUP

I always seem to forget who powerful and efficient SQL is and try to do my 'thing' without it...

Originally Posted by AndrewdAzotus

Sorry to open an old wound, but going back to one of my earlier posts. Given that literals must be M/dd/yyyy (which, now that I understand why, is fine) that does not explain why



did not work since it's not a literal...

Post #15 was actually in answer to post #13. In answer to this question, it actually is a literal from the point of view of the SQL code. You are relying on the Date being implicitly converted to a String and then concatenating that with the other String containing the rest of your SQL code. That whole statement is passed to the database as is, containing the literal value. The aforementioned implicit conversion will be done differently on different systems, which is part of the problem. When you use parameters the values are sent to the database in binary form and used in binary form by the database, so formatting text is never an issue because there's no text to format.

Of Course!

It wasn't a literal for me, but it was by the time it was processed in SQL - It's obvious ... now!



I suppose what was confusing was that it managed to make sense of 21/2/2012 whereas since it was expecting M/dd/yyyy it should have reported an invalid date - too clever for it's own good...