OutFront Forums
     Home    Register     Search      Help      Login    

Follow Us
On Facebook
On Twitter
RSS
Via Email

Recent Posts
Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Sponsors
Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.
Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.
Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax
 

Data type mismatch in criteria expression?

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP, PHP, and Database >> Data type mismatch in criteria expression? 		Page: [1]
 
boddah

 

Posts: 24
Joined: 1/26/2004
Status: offline

  		Data type mismatch in criteria expression? - 1/26/2004 12:40:26   
Can't understand why I'm getting this error, I've checked fields in database to ensure identical names. This code represents a searchresults page so that matching criteria in any of the fields in a form on the previous page return a match and show on screen.

<%
set Conn = server.CreateObject("ADODB.connection")
Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.MapPath("agencydatabase.mdb")

SQL = "select areaID, propID, proppropertytype, propnoofbedrooms, propgarage, propprice from property where " & vbcrlf

SQL = SQL & "areaID = " & request.form("areaID") & " or " & vbcrlf
SQL = SQL & "proppropertytype = " & request.form("proppropertytype") & " or " & vbcrlf
SQL = SQL & "propnoofbedrooms = " & request.form("propnoofbedrooms") & " or " & vbcrlf
SQL = SQL & "propgarage = " & request.form("propgarage") & " and " & vbcrlf


SQL = SQL & "(propaddressl1 like '%" & request.form("keyword") & "%' or " & vbcrlf
SQL = SQL & "propaddressl2 like '%" & request.form("keyword") & "%')" & vbcrlf

Response.Write SQL
set RS = Conn.Execute(SQL)
do while not RS.eof%>
<%=RS("propID")%><br>
<%=RS("areaID")%><br>
<%=RS("proppropertytype")%><br>
<%=RS("propnoofbedrooms")%><br>
<%=RS("propgarage")%><br>
<%=RS("propprice")%><br>
<%RS.movenext
loop
Conn.Close%>

Thanks for any help.
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/26/2004 12:49:37   
What is the actual error? Are you leaving blank fields on your search form when searching?

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to boddah)
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/26/2004 12:52:09   
Is this an Access DB? if so, all field delimiters for text need to have apostrophes...

SQL = SQL & "proppropertytype = '" & request.form("proppropertytype") & "' or "

Also noticed I got rid of the VbCrLf at the end of those; I don't believe you really want to change the lines during the middle of your SQL statement...:)

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to rdouglass)
boddah

 

Posts: 24
Joined: 1/26/2004
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/26/2004 12:56:01   
Actual error is...

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

/searchresult.asp, line 85

Yep it is an Access DB.

Thanks for your help so far.

(in reply to rdouglass)
boddah

 

Posts: 24
Joined: 1/26/2004
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/26/2004 12:59:14   
Oh and that is with all fields completed so that a match should be made.

(in reply to boddah)
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/26/2004 12:59:19   
Did you see my second post? about the VbCrLf issue?

Oh, and by the way, Welcome to Outfront!:)

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to boddah)
boddah

 

Posts: 24
Joined: 1/26/2004
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/26/2004 14:27:03   
thanks for that, VbCrLf's removed and 's added, it seems to have done the trick :)

I am now trying to work out how to allow empty fields in any of the search criteria so that the search may be widened easily. At present is is very restrictive using ANDs between statements and using ORs pretty much returns the entire database as a match! Hoping somebody can point me in the right direction!

<%
set Conn = server.CreateObject("ADODB.connection")
Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.MapPath("agencydatabase.mdb")

SQL = "select proparea, propID, proppropertytype, propnoofbedrooms, propgarage, propprice from property where " & vbcrlf

SQL = SQL & "proparea = '" & request.form("proparea") & "' and "
SQL = SQL & "proppropertytype = '" & request.form("proppropertytype") & "' and "
SQL = SQL & "propnoofbedrooms = " & request.form("propnoofbedrooms") & " and "
SQL = SQL & "propgarage = '" & request.form("propgarage") & "' and "

