Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Mar 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Microsoft JET Database Engine error '80040e07'

    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 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?



    Rick

  • #2
    New Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Looks like you've left out some quotes or something in your SQL statment.

    In between these two lines:
    Code:
    'put the recordset into the recordset object
    oReturn.Open sSQL,oConn,1,2 This is Line 147
    Insert this:
    Code:
    Response.write sSQL
    Response.end
    That'll help you debug the error.

  • #3
    New to the CF scene
    Join Date
    Mar 2006
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    And just for future reference:

    Why do I get 80040E57 / 80040E07 errors?
    http://www.aspfaq.com/show.asp?id=2289
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •