Hi,
First of all I think it is a bad idea to use "SELECT *" as this will return
more data then you are going to consume on the web page. The client (in
this case your web page) should only ever select data that it is going to
use. This is good practice.
However I would do something like this
strSQL = "SELECT Field1, Field2, Field3 "
strSQL = strSQL & "FROM Productions "
strSQL = strSQL & "WHERE (1 = 1) "
If Not strProductionNo = "" Then strSQL = strSQL & "AND (ProductionNo = '" &
strProductionNo & "') "
If Not strProductionTi tle = "" Then strSQL = strSQL & "AND (ProductionTitl e
LIKE '%" & strProductionTi tle & "%') "
If Not strSynopsis = "" Then strSQL = strSQL & "AND (Synopsis LIKE '%" &
strSynopsis & "%') "
strSQL = strSQL & "ORDER BY Field1"
This way the user can fill in as many of the search fields as he or she
wishes in order to narrow down the results. You need part of your WHERE
clause to be unconditional so I often use (1=1) because this never
eliminates any rows. Realize however if the user does not put in any search
criteria that all the rows in the table will be returned. For this reason
you may wish to put some kind of logic in place that will not perform the
search if all the fields are empty. Also, particularly when doing search
engines, it is a good idea to use a TOP clause to restrict the maximum
number of results. You should not rely on the underlying table/view to have
a reasonable number of rows. It may have 100's of thousands of rows. No
one really wants to scroll through 500 results from their search engine
anyway. Using a TOP 500 statement is a good idea to reduce overhead on your
server and if the user gets that many results back it's time to narrow the
search down anyway.
Cheers,
Ken.
"JP SIngh" <no**@none.co m> wrote in message
news:%2******** **********@TK2M SFTNGP10.phx.gb l...
Hi There
I am creating a search page and need help writing the code to build the
sql string.
I have 3 fields on the main page which a user can choose to enter search
terms in any of the 3 fields. My question is how do I write the logic so
it only includes the fields in the search criteria where the user has entered
anything.
Fields are
Production No
Production Title
Synopsis
The user may enter any, all or some of the search teams in the fields.
On the search page I am using lots of if conditions to build my sql string
like
strSQL = "Select * from Productions where "
now after where there are lots of permutations and combination like
production no could be empty or it could be filled in and the other fields
might be empty or filled in as well.
Does someone has a simple logic to build the correct sql String?
All fields are text.
Thanks
