We help IT Professionals succeed at work.

Search Form

paulvb
paulvb asked
on
Here is my search page
http://www.registeryourpets.com/forms/humane/search.asp

How can I limit the search with multiple criteria?
Another words if I enter a city it will give all records with that city
If I enter a city and a state it not only gives all the records with that city but it gives me all the records with that state.
I want to limit the records to that city within that state.

I tried the "and" criteria but it does not return any records unless all the fields are filled out
Also how can I use wildcards ( " * " ) in the search?

P.S. I want to do this within Frontpage and not have to use "html" or "asp" code.

Thanks
Paulvb
Comment
Watch Question

Author

Commented:
??
EE's been down for 5 days or so Paul.  I've just been able to get back in now.  8-(

>>I want to do this within Frontpage and not have to use "html" or "asp" code

You really are trying to make this difficult aren't you?  ;-)

Post the code of the form you already have and I'll see what can be done.

Author

Commented:
This is my search.asp

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>LastName</title>
</head>

<body stylesrc="../../mainframe.htm">

<form BOTID="0" METHOD="POST" ACTION="searchresults.asp">
  <table BORDER="0">
    <tr>
      <td><b>Humane Society</b></td>
      <td><input NAME="LastName" VALUE="<%=Request("LastName")%>" size="30"></td>
    </tr>
    <tr>
      <td><b>Address</b></td>
      <td><input NAME="LocationAddress" VALUE="<%=Request("LocationAddress")%>" size="40"></td>
    </tr>
    <tr>
      <td><b>City</b></td>
      <td><input NAME="LocationCity" VALUE="<%=Request("LocationCity")%>" size="25"></td>
    </tr>
    <tr>
      <td><b>County</b></td>
      <td><input TYPE="TEXT" NAME="LocationCounty" VALUE="<%=Request("LocationCounty")%>" size="20"></td>
    </tr>
    <tr>
      <td><b>State</b></td>
      <td><input NAME="LocationState" VALUE="<%=Request("LocationState")%>" size="2"></td>
    </tr>
    <tr>
      <td><b>Zip Code</b></td>
      <td><input NAME="LocationZipCode" VALUE="<%=Request("LocationZipCode")%>" size="10"></td>
    </tr>
  </table>
  <br>
  <input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP" CLIENTSIDE
  SuggestedExt="asp" PREVIEW=" " startspan --><!--webbot bot="SaveAsASP" endspan -->
  <p>&nbsp;</p>
</form>

<h5>
<!--webbot bot="Substitution" S-Variable="Company" --><br>
<!--webbot bot="Substitution" S-Variable="Copyright" --><br>
Revised: <!--webbot bot="TimeStamp" S-FORMAT="%B %d, %Y" S-TYPE="EDITED" -->
 .
</h5>

</body>

</html>

And this is my searchresults.asp

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>LastName</title>
</head>

<body stylesrc="../../mainframe.htm">

<p>&nbsp;</p>
<table width="100%" border="1" cellspacing="0" bordercolor="#000000" cellpadding="2">
  <thead>
    <tr>
      <td bgcolor="#FFFA00"><b>Humane Society</b></td>
      <td bgcolor="#FFC800"><b>Address</b></td>
      <td bgcolor="#FFAF00"><b>City</b></td>
      <td bgcolor="#FF9600"><b>County</b></td>
      <td bgcolor="#FF7D00"><b>State</b></td>
      <td bgcolor="#FF6400"><b>Zip Code</b></td>
      <td bgcolor="#FF4B00"><b>Phone #</b></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" startspan
    s-columnnames="CustomerNumber,UserName,Password,FirstName,LastName,CustomersFullName,Address,City,County,State,ZipCode,HomePhone,WorkPhone,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,SameAddress,Comments,email,User_name,Browser_type,Timestamp,Remote_computer_name"
    s-columntypes="200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,200,11,200,200,200,200,135,200"
    s-dataconnection="HumaneSocieties" b-tableformat="TRUE" b-menuformat="FALSE"
    s-menuchoice="LastName" s-menuvalue="LastName" b-tableborder="TRUE" b-tableexpand="TRUE"
    b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE"
    i-ListFormat="0" b-makeform="FALSE" s-recordsource="HumaneSocieties"
    s-displaycolumns="LastName,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,WorkPhone"
    s-criteria="[LastName] EQ {LastName} | [LocationAddress] EQ {LocationAddress} | [LocationCity] EQ {LocationCity} | [LocationCounty] EQ {LocationCounty} | [LocationState] EQ {LocationState} | [LocationZipCode] EQ {LocationZipCode} |"
    s-order
    s-sql="SELECT * FROM HumaneSocieties WHERE (LastName =  '::LastName::' OR LocationAddress =  '::LocationAddress::' OR LocationCity =  '::LocationCity::' OR LocationCounty =  '::LocationCounty::' OR LocationState =  '::LocationState::' OR LocationZipCode =  '::LocationZipCode::')"
    b-procedure="FALSE" clientside SuggestedExt="asp"
    s-DefaultFields="LastName=&amp;LocationAddress=&amp;LocationCity=&amp;LocationCounty=&amp;LocationState=&amp;LocationZipCode="
    s-NoRecordsFound="No records returned." i-MaxRecords="256" i-GroupSize="0"
    BOTID="0" u-dblib="../../_fpclass/fpdblib.inc"
    u-dbrgn1="../../_fpclass/fpdbrgn1.inc"
    u-dbrgn2="../../_fpclass/fpdbrgn2.inc" tag="TBODY"
    preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" b-WasTableFormat="TRUE" b-ReplaceDatabaseRegion="FALSE" --><!--#include file="../../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM HumaneSocieties WHERE (LastName =  '::LastName::' OR LocationAddress =  '::LocationAddress::' OR LocationCity =  '::LocationCity::' OR LocationCounty =  '::LocationCounty::' OR LocationState =  '::LocationState::' OR LocationZipCode =  '::LocationZipCode::')"
fp_sDefault="LastName=&LocationAddress=&LocationCity=&LocationCounty=&LocationState=&LocationZipCode="
fp_sNoRecords="<tr><td colspan=7 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="HumaneSocieties"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="LastName"
fp_sMenuValue="LastName"
fp_iDisplayCols=7
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="18763" -->
    <tr>
      <td>
      <!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="CustomerNumber,UserName,Password,FirstName,LastName,CustomersFullName,Address,City,County,State,ZipCode,HomePhone,WorkPhone,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,SameAddress,Comments,email,User_name,Browser_type,Timestamp,Remote_computer_name"
        s-column="LastName" b-tableformat="TRUE" b-hasHTML="FALSE" clientside
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;LastName&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" b-makelink b-MenuFormat --><%=FP_FieldVal(fp_rs,"LastName")%><!--webbot
        bot="DatabaseResultColumn" endspan i-checksum="14417" -->
      </td>
      <td>
      <!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="CustomerNumber,UserName,Password,FirstName,LastName,CustomersFullName,Address,City,County,State,ZipCode,HomePhone,WorkPhone,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,SameAddress,Comments,email,User_name,Browser_type,Timestamp,Remote_computer_name"
        s-column="LocationAddress" b-tableformat="TRUE" b-hasHTML="FALSE"
        clientside
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;LocationAddress&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" b-makelink b-MenuFormat --><%=FP_FieldVal(fp_rs,"LocationAddress")%><!--webbot
        bot="DatabaseResultColumn" endspan i-checksum="38068" -->
      </td>
      <td>
      <!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="CustomerNumber,UserName,Password,FirstName,LastName,CustomersFullName,Address,City,County,State,ZipCode,HomePhone,WorkPhone,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,SameAddress,Comments,email,User_name,Browser_type,Timestamp,Remote_computer_name"
        s-column="LocationCity" b-tableformat="TRUE" b-hasHTML="FALSE"
        clientside
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;LocationCity&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" b-makelink b-MenuFormat --><%=FP_FieldVal(fp_rs,"LocationCity")%><!--webbot
        bot="DatabaseResultColumn" endspan i-checksum="30232" -->
      </td>
      <td>
      <!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="CustomerNumber,UserName,Password,FirstName,LastName,CustomersFullName,Address,City,County,State,ZipCode,HomePhone,WorkPhone,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,SameAddress,Comments,email,User_name,Browser_type,Timestamp,Remote_computer_name"
        s-column="LocationCounty" b-tableformat="TRUE" b-hasHTML="FALSE"
        clientside
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;LocationCounty&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" b-makelink b-MenuFormat --><%=FP_FieldVal(fp_rs,"LocationCounty")%><!--webbot
        bot="DatabaseResultColumn" endspan i-checksum="41531" -->
      </td>
      <td>
      <!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="CustomerNumber,UserName,Password,FirstName,LastName,CustomersFullName,Address,City,County,State,ZipCode,HomePhone,WorkPhone,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,SameAddress,Comments,email,User_name,Browser_type,Timestamp,Remote_computer_name"
        s-column="LocationState" b-tableformat="TRUE" b-hasHTML="FALSE"
        clientside
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;LocationState&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" b-makelink b-MenuFormat --><%=FP_FieldVal(fp_rs,"LocationState")%><!--webbot
        bot="DatabaseResultColumn" endspan i-checksum="36079" -->
      </td>
      <td>
      <!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="CustomerNumber,UserName,Password,FirstName,LastName,CustomersFullName,Address,City,County,State,ZipCode,HomePhone,WorkPhone,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,SameAddress,Comments,email,User_name,Browser_type,Timestamp,Remote_computer_name"
        s-column="LocationZipCode" b-tableformat="TRUE" b-hasHTML="FALSE"
        clientside
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;LocationZipCode&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" b-makelink b-MenuFormat --><%=FP_FieldVal(fp_rs,"LocationZipCode")%><!--webbot
        bot="DatabaseResultColumn" endspan i-checksum="39922" -->
      </td>
      <td>
      <!--webbot bot="DatabaseResultColumn" startspan
        s-columnnames="CustomerNumber,UserName,Password,FirstName,LastName,CustomersFullName,Address,City,County,State,ZipCode,HomePhone,WorkPhone,LocationAddress,LocationCity,LocationCounty,LocationState,LocationZipCode,SameAddress,Comments,email,User_name,Browser_type,Timestamp,Remote_computer_name"
        s-column="WorkPhone" b-tableformat="TRUE" b-hasHTML="FALSE" clientside
        preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;WorkPhone&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" b-makelink b-MenuFormat --><%=FP_FieldVal(fp_rs,"WorkPhone")%><!--webbot
        bot="DatabaseResultColumn" endspan i-checksum="20872" -->
      </td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="TRUE"
    b-menuformat="FALSE" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" i-groupsize="0"
    clientside tag="TBODY"
    preview="&lt;tr&gt;&lt;td colspan=64 bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot; width=&quot;100%&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;" --><!--#include file="../../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="65064" -->
  </tbody>
</table>

<h5>
<!--webbot bot="Substitution" S-Variable="Company" --><br>
<!--webbot bot="Substitution" S-Variable="Copyright" --><br>
Revised: <!--webbot bot="TimeStamp" S-FORMAT="%B %d, %Y" S-TYPE="EDITED" -->
 .
</h5>

<p>
&nbsp;
</p>

</body>

</html>

In frontpage criteria is where set my fields.

Thanks
Paulvb

Author

Commented:
?????
Paul...with no email notifs, doing this is going to be impossible.

I've got the files on my computer now....when my client leaves today I'll tackle it.
Ok Paul...client is late...so here goes.

I can do what you want to do, but it is going to involve having two sql statements chosen by an If statement.

You're going to have to edit the HTML mode to do this.  If you want me to show you that, then say go.  Otherwise, my hands are tied.

Author

Commented:

Author

Commented:
Yes

Author

Commented:
Yes I can do it in HTML as long I know what and why I am editing. You experts can get very complicated with your code.

Thanks
Paulvb
Ok...try this....and I cannot test drive it because I don't have your db here...but it should work.


When you go into HTML mode you will see something that looks like this:

fp_sQry="SELECT * FROM HumaneSocieties WHERE (LastName =  '::LastName::' OR LocationAddress =  '::LocationAddress::'
OR LocationCity =  '::LocationCity::' OR LocationCounty =  '::LocationCounty::' OR LocationState =  
'::LocationState::' OR LocationZipCode =  '::LocationZipCode::')"

