<% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "askphil3" i = 0 sql = "select * from dealer where " if Request.Form("state") <> "" then ' *** search on state sql = sql + "GeoSort like '%"&Request.Form;("state")&"%' " i = 1 else if Request.Form("country") <> "" then ' *** search on country sql = sql + "GeoSort like '*%"&Request.Form;("country")&"%' " i = 1 end if end if if Request.Form("firm_name") <> "" then if i = 1 then sql = sql + "and " end if sql = sql + "FirmName like '%"&Request.Form;("firm_name")&"%' " i = 1 end if if Request.Form("last_name") <> "" then if i = 1 then sql = sql + "and " end if sql = sql + "LastName like '%"&Request.Form;("last_name")&"%' " i = 1 end if if Request.Form("specialty") <> "" then if i = 1 then sql = sql + "and " end if sql = sql + "Specialties like '%"&Request.Form;("specialty")&"%' " i = 1 end if ' added 4/25/02 by John ' there was a problem with the sql clause not working if the user ' didn't fill in any of the search fields if i = 0 then sql = "select * from dealer" end if Set RS = Conn.Execute(sql) If NOT RS.EOF Then Do While Not RS.EOF %> <% ' *** display FirmName %> <%=RS("FirmName")%>
<% ' *** get rid of * character in front of GeoSort if it exists country = RS("GeoSort") length = len(country) if left(country, 1) = "*" then length = length - 1 country = right(country, length) end if %> <% ' *** display FirstName and LastName %> <%=RS("FirstName")%>   <%=RS("LastName")%>
<% ' *** display Address %> <%=RS("Address")%>
<% ' *** display CityStateCode %> <%=RS("CityStateCode")%>
<% ' *** display Specialties %> <%=RS("Specialties")%>
<% link = RS("link") ' links returned from the db have a # character at the beginning ' and the end ' get rid of # character at beginning of link length = len(link) if left(link, 1) = "#" then length = length - 1 link = right(link, length) end if ' get rid of # character at end of link length = len(link) if right(link, 1) = "#" then length = length - 1 link = left(link, length) end if %> <%' *** display link %> <%=link%>

<% RS.MoveNext Loop Else %>No Records Found.
<% End If RS.Close Conn.Close %>

Another search?