Can any wiz's figure this puzzle out!

[JD7453]

I have written this to the best of my knowledge but can't get it to run with the (Request.QueryString("menuPrice_range&quot & Request.QueryString("menuPrice_range1&quotis utilized. This is my results page for a MS Access database query. For some of you guys this will probably be a no brainer. Thanks for your time!!


<%
Dim tfm_orderby, tfm_order
tfm_orderby = &quot;sold_price&quot;
tfm_order = &quot;ASC&quot;
If(CStr(Request.QueryString(&quot;tfm_orderby&quot) <> &quot;&quot Then
tfm_orderby = Cstr(Request.QueryString(&quot;tfm_orderby&quot)
End If
If(Cstr(Request.QueryString(&quot;tfm_order&quot) <> &quot;&quot Then
tfm_order = Cstr(Request.QueryString(&quot;tfm_order&quot)
End If

Dim sql_orderby
sql_orderby = &quot; &quot; & tfm_orderby & &quot; &quot; & tfm_order
%>
<%
Dim rsResults__varCategory
rsResults__varCategory = &quot;%&quot;
if (Request.QueryString(&quot;menuCategory&quot <> &quot;&quot then rsResults__varCategory = Request.QueryString(&quot;menuCategory&quot
%>
<%
Dim rsResults__varCounty
rsResults__varCounty = &quot;%&quot;
if (Request.QueryString(&quot;menuCounty&quot <> &quot;&quot then rsResults__varCounty = Request.QueryString(&quot;menuCounty&quot
%>
<%
Dim rsResults__varFoundation
rsResults__varFoundation = &quot;%&quot;
if (Request.QueryString(&quot;menuFoundation&quot <> &quot;&quot then rsResults__varFoundation = Request.QueryString(&quot;menuFoundation&quot
%>
<%
Dim rsResults__sql_orderby
rsResults__sql_orderby = &quot;sold_price&quot;
if (sql_orderby <> &quot;&quot then rsResults__sql_orderby = sql_orderby
%>
<%
Dim rsResults__varPrice_range
rsResults__varPrice_range >= &quot;%&quot;
if (Request.QueryString(&quot;menuPrice_range&quot <> &quot;&quot then rsResults__varPrice_range = Request.QueryString(&quot;menuPrice_range&quot
%>
<%
Dim rsResults__varPrice_range1
rsResults__varPrice_range1 <= &quot;%&quot;
if (Request.QueryString(&quot;menuPrice_range1&quot <> &quot;&quot then rsResults__varPrice_range1 = Request.QueryString(&quot;menuPrice_range1&quot
%>
<%
Dim rsResults__varKeywords
rsResults__varKeywords = &quot;%&quot;
if (Request.QueryString(&quot;menuKeywords&quot <> &quot;&quot then rsResults__varKeywords = Request.QueryString(&quot;menuKeywords&quot
%>
<%
set rsResults = Server.CreateObject(&quot;ADODB.Recordset&quot
rsResults.ActiveConnection = MM_connBusDirectory_STRING
rsResults.Source = &quot;SELECT * FROM businesses WHERE approved = 'Yes' AND sold_price like '&quot; + Replace(rsResults__varPrice_range, &quot;'&quot;, &quot;''&quot + &quot;' AND sold_price like '&quot; + Replace(rsResults__varPrice_range1, &quot;'&quot;, &quot;''&quot + &quot;' AND category like '&quot; + Replace(rsResults__varCategory, &quot;'&quot;, &quot;''&quot + &quot;' AND county like '&quot; + Replace(rsResults__varCounty, &quot;'&quot;, &quot;''&quot + &quot;' AND foundation like '&quot; + Replace(rsResults__varFoundation, &quot;'&quot;, &quot;''&quot + &quot;' AND Address_1 like '%&quot; + Replace(rsResults__varKeywords, &quot;'&quot;, &quot;''&quot + &quot;%' OR approved = 'Yes' AND sold_price like '&quot; + Replace(rsResults__varPrice_range, &quot;'&quot;, &quot;''&quot + &quot;' AND sold_price like '&quot; + Replace(rsResults__varPrice_range1, &quot;'&quot;, &quot;''&quot + &quot;' AND category like '&quot; + Replace(rsResults__varCategory, &quot;'&quot;, &quot;''&quot + &quot;' AND county like '&quot; + Replace(rsResults__varCounty, &quot;'&quot;, &quot;''&quot + &quot;' AND foundation like '&quot; + Replace(rsResults__varFoundation, &quot;'&quot;, &quot;''&quot + &quot;' AND desc_short like '%&quot; + Replace(rsResults__varKeywords, &quot;'&quot;, &quot;''&quot + &quot;%' OR approved = 'Yes' AND sold_price like '&quot; + Replace(rsResults__varPrice_range, &quot;'&quot;, &quot;''&quot + &quot;' AND sold_price like '&quot; + Replace(rsResults__varPrice_range1, &quot;'&quot;, &quot;''&quot + &quot;' AND category like '&quot; + Replace(rsResults__varCategory, &quot;'&quot;, &quot;''&quot + &quot;' AND county like '&quot; + Replace(rsResults__varCounty, &quot;'&quot;, &quot;''&quot + &quot;' AND foundation like '&quot; + Replace(rsResults__varFoundation, &quot;'&quot;, &quot;''&quot + &quot;' AND busname like '%&quot; + Replace(rsResults__varKeywords, &quot;'&quot;, &quot;''&quot + &quot;%' ORDER BY &quot; + Replace(rsResults__sql_orderby, &quot;'&quot;, &quot;''&quot + &quot;&quot;
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 3
rsResults.Open()
rsResults_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = 10
Dim Repeat1__index
Repeat1__index = 0
rsResults_numRows = rsResults_numRows + Repeat1__numRows
%>

 

[BDC2]

Can you post your error, your querystring and your SQL statement as it is formed please.
BDC.

 

[JD7453]

I've shortened the statement a bit. The statement is running, but not correctly. When searched price range of 50000 to 100000, I'm getting results of any DB entry that includes the 50000 integer only. The 100000 does not seem to be acknowledged. I'm sure its the &quot;like&quot; operator but no other operator will work. I have included the Keywords query and statement which is working great. Thanks for your time!


<%
Dim rsResults__varKeywords
rsResults__varKeywords = &quot;%&quot;
if (Request.QueryString(&quot;menuKeywords&quot <> &quot;&quot then rsResults__varKeywords = Request.QueryString(&quot;menuKeywords&quot
%>
<%
Dim rsResults__varMinprice
rsResults__varMinprice = &quot;%&quot;
if (Request.QueryString(&quot;menuMinprice&quot >= &quot;&quot then rsResults__varMinprice = Request.QueryString(&quot;menuMinprice&quot
%>
<%
Dim rsResults__varMaxprice
rsResults__varMaxprice = &quot;%&quot;
if (Request.QueryString(&quot;menuMaxprice&quot <= &quot;&quot then rsResults__varMaxprice = Request.QueryString(&quot;menuMaxprice&quot
%>


<%
set rsResults = Server.CreateObject(&quot;ADODB.Recordset&quot
rsResults.ActiveConnection = MM_connBusDirectory_STRING
rsResults.Source = &quot;SELECT * FROM businesses WHERE approved = 'Yes' AND Address_1 like '%&quot; + Replace(rsResults__varKeywords, &quot;'&quot;, &quot;''&quot + &quot;%' AND sold_price like '%&quot; + Replace(rsResults__varMinprice, &quot;'&quot;, &quot;''&quot + &quot;%' AND sold_price like '%&quot; + Replace(rsResults__varMaxprice, &quot;'&quot;, &quot;''&quot + &quot;%' ORDER BY &quot; + Replace(rsResults__sql_orderby, &quot;'&quot;, &quot;''&quot + &quot;&quot;
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 3
rsResults.Open()
rsResults_numRows = 0
%>


Thanks again!

 

[JD7453]

Example of search and results: Searched between 50000 and 100000. Results of 50000, 150000, 350000, 450000. Basically and entry which includes 50000.

 

[BDC2]

to search within a range of numbers which is I think what you want try using the following SQL format:

SELECT * FROM businesses WHERE approved = 'Yes' AND Address_1 like '%&quot; + Replace(rsResults__varKeywords, &quot;'&quot;, &quot;''&quot + &quot;%' AND sold_price BETWEEN &quot; + CInt(rsResults__varMinprice) + &quot; AND CInt(rsResults__varMaxprice) + &quot; ORDER BY &quot; + Replace(rsResults__sql_orderby, &quot;'&quot;, &quot;''&quot + &quot;&quot;


BDC.

 

[JD7453]

I'm getting this error message:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/busdirectory/search_results.asp, line 71

rsResults.Source = &quot;SELECT * FROM businesses WHERE approved = 'Yes' AND Address_1 like '%&quot; + Replace(rsResults__varKeywords, &quot;'&quot;, &quot;''&quot + &quot;%' AND sold_price BETWEEN &quot; + CInt(rsResults__varMinprice) + &quot; AND CInt(rsResults__varMaxprice) + &quot; AND ORDER BY &quot; + Replace
------------------------------------------/(rsResults__sql_orderby, &quot;'&quot;, &quot;''&quot + &quot;&quot;


Thanks!

 

[BDC2]

Sorry, try this...

rsResults.Source = &quot;SELECT * FROM businesses WHERE approved = 'Yes' AND Address_1 like '%&quot; + Replace(rsResults__varKeywords, &quot;'&quot;, &quot;''&quot + &quot;%' AND sold_price BETWEEN &quot; + CInt(rsResults__varMinprice) + &quot; AND &quot; + CInt(rsResults__varMaxprice) + &quot; ORDER BY &quot; + Replace(rsResults__sql_orderby, &quot;'&quot;, &quot;''&quot
BDC.

 

[JD7453]

Sorry,

My post didn't show correct &quot;unexpected end of statement&quot; location. The error was:
+ &quot; ORDER BY &quot; + Replace(rsResults__sql_orderby,&quot;'&quot;, &quot;''&quot

The error arrow was on &quot;BY&quot; at + &quot; ORDER BY &quot;

 

[BDC2]

Are you still getting an error with my last post?

The format of the SQL statement should be:

SELECT * FROM businesses WHERE Var1 = 'Yes' AND Var2 LIKE '%Keyword%' AND Var3 BETWEEN Num1 AND Num2 ORDER BY Var4

...you get the idea...

Try putting the whole string into a variable and response.write() the string to screen to make sure the SQL statement has been constructed properly.
BDC.

 

[JD7453]

Sorry to keep posting, but I am at a standstill. The problem lies within the &quot; before ORDER BY. It is reading this as an end statement. If I change this at all I get the &quot;Internal Server Error&quot; message.

 

[BDC2]

You need to debug the query...

SQLquery = &quot;SELECT * FROM businesses WHERE approved = 'Yes' AND Address_1 like '%&quot; + Replace(rsResults__varKeywords, &quot;'&quot;, &quot;''&quot + &quot;%' AND sold_price BETWEEN &quot; + CInt(rsResults__varMinprice) + &quot; AND &quot; + CInt(rsResults__varMaxprice) + &quot; ORDER BY &quot; + Replace(rsResults__sql_orderby, &quot;'&quot;, &quot;''&quot

response.write(SQLquery)
response.end

Put this before the &quot;rsResults.Source =&quot; statement to see if the query has been formed correctly.

Check the syntax of the query, If you can't find anything wrong with it, post it here so I can see it.
BDC.