OK....right before that, I want you to copy this:  (remember to put it into notepad first though and look for my comments...which are preceded by a ')



If request.form("LocationState")> "" AND request.form("LocationCity") > "" then
'the above should be all on one line, now a new line
fp_sQry="SELECT * FROM HumaneSocieties WHERE (LocationCity =  '::LocationCity::' AND ' OR LocationState = '::LocationState::')"
'the above should be all on one line, now a new line

fp_sDefault="LastName=&LocationAddress=&LocationCity=&LocationCounty=&LocationState=&LocationZipCode="
'the above should be all on one line, now a new line
fp_sNoRecords="<tr><td colspan=7 align=left width=""100%"">No records returned.</td></tr>"
'the above should be all on one line, now a new line
fp_sDataConn="HumaneSocieties"
'the above should be all on one line, now a new line
fp_iMaxRecords=256
'the above should be all on one line, now a new line
fp_iCommandType=1
'the above should be all on one line, now a new line
fp_iPageSize=0
'the above should be all on one line, now a new line
fp_fTableFormat=True
'the above should be all on one line, now a new line
fp_fMenuFormat=False
'the above should be all on one line, now a new line
fp_sMenuChoice="LastName"
'the above should be all on one line, now a new line
fp_sMenuValue="LastName"
'the above should be all on one line, now a new line
fp_iDisplayCols=7
'the above should be all on one line, now a new line
fp_fCustomQuery=False
'the above should be all on one line, now a new line
BOTID=0
'the above should be all on one line, now a new line
fp_iRegion=BOTID
'the above should be all on one line, now a new line

Else

'stop here
*****

Then, after the text that is already there (your currect sql statement write this:

End If.

Ok.  So what this is telling the page is that if both the state and city fields of search.asp have values then it should only return records that contain both those values...otherwise it proceeds as normal.

Does this make sense??

Author

Commented:
Yes! this can done, should I
Paste before or after the <%

???
<%or???
fp_sQry="SELECT * FROM Profiles WHERE (HumaneSociety =  '::HumaneSociety::' OR LocationAddress =  '::LocationAddress::' OR LocationCity =  '::LocationCity::' OR LocationCounty =  '::LocationCounty::' OR LocationState =  '::LocationState::' OR LocationZipCode =  '::LocationZipCode::')"
fp_sDefault="HumaneSociety=*&LocationAddress=*&LocationCity=*&LocationCounty=*&LocationState=*&LocationZipCode=*"
fp_sNoRecords="<tr><td colspan=8 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="HumaneSocieties"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="HumaneSociety"
fp_sMenuValue="HumaneSociety"
fp_iDisplayCols=8
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

-------------------------------------------------
Also can this be expanded for all the search fields?

Like this

If request.form("LocationState")> "" AND request.form("LocationCity") > "" AND request.form("LocationZipCode") > "" then
'the above should be all on one line, now a new line
fp_sQry="SELECT * FROM HumaneSocieties WHERE (LocationCity =  '::LocationCity::' AND ' OR LocationState  = '::LocationState::' AND ' OR LocationZipCode  = '::LocationZipCode::')" Etc, Etc.
'the above should be all on one line, now a new line

Thanks
Paulvb

Author

Commented:
I tried it both ways and FrontPage says

The contents of a FrontPage component have been modified.
These contents will be overwritten when you save this page.

Any suggestions?

Thanks
Paulvb
Yes...but you won't like it.

You know that I want to rewrite that sql statement so that it isn't done by FP and you have ultimate control.
You're on the right track Paul...I can see you're getting the hang of this ASP stuff......everything that needs to be interpretted by the server (anything logical like selecting records) needs to be INSIDE those <% and %> tags.

Author

Commented:
With all that I have been doing and the way you layed it out for me, I and starting to understand how some of the .asp code works.

So lets go for it, starting with the search form only.

Thanks for all of your patience
Paulvb

Author

Commented:
With all that I have been doing and the way you layed it out for me, I and starting to understand how some of the .asp code works.

So lets go for it, starting with the search form only.

Thanks for all of your patience
Paulvb
ok...give me a few mins.....I'll see what I can come up with.

Author

Commented:
I understand how you feel about control, I know how to program in MS Access, and all of my programing is done with VB. I never have and will never use a macro or use the properties box for a control (except for width and height that sort of stuff)

The reason I was against .asp code is that I do not know it, and I am a little uneasy about screwing it up.

So lets give it try.

Thanks
Paulvb
ok...the only other thing I need to know is, is this still your vettable database?  and which folder is the database in?

Author

Commented:
No the database name is humanesocieties, the table name is profiles.

I want to be able to change a few things to work with other databases.
I have created the database with the same structure as each other.


Thanks
Paulvb
which folder is humanesocieties.mdb in?
Ok, try this....I've put in a lot of comments so you can follow along (and learn too ;-)  I've assumed that your db is in the same folder as the page.

This is your SearchResults.asp page:

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>LastName</title>
</head>

<body stylesrc="../../mainframe.htm">

<p>&nbsp;</p>
<table width="100%" border="1" cellspacing="0" bordercolor="#000000" cellpadding="2">
 <thead>
   <tr>
     <td bgcolor="#FFFA00"><b>Humane Society</b></td>
     <td bgcolor="#FFC800"><b>Address</b></td>
     <td bgcolor="#FFAF00"><b>City</b></td>
     <td bgcolor="#FF9600"><b>County</b></td>
     <td bgcolor="#FF7D00"><b>State</b></td>
     <td bgcolor="#FF6400"><b>Zip Code</b></td>
     <td bgcolor="#FF4B00"><b>Phone #</b></td>
   </tr>
 </thead>
 <tbody>
<%

'im going to make some local variables to save trips to the server here.
LastName = request.form("LastName")
LocationAddress = request.form("LocationAddress")
LocationCity = request.form("LocationCity")
LocationCounty = request.form("LocationCounty")
LocationState = request.form("LocationState")
LocationZipCode = request.form("LocationZipCode")
WorkPhone = request.form("WorkPhone")

     'connect to your database
          set conn = server.createobject("adodb.connection")
          conn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("humanesocieties.mdb")&";"
          conn.open
     'to make this work with other databases, you only need to change the name after the Server.MapPath
     ' above and the tablename in the sql statements below

If request.form("LocationState")> "" AND request.form("LocationCity") > "" then
'the above should be all on one line, now a new line

'ok..now we tell it what to select.
'the format for this is SELECT * (which means everything) FROM tablename WHERE your parameters go here

     sql="SELECT * FROM Profiles WHERE LocationCity =  &"LocationCity&" AND LocationState = "&LocationState&""
     'the above should be all on one line, now a new line
ELSE

     sql="SELECT * FROM Profiles WHERE LastName = "&LastName&" OR LocationAddress =  "&LocationAddress&" OR LocationCity =  "&LocationCity&" OR LocationCounty =  "&LocationCounty&" OR LocationState =  "&LocationState&" OR LocationZipCode =  "&LocationZipCode&""
     'the above should be all on one line
End If

set rs= conn.execute(sql)

Do WHILE NOT rs.eof
'the above line means, do while we aren't at eof (end of file)

%>
   <tr>
     <td>
     <%=rs("LastName")%>
     </td>
     <td>
     <%=rs("LocationAddress")%>
     </td>
     <td>
     <%=rs("LocationCity")%>
     </td>
     <td>
     <%=rs("LocationCounty")%>
     </td>
     <td>
     <%=rs("LocationState")%>
     </td>
     <td>
     <%=rs("LocationZipCode")%>
     </td>
     <td>
     <%=rs("WorkPhone")%>
     </td>
   </tr>
   <%rs.movenext
   'the above means move to the next record in the db
   Loop
   'which means keep doing it until you get to the end of the db%>
 </tbody>
</table>

<h5>
<!--webbot bot="Substitution" S-Variable="Company" --><br>
<!--webbot bot="Substitution" S-Variable="Copyright" --><br>
Revised: <!--webbot bot="TimeStamp" S-FORMAT="%B %d, %Y" S-TYPE="EDITED" -->
.
</h5>

<p>
&nbsp;
</p>

</body>

</html>

Author

Commented:
I dropped everything into place, with no problem and I get this error.

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/forms/humane/searchresults1.asp, line 49

sql="SELECT * FROM Profiles WHERE LocationCity = &"LocationCity&" AND LocationState = "&LocationState&""
---------------------------------------------------^

Info:
My database is not in the same folder, so I changed the path to this

../../fpdb/humanesocieties.mdb

www.domain.com/forms/humane/searchresults.asp would be the form URL
(www.domain.com/fpdb/humanesocieties.mdb) would be the database URL

Thanks
Paulvb

Author

Commented:
I dropped everything into place, with no problem and I get this error.

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/forms/humane/searchresults1.asp, line 49

sql="SELECT * FROM Profiles WHERE LocationCity = &"LocationCity&" AND LocationState = "&LocationState&""
---------------------------------------------------^

Info:
My database is not in the same folder, so I changed the path to this

../../fpdb/humanesocieties.mdb

www.domain.com/forms/humane/searchresults.asp would be the form URL
(www.domain.com/fpdb/humanesocieties.mdb) would be the database URL

Thanks
Paulvb

Author

Commented:
OK Disregard the above statement I found a typo

sql="SELECT * FROM Profiles WHERE LocationCity = &"LocationCity&" AND LocationState = "&LocationState&""

&"LocationCity&" changed to "&LocationCity&"

that's fixed

----------------------------------------------------------------------------------------------

OK got passed that line, now I come up with this
I entered only TX for the search

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'HumaneSociety = OR
LocationAddress = OR LocationCity = OR LocationCounty = OR LocationState = tx OR LocationZipCode ='.

/forms/humane/searchresults1.asp, line 57

-----------------------------------------------------------------------------------------------

So then I tried Bexar (locationcity) and TX (locationstate)
(There is NO city named Bexar in the database)
and I get this

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

/forms/humane/searchresults1.asp, line 57

-----------------------------------------------------------------------------------------------------

So then I tried San Antonio(locationcity) and TX (locationstate)
(This is a city in the database)
and I get this

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'LocationCity = san antonio AND
LocationState = tx'.

/forms/humane/searchresults1.asp, line 57

Thanks
Paulvb
Paul...first I want to say "Way to go!"  I know this is frustrating for you, but finding that typo tells me that you are understanding this stuff.

If you don't mind, please send me the database.  The reason for this is that it appears my logic is faulty on the sql and unless I can test drive it by writing out the statement ever time I do a search I can't see what it is I'm actually telling the page to do.


Sorry....It's my blonde roots showing again.  ;-)

Author

Commented:
It is on it's way.

Thanks
Paulvb
ok...thanks...will take a peek.
Great Paul...thanks.....it's amazing what I can see when I actually run it against the table.  Silly me...i forgot the ' marks.....change the sql's to this:

    sql="SELECT * FROM Profiles WHERE LocationCity =  '"&LocationCity&"' AND LocationState = '"&LocationState&"'"
    'the above should be all on one line, now a new line
ELSE

    sql="SELECT * FROM Profiles WHERE LastName = '"&LastName&"' OR LocationAddress =  '"&LocationAddress&"' OR LocationCity =  '"&LocationCity&"' OR LocationCounty =  '"&LocationCounty&"' OR LocationState =  '"&LocationState&"' OR LocationZipCode =  '"&LocationZipCode&"'"
    'the above should be all on one line

Seems to work fine over here, but you try it out.

Author

Commented:
This works great, but now how can it work for narrowing it down with 3 form fields
LocationCity
LocationCounty
Location State

I tried to add to the sql like this:

If request.form("LocationState")> "" AND request.form("LocationCounty")> "" AND request.form("LocationCity")> "" then
'the above should be all on one line, now a new line

'ok..now we tell it what to select.
'the format for this is SELECT * (which means everything) FROM tablename WHERE your parameters go here

sql="SELECT * FROM Profiles WHERE LocationCounty = '"&LocationCounty&"' AND LocationCity = '"&LocationCity&"' AND LocationState = '"&LocationState&"'"
'the above should be all on one line, now a new line

but no luck

I understand that if  LocationCity AND LocationState do not have entries it skips over to

sql="SELECT * FROM Profiles WHERE HumaneSociety = '"&HumaneSociety&"' OR LocationAddress = '"&LocationAddress&"' OR LocationCity = '"&LocationCity&"' OR LocationCounty = '"&LocationCounty&"' OR LocationState = '"&LocationState&"' OR LocationZipCode = '"&LocationZipCode&"'"
'the above should be all on one line

End If

Tell if I'm on the right track here, if I write:

If request.form("LocationState")> "" AND request.form("LocationCounty")> "" AND request.form("LocationCity")> "" then

If all 3 fields are not entered it will skip over. Another words county and state are entered, it will skip over the sql

Can I use wildcards to fill in the empty fields, I have seen this where you can enter
part of a name Fort* and it will return Fort Worth, Fortington etc.

I must say even if this is wrong, I have learned so much with just this one question, you have no idea.

Thanks
Paulvb
I'm glad you are learning Paul.  That mean's I've not my job here.

Ok...let's talk about the wildcard stuff first.  If you want to make the search return anything that is "similar" to the search the person enters (which is really what that * woudl do right?) then you need to change the sql statement.

Remember back in math class....there was =, > and < ???  Well in ASP all those symbols apply and mean EXACTLY the same as they did then....however now you can add two others to your list....=NOT (which is "not equal to") and LIKE.  The LIKE is what we use when we are writing search engines and don't care if the results the search results return are EXACTLY like the word(s) typed in.

To use it in your case you would replace the

sql="SELECT * FROM Profiles WHERE HumaneSociety = '"&HumaneSociety&"' OR LocationAddress = '"&LocationAddress&"' OR LocationCity = '"&LocationCity&"' OR LocationCounty = '"&LocationCounty&"' OR LocationState = '"&LocationState&"' OR LocationZipCode = '"&LocationZipCode&"'"

With

sql="SELECT * FROM Profiles WHERE HumaneSociety LIKE '%"&HumaneSociety&"%' OR LocationAddress LIKE '%"&LocationAddress&"%' OR LocationCity LIKE '%"&LocationCity&"%' OR LocationCounty LIKE '%"&LocationCounty&"%' OR LocationState LIKE '%"&LocationState&"%' OR LocationZipCode LIKE '%"&LocationZipCode&"%'"

(I think I did that right)

The % is kind of like the wild card...it means "not exactly" or something like that.

Now, to get back to your question about further limiting the search string.

You are perfectly on the right track!  When you say no luck, I'm wondering what the error is....without trying in my guess is that you are adding to the I statement, but not closing it with an end if....which will really mess up the server's "brain".

Your entire logic statement telling the server when to search where would become:

If request.form("LocationState")> "" AND request.form("LocationCity") > "" then
'the above should be all on one line, now a new line

'ok..now we tell it what to select.
'the format for this is SELECT * (which means everything) FROM tablename WHERE your parameters go here

    sql="SELECT * FROM Profiles WHERE LocationCity =  '"&LocationCity&"' AND LocationState = '"&LocationState&"'"
    'the above should be all on one line, now a new line
   
ELSE IF request.form("LocationState")> "" AND request.form("LocationCity") > "" AND request.form("LocationCounty") > ""then
'the above should be all on one line, now a new line

    sql="SELECT * FROM Profiles WHERE LocationCity =  '"&LocationCity&"' AND LocationState = '"&LocationState&"' AND LocationCounty = '"&LocationCounty&"'"
    'the above should be all on one line, now a new line
     

ELSE

    sql="SELECT * FROM Profiles WHERE LastName = '"&LastName&"' OR LocationAddress =  '"&LocationAddress&"' OR LocationCity =  '"&LocationCity&"' OR LocationCounty =  '"&LocationCounty&"' OR LocationState =  '"&LocationState&"' OR LocationZipCode =  '"&LocationZipCode&"'"
    'the above should be all on one line
End If
End If

*******************************

Hey Paul....see what you can now do!  You could never have done this with the database results wizard....AND you are more learned for the experience.

Author

Commented:
Ok this is what I tried before but unlike VB the ELSE IF in .asp is ELSEIF
This works great now see below.

     If request.form("LocationCity")> "" AND request.form("LocationState")> "" then
sql="SELECT * FROM Profiles WHERE LocationCity = '"&LocationCity&"' AND LocationState = '"&LocationState&"'"

ELSEIf request.form("LocationCity")> "" AND request.form("LocationCounty")> "" AND request.form("LocationState")> "" then
sql="SELECT * FROM Profiles WHERE LocationCity = '"&LocationCity&"' AND LocationCounty = '"&LocationCounty&"' AND LocationState = '"&LocationState&"'"

ELSE

sql="SELECT * FROM Profiles WHERE HumaneSociety = '"&HumaneSociety&"' OR LocationAddress = '"&LocationAddress&"' OR LocationCity = '"&LocationCity&"' OR LocationCounty = '"&LocationCounty&"' OR LocationState = '"&LocationState&"' OR LocationZipCode = '"&LocationZipCode&"'"
'the above should be all on one line
End If

What I need to know is do I need to put in every combination there is?
City AND State
Cty AND County AND State
County AND State
Address AND City
Address AND City AND County
Address AND City AND County AND State
Address AND County
Address AND State
Etc., Etc.
or is there a shortcut

_________________________________________________________

Next I tried the statement above adding %
It returns every record regardless of what I search for.

Thanks
Paulvb
When you used the % did you use LIKE??

There must be a shortcut...let me think about it and see what I can come up with.

Author

Commented:
Yes I replaced the "=" with "LIKE", just you wrote previously.

Well I wrote an sql with all the possible combinations, and it works great, but what  a chore.

You know, I look back at the previous comments and code, and man I was saying this is rediculous. Now that you have educated me on search forms, I have some sort of clue on how .asp works (simple stuff).

I want to accept your answer, but I want to continue with the wildcards.
Should I post another question so you can earn more points, and break this question up a little? You have done more than 200 points worth.

Thanks
Paulvb
I'm not worried about the points stuff Paul.  I'm already #1 in this ta by a fair chunk.  Keep the points.

What I would suggest is....now that you are getting an idea about how ASP works, and hopefully, how it can give you more control over your pages than the wizards, nip on over to the ASP topic area and ask about the wildcards and streamlining your code in there.

I'm no ASP guru....I'm ok with the basics, and can troubleshoot my way out of more error messages, but it is not in any way intuitive for me.  The experts over in ASP have taken me out of many a jam.

Don't be afraid to post your current code, with all the If's and see what they can suggest to streamline it.  And in a separate question ask about the wildcards/like possibilities.

From here on in this question really isn't about FrontPage anyway.  8-)
Glad it's working for you Paul.

Author

Commented:
ClassyLinks here is the final code, your suggestion to ask the .asp people was brillant. They gave me a shorter way to do the search we discussed, and with wildcards.

I used some of your code at the beginning:

<%
'im going to make some local variables to save trips to the server here.
Veterinarian = request.form("Veterinarian")
LocationAddress = request.form("LocationAddress")
LocationCity = request.form("LocationCity")
LocationCounty = request.form("LocationCounty")
LocationState = request.form("LocationState")
LocationZipCode = request.form("LocationZipCode")
WorkPhone = request.form("WorkPhone")

'connect to your database
set conn = server.createobject("adodb.connection")
conn.ConnectionString="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../../fpdb/veterinarians.mdb")&";"
conn.open
'to make this work with other databases, you only need to change the name after the Server.MapPath
' above and the tablename in the sql statements below

strWhere =""
If Veterinarian>"" Then
strWhere=" and Veterinarian LIKE '" & Replace(Veterinarian,"*","%") & "'"
End IF

If LocationAddress>"" Then
strWhere=strWhere & " and LocationAddress LIKE '" & Replace(LocationAddress ,"*","%") & "'"
End If

If LocationCity>"" Then
strWhere=strWhere & " and LocationCity LIKE '" & Replace(LocationCity ,"*","%") & "'"
End If

If LocationCounty>"" Then
strWhere=strWhere & " and LocationCounty LIKE '" & Replace(LocationCounty ,"*","%") & "'"
End If

If LocationState>"" Then
strWhere=strWhere & " and LocationState LIKE '" & Replace(LocationState ,"*","%") & "'"
End If

If LocationZipCode>"" Then
strWhere=strWhere & " and LocationZipCode LIKE '" & Replace(LocationZipCode ,"*","%") & "'"
End If

sql="SELECT * FROM Profiles WHERE" & Mid(strWhere,5)

set rs= conn.execute(sql)

Do WHILE NOT rs.eof
'the above line means, do while we aren't at eof (end of file)
%>

  <TR>
    <TD HEIGHT="75"><%=rs("Veterinarian")%><BR>
    <%=rs("LocationAddress")%><BR>
    <%=rs("LocationCity")%>, <%=rs("LocationState")%>&nbsp;<%=rs("LocationZipCode")%><BR>
    <%=rs("LocationCounty")%> County<BR>
    <%=rs("WorkPhone")%></TD>
   
    <TD HEIGHT="75" NOWRAP>
   
     <P ALIGN="center">
    <% If rs("Website")="http://" then %>
    <img border="0" src="../../1images/transparentbackground.gif">
    <% ELSEIf rs("Website")="0" then %>
    <img border="0" src="../../1images/transparentbackground.gif">
    <% ELSEIf rs("Website")<>"" then %>
    <A HREF="<%=rs("Website")%>" target="contents"><img border="0" src="../../1images/spiderweb.gif"></A>
    <% Else %>
    <img border="0" src="../../1images/transparentbackground.gif">
    <% End If %>
    </TD>
   
    <TD HEIGHT="75">
   
    <p align="center">
    <% If rs("Email")="0" then %>
    <img border="0" src="../../1images/transparentbackground.gif">
    <% ELSEIf rs("Email")<>"" then %>
    <a href="mailto:<%=rs("Email")%>" target="_top"><img border="0" src="../../1images/mailbox.gif"></a>
    <% Else %>
    <img border="0" src="../../1images/transparentbackground.gif">
    <% End If %>
    </TD>
  </TR>
 
  <TR>
    <TD COLSPAN="3" ALIGN="left" HEIGHT="19">
    &nbsp;</TD>
  </TR>
 
  <%rs.movenext
'the above means move to the next record in the db
Loop
'which means keep doing it until you get to the end of the db%>
</TABLE>

Classy thanks again for all of your help.

Thanks
Paulvb

Explore More ContentExplore courses, solutions, and other research materials related to this topic.