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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to Import and export files in WordPress. 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 : Click on Too…
Suggested Courses

770 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