Wrox Programmer Forums
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 11th, 2006, 06:13 PM
Registered User
Join Date: Mar 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data type mismatch in criteria expression.

Hey guys,

I was hoping you can help me. I'm building a site for a real estate company. They want to search for houses for sale on a database. Multiple fields will be used in the search. The client may leave some fields empty during search.

This is the best I was able to come up with, but I get "Data type mismatch in criteria expression." error when the the fields are filled.

Dim PropertyCode, CityCode, StartPrice, EndPrice, MinBeds, MinBaths, MinBSF, MinAcres, YearBuilt
PropertyCode = Request("PropertyCode")
CityCode = Request("CityCode")
CityCode = Replace(CityCode, ", ", "','")
StartPrice = Request("StartPrice")
EndPrice = Request("EndPrice")
MinBeds = Request("MinBeds")
MinBaths = Request("MinBaths")
MinBSF = Request("MinBSF")
MinAcres = Request("MinAcres")
YearBuilt = Request("YearBuilt")

sql="SELECT * FROM Midfl_data WHERE PropertyCode = '" & PropertyCode & "' AND CityCode IN ('" & CityCode & "')"

If Request("StartPrice") = "0" Then
sql = sql & "AND Price >= '" & StartPrice & "'"
End If

If Request("EndPrice") = "99999999999" Then
sql = sql & "AND Price <= '" & EndPrice & "'"
End If

If Request("MinBeds") = "0" Then
sql = sql & "AND Bedrooms >= '" & MinBeds & "'"
End If

If Request("MinBaths") = "0" Then
sql = sql & "AND Bathrooms >= '" & MinBaths & "'"
End If

If Request("MinBSF") = "0" Then
sql = sql & "AND BuildingSquareFeet >= '" & MinBSF & "'"
End If

If Request("MinAcres") = "0" Then
sql = sql & "AND Acres >= '" & MinAcres & "'"
End If

If Request("YearBuilt") = "0" Then
sql = sql & "AND YearBuilt >= '" & YearBuilt & "'"
End If

sql = sql & "Order by Price"

rs.open sql, oConn, 1, 1

P.S. I've found that if I insert the code for each field choice in the sql statement that the result page only pulls records that have data in these fields. Some records may have some fields empty. So if a customer leaves out a field I need it to pull record not just the ones with some data.

Thank you guys for any help you can provide.
Old January 11th, 2006, 06:57 PM
Friend of Wrox
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41

You are inserting a value into a field that is the wrong data type or format. What DB are you using?

Next time you have a troubling SQL statement:

1..Mention the DB type
2..Coment out the execute part of code
3..Response.write the sql to the browser
4..Paste the query here (we want to see your values)
5..If its not to much trouble illustrate Data Types

If all this is one you will get a solution in one reply.

TIP 1 : Strip your sql back n run (insert one or two fields only) if it works the one or two you inserted are trouble free. Add another field n run, then another n run ...

Eventually your will find your problematic field

Tip 2: Get into the habbit of including spaces inside you conditional sql string, Eg:
sql = sql & "AND Price >= '" & StartPrice & "'"
Should be:
sql = sql & " AND Price >= '" & StartPrice & "'"

BTW: this is not your problem however a good habbit to get into

Tip 3: Post your sql related problems in the sql area of the forum.

Tip 4: Do you want to shorten your code? You dont need to:
PropertyCode = Request("PropertyCode")
CityCode = Request("CityCode")

Therefore you chnage:
sql="SELECT * FROM Midfl_data WHERE PropertyCode = '" & PropertyCode & "' AND

sql="SELECT * FROM Midfl_data WHERE PropertyCode = '" & Request("PropertyCode") & "' AND

I would also run trim functions around all your incoming values, Eg:
sql="SELECT * FROM Midfl_data WHERE PropertyCode = '" & trim(Request("PropertyCode")) & "' AND

Wind is your friend
Old January 13th, 2006, 04:05 PM
Registered User
Join Date: Mar 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks Matt, I tried what you said and it worked.
Old November 23rd, 2006, 11:09 AM
Registered User
Join Date: Nov 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

Hello imercha.
I'm writing an aplication that enables to view and book flights between different cities.
And I have a problem.I don't know from where I can get the citycodes.
I read that you're also working with citycodes, so I decided to ask you to help me.
Please write anything you know.
Best Regards.

Similar Threads
Thread Thread Starter Forum Replies Last Post
data type mismatch in criteria expression coreyjustin Classic ASP Basics 1 December 10th, 2007 06:49 PM
Data type mismatch in criteria expression Tawanda BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 0 May 5th, 2007 05:29 PM
data type mismatch in criteria expression xigler Access 3 March 23rd, 2007 08:54 AM
Data type mismatch in criteria expression. EDEN Access ASP 1 November 22nd, 2006 01:19 AM
Data type mismatch in criteria expression. kalchev ASP.NET 2.0 Professional 5 April 11th, 2006 11:08 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.