SQL = SQL & "(propaddressl1 like '%" & request.form("keyword") & "%' or " & vbcrlf
SQL = SQL & "propaddressl2 like '%" & request.form("keyword") & "%')" & vbcrlf

'Response.Write SQL
set RS = Conn.Execute(SQL)
do while not RS.eof%><p>
<%=RS("propID")%><br>
<%=RS("proparea")%><br>
<%=RS("proppropertytype")%><br>
<%=RS("propnoofbedrooms")%><br>
<%=RS("propgarage")%><br>
<%=RS("propprice")%><br>
<%RS.movenext
loop
Conn.Close%>




Oh and here is the form on the previous page...




<form action="searchresult.asp" method="post">
<table>

<tr>
<td>Area:</td>
<td>
<select name="proparea">
<option>Stoke Bishop</option>
<option>Henleaze</option>
<option>Westbury on Trym</option>
<option>Sneyd Park</option>
</select>
</td>
</tr>

<tr>
<td>Property Type:</td>
<td>
<select name="proppropertytype">
<option>Flat</option>
<option>Terrace</option>
<option>Semi-Detached</option>
<option>Detached</option>
<option>Bungalow</option>
</select>
</td>
</tr>

<tr>
<td>No. of Bedrooms:</td>
<td>
<select name="propnoofbedrooms">
<option>1</option>
<option>2</option>
<option>3</option>
<option>4</option>
<option>5</option>
<option>6</option>
<option>7</option>
<option>8</option>
<option>9</option>

</select>
</td>
</tr>

<tr>
<td>Garage:</td>
<td>
<select name="propgarage">
<option>YES</option>
<option>NO</option>
</select>
</td>
</tr>

<tr>
<td>Keyword:</td>
<td><input type="text" name="keyword"></td>
</tr>

<tr>
<td colspan="2"><input type="submit" value="Search"></td>
</tr>

</table>
</form>

Thanks again.

(in reply to rdouglass)
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/26/2004 15:18:28   
You can take care of empty form fields by only including it in the SQL criteria if it contains data. I have an example here that has helped others before:

http://www.clarkinsurance.com/wildcards.htm

You can see how I deal with empty fields in the first red section of the code. It's an old example (I even used the DRW on that one...:):)) but still relevant. Hope it helps...

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to boddah)
boddah

 

Posts: 24
Joined: 1/26/2004
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/27/2004 11:10:13   
Thanks very much :)

Just one more thing how would I terminate after the propgarage statement below, I've tried various things but it doesn't seem to like it! I get the following error...


Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/searchresult.asp, line 100

SQL = SQL & "propgarage = '" & request.form("propgarage") & "'
--------------------------------------------------------------^




set Conn = server.CreateObject("ADODB.connection")
Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.MapPath("agencydatabase.mdb")

SQL = "select proparea, propID, proppropertytype, propnoofbedrooms, propgarage, propprice from property where " & vbcrlf

SQL = SQL & "proparea = '" & request.form("proparea") & "' and "
SQL = SQL & "proppropertytype = '" & request.form("proppropertytype") & "' and "
SQL = SQL & "propnoofbedrooms = " & request.form("propnoofbedrooms") & " and "
SQL = SQL & "propgarage = '" & request.form("propgarage") & "'

< Message edited by boddah -- 1/27/2004 11:11:08 >

(in reply to rdouglass)
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/27/2004 11:13:48   
quote:

SQL = SQL & "propgarage = '" & request.form("propgarage") & "'


Looks to me you're just missing your closing double quote:

SQL = SQL & "propgarage = '" & request.form("propgarage") & "'"

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to boddah)
boddah

 

Posts: 24
Joined: 1/26/2004
Status: offline

  		RE: Data type mismatch in criteria expression? - 1/27/2004 12:29:34   
Doh! :) I could swear I tried that. Thanks

(in reply to rdouglass)
Page:   [1]

All Forums >> Web Development >> ASP, PHP, and Database >> Data type mismatch in criteria expression? 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Copyright © 2009 Thomas Brunt's OutFront Inc. All Rights Reserved.