Hi,
I'm working on a simple SEARCH BY City Option on a website. I'm Using DWMX and Access as my DB. So far, I can display the rsults of my search in ascending order or Descending order, but what I do not know how to setup is to have the search only send the results of my query. Not all the records. For example. I type New York in the search field. In my result page I want to receive only The records that contain New York in them. this is where I got so far. Ay help will be appreciated greatly.
Cheers.

<%
Dim rsContractors
Dim rsContractors_numRows
Dim city
Dim sql

city = Request.Form("city"
city = Replace("city", "'", "''"

sql = "SELECT CompanyName, Telephone, City, ZipPost "
sql = sql & "FROM Users "

If len(city) Then
sql = sql & "WHERE city = '" & city & "'"
End If

Response.Write sql
Set rsContractors = Server.CreateObject("ADODB.Recordset"
rsContractors.ActiveConnection = MM_connOELDB1_STRING
rsContractors.Source = sql
rsContractors.CursorType = 0
rsContractors.CursorLocation = 2
rsContractors.LockType = 1
rsContractors.Open()

rsContractors_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
rsContractors_numRows = rsContractors_numRows + Repeat1__numRows
%>

oops. that one is not the correct code. although that was one of my alternatives.

This is the code generated with DWMX:

<%
Dim rsContractors__Param
rsContractors__Param = "City <> 0"
If (Param <> "" Then
rsContractors__Param = Param
End If
%>
<%
Dim rsContractors
Dim rsContractors_numRows

Set rsContractors = Server.CreateObject("ADODB.Recordset"
rsContractors.ActiveConnection = MM_connOELDB1_STRING
rsContractors.Source = "SELECT City, Telephone, ZipPost, CompanyName FROM Users WHERE City = " + Replace(rsContractors__Param, "'", "''" + ""
rsContractors.CursorType = 0
rsContractors.CursorLocation = 2
rsContractors.LockType = 1
rsContractors.Open()

rsContractors_numRows = 0
%>

You've got a text box (with its id value set to "city" on search.asp and the form action is set to results.asp. The user types in New York, or whatever, then when they hit the submit button the form sends the content of the search box to results.asp and on results.asp you've got a recordset that selects the columns you want? Is that a fair summary of what you've got so far?

Its not clear from your code how you're picking up the value that's being passed, but when you build the recordset make sure that you use request.form("city" to pick up the value from the textbox and then assign that to a variable to filter your recordset by (if you're using the simple view in the recordset dialogue then your filter should have city, =, form parameter, city). This will allow results.asp to get the value from the form collection that's been sent to it from search.asp and use that value to filter the records.

Just as a thought, in your code it seems like you are only looking at 1 table (Users), you could consider setting up a table for cities... and let the user select which city they're interested in from a drop down list. This would reduce the chances of records not being found due to typo's e.g. 'NewYork' would be different to 'New York'

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

Thanks for your reply Simon, I appreciate your suggestions.

This is not been an easy process but I have enjoyed it so far. I've considered about having another table with only cities and and having a list. However, I want this to work. Call it hard headed, if you like but I think I'm very close.

I have refined the code a bit and tested on DWMX, and it works perfectly, but once I upload the page to the site there are no results. Please have a look at the code and let me know if I'm missing something.

<%
Dim rsContractors__MMColParam
rsContractors__MMColParam = "1"
If (Request.Form("SearchKeyword" <> "" Then
rsContractors__MMColParam = Request.Form("SearchKeyword"
End If
%>
<%
Dim rsContractors
Dim rsContractors_numRows

Set rsContractors = Server.CreateObject("ADODB.Recordset"
rsContractors.ActiveConnection = MM_connOELDB1_STRING
rsContractors.Source = "SELECT UserID, CompanyName, City, ZipPost, Telephone FROM Users WHERE City = '" + Replace(rsContractors__MMColParam, "'", "''" + "'"
rsContractors.CursorType = 0
rsContractors.CursorLocation = 2
rsContractors.LockType = 1
rsContractors.Open()

rsContractors_numRows = 0
%>

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
You've got a text box (with its id value set to "city" on search.asp and the form action is set to results.asp. The user types in New York, or whatever, then when they hit the submit button the form sends the content of the search box to results.asp and on results.asp you've got a recordset that selects the columns you want? Is that a fair summary of what you've got so far?

Its not clear from your code how you're picking up the value that's being passed, but when you build the recordset make sure that you use request.form("city" to pick up the value from the textbox and then assign that to a variable to filter your recordset by (if you're using the simple view in the recordset dialogue then your filter should have city, =, form parameter, city). This will allow results.asp to get the value from the form collection that's been sent to it from search.asp and use that value to filter the records.

Just as a thought, in your code it seems like you are only looking at 1 table (Users), you could consider setting up a table for cities... and let the user select which city they're interested in from a drop down list. This would reduce the chances of records not being found due to typo's e.g. 'NewYork' would be different to 'New York'

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Obvious question, but are you also uploading the database

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

Yes, I did. I have been sitting here trying this little crazy thing for the last 8 hrs, solid. My behind is already feeling the consequences. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Reading your code I don't see any problems with the code you've got there.
Are you sure that your results.asp is getting the value from search.asp?

It might be worth sticking in a line or 2 of debugging on results.asp
&lt;%= request.form("SearchKeyword" %&gt;
to see what's being sent

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

The funny part is that, when I take the "filter: City" out , and make the search I get the results. But when I add the "Filter: City" I get nothing. It seems that it doesn't like the combination I have. On the recordset window:

Name: rsContractors
Connection: connOELDB1
Table: Users
Columns: Selected
CompanyName, Telephone....
Filter: City =
Form Variable SearchKeywords (input name of ContractorSearch form)
Sort is empty or ASC

I Tried several other combinations, but no luck. It must be something small that I'm missing...ah well..isn't it always like that...a small thing.
Thanks for your help Simon. I'm not pulling my hairs out yet <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> The next course of action may be that I start from scratch retracing my steps....

Cheers.

You said you've tested in DMX and you get results (is that with the filter in place?) - so the recordset itself is correct. So if its failing when you add the filter then it sounds like either there is nothing that matches the value you've entered in the search box, or the value from the search box is not being used on results.asp

Did you stick in the print to screen debugging?
Do you get the right value passed into the page?

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

Yes, yes, yes. It worked. I went back to the search form and made sure I identified the text field and used clear names for everything. Went back to the recordset, applied the filter with the new names uploaded the pages and there I had a beautiful result. Well as beautiful it can get. hahaha. Thanks for sticking around with me through this adventure. Thanks Again Simon, I really appreciate it. Cheers. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>