How to write Advance Searching SQL Query

[Fary4u]

Baically how i can differentiate Query like i need 1st Query + including 2nd part + including 3rd part & so on & so forth

SQL Query i need is like this

Residential (Not Commercial) but Tolet (Not Sale) and 2 bedrooms (Not 3) with in 40 to 70 Price (<>) Rent also include in City (Not Area) but Area (With in City chose Area) Town
EXPLAIN
i've got 4 property in DB 3 are Sale & 1 are Rented + 3 Sale Property i've got 1 in Commercial & 2 in Residential Property + 2 Residential one's i've got 1 in x area & 2 in y area (but Y is Commercial) is these both are in some city & 1 in some other city + 1 is less then 1000 & 1 is greater then 1000

these filed i've got in database, it's taking values from Dropdown + Combo box + Radio & Text filed

Here is Coding i'm writing database fields as well but this coding is working but not detail search.

chkres = Request.form("chkres")         ' ( DB Filed as Type - Resd, Comcl, Othr )
chkcom = Request.form("chkcom")     ' ( DB Filed as Type - Resd, Comcl, Othr )
chkoth = Request.form("chkoth")         ' ( DB Filed as Type - Resd, Comcl, Othr )
strCat = Request.form("strCat")         ' ( DB Filed as Category_ID - Sale or Tolet )
intPrice = Request.form("intPrice")      ' ( DB Filed as Price comprasion Min )
intPricem = Request.form("intPricem")      ' ( DB Filed as Price compras  Max )
strbed = Request.form("strbed")         ' ( DB Filed as Beds - 0 <> 1 , 2 , 3 )
strText = cstr(Request.form("strText")) '(DB Filed as city & town Text Search)
 
    if chkres = "yes" then
        chkres = "Residential"
    end if
    if chkcom = "yes" then
        chkcom = "Commercial"
    end if
    if chkoth = "yes" then
        chkoth = "Others"
    end if    
 
        strTextWhere = " and (uCase(db_city) like '%"&strText&"%'"
        strTextWhere = strTextWhere & " or uCase(db_town) like '%"_
            & strText & "%')"    
 
'  intPriceWhere = " and Price < " &intPrice
   intPriceWhere = " and Price >= " & intPrice & " and Price <=" & intPricem 
 
        strCatWhere = " and property.category_ID = '" &strCat&"'"
 
        strChk1 = " and property.Type = '" &strChk1&"'"            
'        strChk2 = " and property.Type = '" &strChk2&"'"        
'        strChk3 = " and property.Type = " & strChk3    ' Check box options
 
        strbed = " and property.Beds < '" &strbed&"'"
 
    set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open ConString
 
    sqlText = "select distinct(property.property_ID), " 
    sqlText = sqlText & "Property.Price ,"
    sqlText = sqlText & "Property.Style ,"
    sqlText = sqlText & "Property.Category_ID ,"
    sqlText = sqlText & "Property.Beds ,"    
    sqlText = sqlText & "Property.address ,"    
    sqlText = sqlText & "Property.offer ,"    
    sqlText = sqlText & "Property.Image_1 ,"    
    sqlText = sqlText & "Property.Type "
    sqlText = sqlText & "from property " 
    sqlText = sqlText & "where property.property_ID = "
    sqlText = sqlText & "property.property_ID"
sqlText = sqlText & strCatWhere & strTextWhere & intPriceWhere & strChk3 & etc.
 
 

it's quite completed query if some body help me to solve this i'm realy thx.
Hope u get my point.

[Fary4u]

Hi i'm just can't sort it out can u plz look the coding give me the possiblity reply

[Fary4u]

What the simplest way to run this kind of problem ?

[jhardman]

What the simplest way to run this kind of problem ?

Instead of replying to bump your post, please consider sending a PM to an forum expert. In answer to your question, I often form complex queries like this:

query = "SELECT * FROM properties WHERE "
if request("maxPrice") <> "" then
   query = query & "Price <= " & request("maxPrice") & " AND "
end if
 
if request("minPrice") <> "" then
   query = query & "Price >= " & request("minPrice") & " AND "
end if
 
if request("tolet") <> "" then
   query = query & "tolet = 'True' AND "
end if
 
'etc.  Notice the where clause of the query ends with " AND " no matter what
 
'you need to trim the " AND " off the end
query = left(query, len(query)-5)

Let me know if this helps.

Jared

[jhardman]

Right before you send this query to the db, check to see what the final output is:

response.write "Query: " & query & "<br>" & vbNewLine

printing out this query is a good place to start troubleshooting. If you don't see the problem just by looking, you can copy the query into a SQL query analyzer which will tell you if you got the right answer.

Please print out this query and then tell me what you get.

Jared