asked on

Data type mismatch in criteria expression. - Occasional Error

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:

https://www.parkmorecottages.co.uk/booking.asp?

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?

Thanks
Steve

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
   Else
      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")
Else
   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"))
Else
   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

Daniel Wilson

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.

Daniel Wilson

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.

Thanks.

Daniel Wilson

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

This suggests the error occurs in 31-day months.

sadbassa

ASKER

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.

Thanks
booking.txt

ASKER CERTIFIED SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sadbassa

ASKER

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

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

Much appreciated.

Steve

Daniel Wilson

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