...

View Full Version : Microsoft JET Database Engine error '80040e07'



rickw
03-05-2006, 08:19 PM
A while back, I hired a programmer to put together a simple Access database script. I have been able to modify the script and forms as needed to satisfy certain requirements for my client's site. That is, until now. :(

I tried some extensive changes (field names, data calls, etc.) to enhance this site, and the script is now throwing :eek: the following error:


Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/include/lmlsscript.asp, line 147


Here are the forms and script I am using:


REQUESTING FORM

<form action="lresults.asp" method="post">
<small>Price Range:</small><br>
<select name="Price" size="1">
<option></option>
<option>$49,999 or Less</option>
<option>$50,000 - $74,999</option>
<option>$75,000 - $99,999</option>
<option>$100,000 - $149,999</option>
<option>$150,000 - 199,999</option>
<option>$200,000 - 299,999</option>
<option>$300,000 and Up</option>
</select><small><br>
<br>
Property Type:</small><br>
<select name="Type" size="1">
<option></option>
<option>Acreage</option>
<option>Farm / Ranch</option>
<option>Lot</option>
<option>Other</option>
</select><small><br>
<br>
Acres:</small><br>
<select name="Acres" size="1">
<option></option>
<option>5 or Less</option>
<option> 6 - 10</option>
<option>11 - 50</option>
<option>51 - 100</option>
<option>100 and Up</option>
</select><small><br>
<br>

<center><input type="submit" name="Submit" value="Search">&nbsp;&nbsp;<input type="reset" value=" Reset "></center>
</form>



SCRIPT

<%
'if the where clause is not in the requesting page (hidden field on lresults.asp),
'then let's build it
if Request("sWhereCriteria") = "" then

'price
tmp = Replace(Request("price"),",","") 'remove commas
tmp = Replace(tmp,"$","") 'remove dollar sign
'if there was a price selected
if tmp <> "" then
'if price selected has " or Less" in it
if InStr(tmp," or Less") then
'set the price less than or equal to selected price
sPrice = "(price <= " & Left(tmp, InStr(tmp, " or Less")-1) & ")"
'if price selected has " and Up" in it
elseif InStr(tmp, " and Up") then
'set the price greater than or equal to the selected price
sPrice = "(price >= " & Left(tmp, InStr(tmp, " and Up")-1) & ")"
else
'put the beginning and ending price range values into an array
aPrices = Split(tmp," - ")
'set the price between the selected price range
sPrice = "(price BETWEEN " & aPrices(0) & " AND " & aPrices(1) & ")"
end if
end if

'LandType
tmp = Request("Type")
'if the type of land was selected
if tmp <> "" then
'if type selected has " Acreage" in it
if InStr(tmp,"Acre") then
'set the type equal to acreage
sType = "(ACRG)"
'if Acreage selected has "Lot" in it
elseif InStr(tmp, "Lot") then
'set the type equal to the lot
sType = "LOT"
elseif InStr(tmp, "Farm") then
'set the type equal to farm / ranch
sType = "FM/RC"
elseif InStr(tmp, "Other") then
'set the type equal to farm / ranch
sType = "OTH"
else
'set type to nothing
sType = ""
end if
end if

'Acreage
tmp = Replace(Request("Acres"),",","") 'remove commas
tmp = Replace(tmp,"$","") 'remove dollar sign
'if there was a acreage selected
if tmp <> "" then
'if acres selected has " or Less" in it
if InStr(tmp," or Less") then
'set the acreage less than or equal to selected acreage
sAcres = "(Acres <= " & Left(tmp, InStr(tmp, " or Less")-1) & ")"
'if Acreage selected has " and Up" in it
elseif InStr(tmp, " and Up") then
'set the acreage greater than or equal to the selected acreage
sAcres = "(Acres >= " & Left(tmp, InStr(tmp, " and Up")-1) & ")"
else
'put the beginning and ending acreage range values into an array
aAcres = Split(tmp," - ")
'set the acreage between the selected acreage range
sAcres = "(Acres BETWEEN " & aAcres(0) & " AND " & aAcres(1) & ")"
end if
end if

'start building where clause of SQL statement
if sPrice <> "" then
sWhere = sPrice
end if
if sType <> "" then
if sWhere <> "" then
sWhere = sWhere & " AND " & sType
else
sWhere = sType
end if
end if
if sAcres <> "" then
if sWhere <> "" then
sWhere = sWhere & " AND " & sAcres
else
sWhere = sAcres
end if
end if

else 'get the where clause from the requesting page (hidden field of lresults.asp)
sWhere = Request("sWhereCriteria")
end if

'if the where criteria is blank, the user didn't enter any data or
'attempted to access the page from somewhere other than the search page
'or results page
if sWhere = "" then
'we have a problem - error page
Response.Redirect("no_search_data.asp")
end if

'build the SQL statement to get the records
sSql = "SELECT * FROM Land WHERE " & sWhere

'create the connection object
set oConn = Server.CreateObject("ADODB.Connection")
'connect the recordset object
Set oReturn = Server.CreateObject("ADODB.Recordset")

'open a connection to the database
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("data/cvsmls.mdb") & ";"
'put the recordset into the recordset object
oReturn.Open sSQL,oConn,1,2 This is Line 147

THIS LAST LINE THROWS THE ERROR!



OUTPUT FORM


<!-- #include virtual="/include/lmlsscript.asp" -->
<html>
<head>

</head>

<body>

<table border="0" cellspacing="0" cellpadding="0" style="width: 600px; height: 100px;">
<tbody>
<tr>
<td style="vertical-align: top; height: 100%;">

