Searching from with radio & dropdown & checkbox

[Fary4u]

Hi any body tell me where is the problem ?

i'm trying 2 search but desnt' come with nothing plz help me in this regards

[HTML]
chkres = Request.form("chkres")
chkcom = Request.form("chkcom")
chkoth = Request.form("chkoth")
if chkres = "yes" then
chkres = "Residential"
end if
if chkcom = "yes" then
chkcom = "Commercial"
end if
if chkoth = "yes" then
chkoth = "Others"
end if

strText = cstr(Request.form("strText"))
strText = uCase(strText)
chkprc1 = Request.form("chkprc1")
intPrice = Request.form("intPrice")
intPricem = Request.form("intPricem")
bedrooms = Request.form("bedrooms")

strTextWhere = ""
intPriceWhere = ""
stroptWhere = ""

strTextWhere = " and (uCase(db_city) like '%"&strText&"%'"
strTextWhere = strTextWhere & " or uCase(db_town) like '%"_
& strText & "%')"

intPriceWhere = " and Price > " & intPrice & " and Price<" & intPricem
stroptWhere = " and property.category_ID = '" &chkopt&"'"

set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConString

sqlText = "select distinct(Property.Property_ID), "
sqlText = sqlText & "Property.Description ,"
sqlText = sqlText & "Property.Price ,"
sqlText = sqlText & "Property.Style ,"
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 & strTextWhere & stroptWhere & intPriceWhere
[/HTML]

[jeffstl]

Since you have such a long and cancatonated SQL string, you might want to do a print out of the final SQL string after running and paste it in here. It's hard to determine what may be wrong with the SQL without seeing it in totality.

Also, when you say its not working do you mean are you getting an error? Or the SQL is not returning any results? If the SQL is not returning results make sure the criteria will actually match data in the database.

[Fary4u]

actually i'm trying to make some ASP search using DB

the problem in a query

if 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 these both are in some city & 1 in some other city + 1 is less then 1000 & 1 is greater then 1000

it's quite completed query if some body help me to solve this i'm realy thx.

it's taking values from Dropdown + Combo box + Radio & Text filed

here is my code some thing like

[PHP]
strTextw = " and (uCase(db_city) like '%"&strText&"%'"
strTextw = strTextw & " or uCase(db_town) like '%"_
& strText & "%')"

intPrice = " and Price > " & intPrice & " and db_pric<" & intPricem

strCat = " and property.db_sale_rent = '" &strCat&"'"

chkresdnt = " and property.Type = '" &strChkbox1&"'"
chkcomer = " and property.Type = '" &strChkbox2&"'"

sqlText = "select distinct(Property.Property_ID), "
sqlText = sqlText & "Property.Type "
sqlText = sqlText & "from Property "
sqlText = sqlText & "where Property.Property_ID = "
sqlText = sqlText & "Property.Property_ID"

sqlText = sqlText & strTextw & strCat & intprice & chkresdnt & chkcomer
[/PHP]

any information you need plz let me know

thx in advance
Fary

[Fary4u]

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

like i need the query i need is like

Residential but Tolet and 2 bedrooms with in 40 to 70 Price Rent also include in Manchester but Cheethamhill area

Here is Coding i'm using with database fileds
[PHP]
chkres = Request.form("chkres") ' Resident Checkbox ( in Database Filed as a Type - Residential, Commerical or Other )
chkcom = Request.form("chkcom") ' Commercial Checkbox ( in Database Filed as a Type - Residential, Commerical or Other )
chkoth = Request.form("chkoth") ' Other Checkbox ( in Database Filed as a Type - Residential, Commerical or Other )
strCat = Request.form("strCat") ' Category Radio Button ( in Database Category_ID - Sale or Tolet )
intPrice = Request.form("intPrice") ' Price ( in Database Filed as a Price comprasion Min & Max )
intPricem = Request.form("intPricem") ' Price ( in Database Filed as a Price comprasion Min & Max )
strbed = Request.form("strbed") ' Beds ( in Database Filed as a Beds - 0 , 1 , 2 , 3 , . . .)
strText = cstr(Request.form("strText")) ' City & Area Seach ( in Database Filed as a db_city & db_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 = ""
intPriceWhere = ""
strCatWhere = ""
strChk1 = ""
strChk2 = ""
strChk3 = ""
strbed = ""

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&"'" ' Check box options
' strChk2 = " and property.Type = '" &strChk2&"'" ' Check box options
' 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

[/PHP]
Hope u get my point.

[jeffstl]

I do know what your saying, but I dont want to go through all this when I dont even know what the problem is.

What error are you seeing? When does the error occur (which line)?

And finally, please do a response.write on the FINAL sqlText string at the very bottom and paste the output here.

This way we can see the entire SQL string and maybe determine why it is not executing properly better or if there is a problem with the order in which you are building the string.

[Fary4u]

select distinct(property.property_ID), Property.Price ,Property.Style ,Property.Category_ID ,Property.Beds ,Property.address ,Property.offer ,Property.Image_1 ,Property.Type from property where property.property_ID = property.property_ID and property.category_ID = '2' and (uCase(db_city) like '%MANCHESTER%' or uCase(db_town) like '%MANCHESTER%') and Price < 10000000

here is the output & it's execute but if i need to add beds then what do i've to do ?

any response would be great thx for the help

same kind of thread is here
http://www.thescripts.com/forum/thread782900.html

[Fary4u]

is that the complated code ?

[jeffstl]

select distinct(property.property_ID), Property.Price ,Property.Style ,Property.Category_ID ,Property.Beds ,Property.address ,Property.offer ,Property.Image_1 ,Property.Type from property where property.property_ID = property.property_ID and property.category_ID = '2' and (uCase(db_city) like '%MANCHESTER%' or uCase(db_town) like '%MANCHESTER%') and Price < 10000000

here is the output & it's execute but if i need to add beds then what do i've to do ?

any response would be great thx for the help

same kind of thread is here
http://www.thescripts.com/forum/thread782900.html

This seems like an unnecessary comparison in your string
property.property_ID = property.property_ID

Other then that I don't see anything inherently wrong with the string, though if your column property_ID is set up as a string. But you also stated that the SQL is executing ok but that you want to add a search for bedrooms?

Assuming that the column for bedrooms is a number at the end simply add the string:
and bedroomsize =" & Variable & "
or
and bedroomsize=2

[Fary4u]

What's the simplest way to solve my problem ?

there is no problem in my coding it's just not executed properly which i need .

By the way thx for cooperation