URGENT: insert database (asp), how to give search options on search form

Posted on 2003-03-13
Medium Priority
Last Modified: 2013-12-24
Dear all,

In frontpage 2002 (& probably 2000): Insert -> Database -> Results, I create a connection to an access database, etc, etc until I have a search button that searches the accessdb through a cuztomizable SQL query, it works and returns results.

The question is, I would like to have options for different sql queries! the user must choose whether he wants to search the country field, or the name field, etc. why is it so hard to do/invisible in frontpage. To make things more clear, here is the piece of ASP coding I changed to do that, within the code created by Frontpage:

The query line:
fp_sQry="SELECT * FROM DRI WHERE (Country Like  '%::search_text::%')"

is replaced by these:

if Request("lstcriteria")="Country or City" then fp_sQry="SELECT * FROM DRI WHERE (Country Like '%::search_text::%')"
if Request("lstcriteria")="Institution Name" then fp_sQry="SELECT * FROM DRI WHERE (Name Like '%::search_text::%')"

that way the query used is decided by the dropdown list "lstcriteria". i.e to search the country or the name. But this simple code modification doesn't work, as soon as I do it, the search returns all records on every search. Probably because there is some back-end coding that is done in other places by frontpage, that is not compatible with the two queries. Also, I know that opening the ASP page with frontpage automatically overwrites the modified code. I didn't open it, so that is not the problem.

How can I give the user options to search different fields??
Bare in mind that I am no programmer, I can write some code and some VB programs sometimes but that's it. ALso, I know that interdev can do this very easily, but I have no time to learn it! how to do it in a simple way?

Please answer.
Question by:tonikaram
  • 6
LVL 14

Expert Comment

ID: 8128925

Try something like that.

If Request.Form("lstcriteria")= "Country" Then
  fp_sQry="SELECT * FROM DRI Where country ...."
End if

If Request.Form("lstcriteria")= "Name" Then
  fp_sQry="SELECT * FROM DRI WHERE Name .. (Name =  '::Name::')"
End If

Let me know
LVL 14

Expert Comment

ID: 8128947
I forgot to tell you that your DRW should be dieted and the above code will be put after the <% which comes before the fp_sQry=.... and remove the original fp_sQry= ... line, because it will be between your if statements.

LVL 14

Expert Comment

ID: 8129048

Create a form
In the form put 1 drop down box
Put in it the names of the fields
Insert a textbox next to it (this will be the search box)
Name the drop down as lstCriteria and the textfield as SearchText.

Now, the user will type his/her search in the textbox and select the field from the drop down then presses submit.

The results DRW will take first the value that comes from the DROP down and see what it is,  accordingly decides which sql to use.

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

LVL 14

Expert Comment

ID: 8129129

Here are another two suggestions:

Dim strChoice, strTextSearch


if strChoice="Country" then
     fp_sQry="select ....... where country
     elseif strChoice="Name" then
           fp_sQry="select ....... where country
end if

Save the page then run the form and start searching.

Another suggestion is to use the Select Statement instead of the Elseif:

Select Case strChoice
  Case "Country"
  fp_sQry="select ....... where country
  Case "Name"
  fp_sQry="select ....... where country
  End Select

All the above code sample should come after:
<% end if %>

and before

Note that the original:
fp_sQry="SELECT .... will be deleted because you put other fp_Qry between your code.

LVL 14

Accepted Solution

hhammash earned 2000 total points
ID: 8129373
Hi again,

This is a working version,  I've tried it:

I will list the steps again:

Create a page and call it Results.asp, do not do anything yet,  just save it like that while it is blank.

Create a new page then,
1- Insert Form
2- Insert TextField
3- Insert Drop Down
4- Name the TextField as TxtSearch
5- Name the Drop Down as lstCriteria
6- Add the value to the Drop down with the names of the fields.  In the drop down you can put descriptive name of the field like Contact Title instead of ContactTitle which is a database field name in the database,  but in your SQl statement you should put the field name as it is in the Database.
6- Post the form to Results.asp
7- Save the form as search.asp

Open Results asp, then
1- Insert Database ...complete the steps then save it again.
After saving the page put it on Diet,  if you don't know diet follow this link to show you how: http://www.outfront.net/spooky/adv_drw.htm

2- Save the page again after putting it on diet, then insert the following code:

(Note: This code has been taken from my dieted DRW which is working very fine). Notice that the code should go after
<% end if%>
I will put the code that you have to enter between the dotted lines.

Here how it looks like
<% end if %>
If request.form("lstCriteria")="Country" Then
fp_sQry="SELECT * FROM Customers Where (Country='::TxtSearch::')"
End if

IF request.form("lstCriteria")="Contact Title" Then
fp_sQry="SELECT * FROM Customers Where (ContactTitle='::TxtSearch::')"
End if

The code between the dotted lines should be in this place,  of course you can change hte SQL if you want according to your needs:

Here are some SQL for (LIKE)

Begins with
Where(Country Like '::TxtSearch::%')

Ends with
Where(Country Like '%::TxtSearch::')

Where(Country Like '%::TxtSearch::%')

Does not begin with
Where(Country Not Like '::TxtSearch::%')

Does not End with
Where(Country Not Like '%::TxtSearch::')

Does not Contain
Where(Country Not Like '%::TxtSearch::%')

Hope it helps

Author Comment

ID: 8135226
thanks alot hhammash,

I see you have given alot of options!
ironically, it worked for me by simply adding one more criteria, and then changing the sql query as I want, including both searchable fields. now I have two text boxes, and leaving one blank will ignore that box. It is not what I intended, but works as well.

Now I am developping the search. I added three boxe (one as a drop-down), I need to display the results as links, want to ignore the ' character in the search text, and finally, I would like to use OR between the words entered instead of trying to match the exact string.

But the initial presentation of the search capability passed well!
I will work on the above, and might post a question about it pretty soon.. so keep an eye!

LVL 14

Expert Comment

ID: 8135520
Hi Tony,

Welcome at any time.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question