<%'Added form and hidden fields to page%>
<form name="frmResults" id="frmResults" action="" method="post">
<input type="hidden" value="<%=sWhere%>" name="sWhereCriteria" id="sWhereCriteria">
<input type="hidden" value="<%=iRecNum%>" name="iRecNum" id="iRecNum">
<table cellpadding="2" cellspacing="2" border="0" style="text-align: left; width: 100%;">
<tbody>
<tr align="center">
<td style="vertical-align: top;" rowspan="1" colspan="4"><br>
<%if iRecNum > 1 then%>
<input type="button" value="&lt;&lt; First" onClick="GoToFirst();">&nbsp;&nbsp;&nbsp;
<input type="button" value="&lt; Previous" onClick="GoToPrevious(<%=iRecNum%>)">&nbsp;&nbsp;&nbsp;
<%end if%>
<%if iRecNum < iRecCnt then%>
<input type="button" value="Next &gt;" onClick="GoToNext(<%=iRecNum%>,<%=iRecCnt%>)">&nbsp;&nbsp;&nbsp;
<input type="button" value="Last &gt;&gt;" onClick="GoToLast(<%=iRecCnt%>)">
<%end if%>
</td>
</tr>
<tr>
<td style="vertical-align: top; width: 20%;" rowspan="1" colspan="3"><small>Displaying Record <%=iRecNum%> of <%=iRecCnt%> Record(s) found.<br>
</small></td>
<td style="verticle-align: top; width: 20%; text-align: right;"><small>MLS #: <%=oReturn("mls#")%></small></td>
</tr>
<tr>
<td style="vertical-align: top; width: 20%;" rowspan="1" colspan="4">
<hr style="width: 100%; height: 2px;"></td>
</tr>
<tr>
<td style="vertical-align: top; width: 20%; text-align: right;"><small><span style="font-weight: bold;">Listing Price:</span></small></td>
<td style="vertical-align: top; width: 20%;"><small><%=FormatCurrency(oReturn("price"),0)%><br>
</small></td>
<td style="vertical-align: top;" rowspan="8" colspan="2">
<%
'see if the pic file exists...if not use the nopic.jpg file
sTmp = oReturn("pic")
sTmp = Right(sTmp,Len(sTmp) - InStrRev(sTmp,"/"))
set fso = server.createObject("Scripting.FileSystemObject")
if fso.FileExists(Server.MapPath("\images\homes\" & sTmp)) then
sImgFilename = oReturn("pic")
else
sImgFilename = "images/nopic.jpg"
end if
set fso = Nothing
%>
<img src="<%=sImgFileName%>" title="" alt="" style="width: 276px; height: 206px;" width="640" height="480"><br>
</td>
</tr>
<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>Prop. Type:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"><small><%=oReturn("Type")%><br>
</small></td>
</tr>

<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>Lot Size:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"><small><%=oReturn("LotSz")%><br>
</small></td>
</tr>

<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>- OR -</td>
<td></td></small>
</tr>

<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>Acres:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"><small><%=oReturn("Acres")%><br>
</small></td>
</tr>
<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>Address:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"><small><%=oReturn("Address")%><br>
</small></td>
</tr>
<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>City:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"><small><%=oReturn("City")%><br>
</small></td>
</tr>
<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>Zip Code:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"><small><%=oReturn("Zip")%><br>
</small></td>
</tr>
<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>County:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"><small><%=oReturn("County")%><br>
</small></td>
</tr>
<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>Remarks:<br>
</small></td>
<td style="vertical-align: top; width: 20%;" rowspan="1" colspan="3"><small><%=oReturn("rmrks")%></small><br>
</td>
</tr>
<tr>
<td style="vertical-align: top; width: 20%;" rowspan="1" colspan="4"><hr>
</td>
</tr>

<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; rowspan="1" colspan="4" text-align: center;"><small>Listing Office Information</small>
</td>
</tr>

<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>List Agent:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"
><small><%=oReturn("lstagent")%></small><br>
</td>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>List Office:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"
><small><%=oReturn("lstoffice")%></small><br>
</td>
</tr>

<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small>Phone:<br>
</small></td>
<td style="vertical-align: top; width: 20%;"
><small><%=oReturn("LstPhone")%></small><br>
</td>
<td><br></td>
<td><br></td>
</tr>

<tr>
<td style="vertical-align: top; width: 20%;" rowspan="1" colspan="4"><hr><br>
</td>
</tr>
<tr>
<td style="vertical-align: top; font-weight: bold; width: 20%; text-align: right;"><small><small>*<br>
</small></small></td>
<td style="vertical-align: top; width: 20%;" rowspan="1" colspan="3"><small><small><strong>Property Types:<br>ACRG = Acreage<br>FM/RC = Farm / Ranch<br>LOT = Lot<br>OTH = Other<br></strong></small></small><br>
</td>
</tr>


</tbody>
</table>
</form>

</td>
</tr>
</tbody>
</table>
<%
'clear objects and close db connection
set oReturn = Nothing
oConn.Close
set oConn = Nothing
%>



I have tried comparing these to the previous script and forms, and cannot seem to locate the problem. Can someone please help with this?:confused:



Rick

MetalStorm
03-05-2006, 09:11 PM
Looks like you've left out some quotes or something in your SQL statment.

In between these two lines:


'put the recordset into the recordset object
oReturn.Open sSQL,oConn,1,2 This is Line 147


Insert this:


Response.write sSQL
Response.end


That'll help you debug the error.

rickw
03-06-2006, 06:03 AM
MetalStorm,

Thanks for the assist. I knew it had to be something fairly simple, but had no idea how to go about finding the location of the problem. After inserting the code snippet and re-running the script a number of times, I was finally able to get it straightened out.

Thanks again,

Rick:thumbsup:

Bullschmidt
03-07-2006, 03:11 AM
And just for future reference:

Why do I get 80040E57 / 80040E07 errors?
http://www.aspfaq.com/show.asp?id=2289



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum