Data type mismatch in criteria expression. - Occasional Error

sadbassa used Ask the Experts™
Hi, I have an issue with an availability system on a web site. Our company usually works in PHP and MySql, but this system has been taken over by a one of our clients and is built in ASP and we only have limited knowldge.

The Problem:
When you try to choose availability dates in ONLY March or May on the page:

You get the error below, any other month is fine.

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/booking.asp, line 89

The VB Script code is written into the header of the page. I have detailed it below, does anyone have any idea what the problem is and why it only happens for March and May?


Option Explicit
Response.Expires = 0
<!-- #include file="library/libForceSSL.asp" -->
<!-- #include file="library/libDatabase.asp" -->
Dim objConn, objRSProperties, objRSPageFooter, objRSPeakSeason, sSQL, strCottageName, iBookLink, today, dtArrival, dtDeparture
Dim iAdults, iChildren, iInfants, strCottageID, dtPeakStart, dtPeakEnd, dblDiscount, strDiscountCode, bInWeekRange

today = date
iBookLink = 1

'Connect to database
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strGDBConnect

Set objRSProperties = Server.CreateObject("ADODB.Recordset")
Set objRSProperties.ActiveConnection = objConn

sSQL = "SELECT cottageid, cottagename & ' (sleeps ' & sleeps & ')' as cottagedetails from cottages where bedrooms > 0 order by cottagename"

objRSProperties.Open sSQL

Set objRSPageFooter = Server.CreateObject("ADODB.Recordset")
Set objRSPageFooter.ActiveConnection = objConn

sSQL = "SELECT text1 FROM pageText WHERE pageid = 'FOOTERTEXT'"

objRSPageFooter.Open sSQL

If Request.Form.Count > 0 Then
   dtArrival = Request.Form("dtArrival")
   dtDeparture = Request.Form("dtDeparture") 
   bInWeekRange = Request.Form("inweekrange")
   If bInWeekRange = 1 Then
      If DateDiff("d",dtArrival,dtDeparture) < 7 Then
         dtDeparture = DateAdd("d",CDate(dtArrival),7)
      End If
      If DateDiff("d",dtArrival,dtDeparture) < 3 Then
         dtDeparture = DateAdd("d",CDate(dtArrival),3)
      End If   
   End If  
   strCottageName = Request.Form("txtProperty")
   iAdults = Request.Form("adults")  
   If iAdults = "" Then
      iAdults = 0
   End If  
   iChildren = Request.Form("children")   
   If iChildren = "" Then
      iChildren = 0
   End If  
   iInfants = Request.Form("infants") 
   If iInfants = "" Then
      iInfants = 0
   End If  
   dblDiscount = Request.Form("discount")
   strDiscountCode = Request.Form("discountCode")
   dtArrival = Request.QueryString("dtArrival")
   If dtArrival = "" Then
      dtArrival = DateAdd("d",Date(),1)
   End If
   dtDeparture = Request.QueryString("dtDeparture")
   If dtDeparture = "" Then
      dtDeparture = DateAdd("d",Date(),8)
   End If
   strCottageName = Request.QueryString("cottageID")
   If strCottageName = "" Then
      strCottageName = "ALL"
   End If         
   dblDiscount = Request.QueryString("discount")
   If dblDiscount = "" Then
      dblDiscount = 0
   End If
   strDiscountCode = Request.QueryString("discountCode")
   iAdults = 0
   iChildren = 0
   iInfants = 0
End If

Set objRSPeakSeason = Server.CreateObject("ADODB.Recordset")
Set objRSPeakSeason.ActiveConnection = objConn

sSQL = "SELECT peakStart, peakEnd FROM book_Intervals WHERE Year = Year('" & dtArrival & "')"

objRSPeakSeason.Open sSQL

If Not objRSPeakSeason.EOF Then
   dtPeakStart = Day(objRSPeakSeason("peakStart")) & "-" & MonthName(Month(objRSPeakSeason("peakStart")),True) & "-" & Year(objRSPeakSeason("peakStart"))
   dtPeakEnd = Day(objRSPeakSeason("peakEnd")) & "-" & MonthName(Month(objRSPeakSeason("peakEnd")),True) & "-" & Year(objRSPeakSeason("peakEnd"))
   dtPeakStart = Day(objRSPeakSeason(today)) & "-" & MonthName(Month(objRSPeakSeason(today)),True) & "-" & Year(objRSPeakSeason(today))
   dtPeakEnd = Day(objRSPeakSeason(today)) & "-" & MonthName(Month(objRSPeakSeason(today)),True) & "-" & Year(objRSPeakSeason(today))
End If

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is line 89 (where the error occurs) the line listed as 89 in your listing?  

That seems an unusual line for a data type mismatch to occur ... and sometimes code snippets pasted in get the line numbers thrown off from the original.  So I want to be sure I'm looking at the right spot.
There is also another error that comes up.  If you choose a date in July other than a Saturday, you get the warning:
During the peak season the online booking system will only accept bookings starting and ending on a saturday. Your arrival date has been modified to fulfill this criteria. Should you require any different dates please phone our team on 01340 820072

Followed by the error:
ADODB.Recordset error '800a0cc1'
 Item cannot be found in the collection corresponding to the requested name or ordinal.
 /booking.asp, line 95

Line 95, as listed above (the final "End If") could scarcely produce that error.  So your clarification of the lines reporting the errors is truly necessary.

October and December show the same error as March and May.

This suggests the error occurs in 31-day months.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.


I think that has happened, Line 89 in UltraEdit is:

objRSPeakSeason.Open sSQL

I have attached the file too, I have uploaded it as a txt file because I cant upload a .asp file.

OK, that makes more sense.  In Access you should be using # around your dates instead of '
so line 87 should be:
sSQL = "SELECT peakStart, peakEnd FROM book_Intervals WHERE Year = Year(#" & dtArrival & "#)"

I'm not sure that's the whole solution, however.  You may be dealing with differing date formats between your ASP and Access.  If one is doing mm/dd/yyyy and the other dd/mm/yyyy, that could cause significant confusion.

Before line 89, would you do a Response.Write of sSQL?  I think you'll need to flush the buffer to get it actually to output before the error strikes.


Thanks, I have made the query change and it works.

Not sure on the Response.Write, will this still be needed?

Much appreciated.

If the query change did it ... no need for more debugging.  Glad it's working!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial