SQL statement for search form

Hi,

In in ASP VB and CS4 and SQL sever 2005. I have created a search form with 13 fields on it. However, I'm not sure how to craft the SQL statement to filter the recordset how I want it. I tried this:

SELECT URN, SchoolName, Add1, Add2, Add4, Add5, Postcode, Tel, Fax, Email, SchoolType, LEACode, Area, UID, LatestRWBooking, Confirmed
FROM dbo.Schools
WHERE URN = varURN OR SchoolName = varSchoolName OR Add1 = varAdd1 OR Add2 = varAdd2 OR Add4 = varAdd4 OR Add5 = varAdd5 OR PostCode = varPostCode OR Tel = varTel OR Fax = varFax OR Email = varEmail OR SchoolType = varSchoolType OR (LEACode = varLEACode AND Area LIKE varArea%)

and that works OK, but what I want is to be able to have wildcard in SchoolName. However, if I replace the 'WHERE SchoolName=varSchoolName' with 'SchoolName LIKE varSchoolName%' then it works OK on that field, but if I search on any other field, it doesn't filter at all, just returning the who;le recordset.

Please help!

The page code is here

Many thanks

SQL statement for search form

What you are asking can get quite complicated.?If you have two fields you want to allow people to search on either/or then I have written code something lile this psuedo code in the past.?Each situation has a different sql statement.?I believe you should revist how you will allow you client to search

if field a is not blank and field b is not blank then

?sql...?where field_a - var_a and field_b = var_b....

end if

if field a is not blank and field b is blank then

sql..?where field_a - var_a....

end if

if field a is blank and field b is not blank then

?sql..?where field_b = var_b....

endif

SQL statement for search form

thanks for that - I sorted it in the end by using a statement that was like this:

Where A LIKE %A% AND B LIKE %B% etc.

Thanks for your help though.

While that will work with a small amount of data, but when you get 100

of thousands of records you will need a much more complicated process

and indices to retrieve it quickly. Having 13 fields (IIRC) with

appropriate an index could be quite challenging. You did not say and I

did not ask how many records you might have.

Yes you're right - there are about 9000 records, but I did get this to

work by only displaying 20 records per page - if I tried to display

all of them on one results page, the process overran the memory

buffer. As it is, it'll do for me right now - next time it'd probably

be better to look into another way of doing it, and I'll explore your

approach at some point when I have time.

many thanks for your help.

Since you have gone this far try building an index of those fields and

see if it helps. That is a very large index but it could help. Worth a

try. Good luck.

I'll look into creating an index - I have no idea how at present but will do

some research! Many thanks

Hi again, I'm back on the case now. My previous solution seems too cumbersome to work properly. And I think there are too many fields to create separate statements for each combination of searches. So, if I create an idex for the database, what then? Does that automatically improve performance on searches made using the exisiting code as above (even if it is an inefficient search statement)?

Or alternatively, as you suggested, maybe a re-think of the search options is worthwhile.

Really, the user needs to be able to search on one or two combinations of up to 13 fields.

Initially I had envisaged using a drop-down menu for the user to select which column/s they are searching on, but didn't know how to construct the SQL statement that puts the selected column/s into the query. Is this a possible way to go?

thanks

--

Phil Oneacre

phil@oneacre.name

Phone 404/210-2943

Fax 678/623-3274 (eFax)

Or, thinking along the lines of conditions, could the statement be constructed conditionally like this:

rsResults_cmd.CommandText = ''SELECT URN, SchoolName, Add1, Add2, Add4, Add5, Postcode, Tel, Fax, Email, SchoolType, LEACode, Area, UID, LatestRWBooking, Confirmed

FROM dbo.Schools

WHERE''

If (Request.QueryString(''URN'') %26lt;%26gt;'''') Then ''(URN LIKE ?)'' End If
If (Request.QueryString(''SchoolName'') %26lt;%26gt;'''') Then ''AND (SchoolName LIKE ?)'' End If
If (Request.QueryString(''Add1'') %26lt;%26gt;'''') Then ''AND (Add1 LIKE ?)'' End If
If (Request.QueryString(''Add4'') %26lt;%26gt;'''') Then ''AND (Add4 LIKE ?)'' End If
If (Request.QueryString(''Add5'') %26lt;%26gt;'''') Then ''AND (Add5 LIKE ?)'' End If

''ORDER BY SchoolName''

This code actually returns an error, but is the general idea OK or is it clutching at straws? And if the idea is reasonable, how could I make the code work?

--

Phil Oneacre

phil@oneacre.name

Phone 404/210-2943

Fax 678/623-3274 (eFax)

Whoops.?I placed my response in the wrong place.?Below are #1 and #2

#1?----------------------------------

An index can speed up a search without doubt.

While your users need to search on 13 fields do they really need to?search on any combination of fields available.?For instance (a simple?example) on the database for my local swim/tennis club members wanted to?search by either name, street name, member type, and activity?preference.?I divided it into 4 different search forms based on how?people think about data as follow
- first name and/or last name - they are looking for a specific person
- street name - what members live on a particular street
- member type
- activity type

The later two search used drop downs to search.?The first two use the?like operator ('%var%).?While occasionally a member might want to know?all the xxx member type on a street, the list of members on a street?shows the member type and since the streets are not that large a member?can count on the screen.

Once I created these searches I created indices on first name, last?name, first %26amp; last name, member type, street name, and activity type. Our database is small (a thousand or so records in any on table) but I?wanted to minimize server load.

I do not know what field your users will search on but think about how?they might search.?You can initially offer certain combinations and?then ask for feedback.

Yes.?You can make the value a query string or a session variable when?you push the button.?I assume the query will be on a separate page. What ever you do is bound to involve a lot of hand coding.

#2 -----------------------------------

This is getting there.
- You said you were using drop downs.?They should be a full value and?therefore the like is not needed or desired.
- Your successful first if statement should produce code something like

SELECT URN, SchoolName, Add1, Add2, Add4, Add5, Postcode, Tel, Fax, Email, SchoolType, LEACode, Area, UID, LatestRWBooking, Confirmed where urn = ?.

Each additional item you will need to add an 'and'

SELECT URN, SchoolName, Add1, Add2, Add4, Add5, Postcode, Tel, Fax, Email, SchoolType, LEACode, Area, UID, LatestRWBooking, Confirmed where urn = ? and add1 like ?

So you have to have intelligence build in that id's the first parameter.?I have?done in Delphi is something like (semi code)

If (Request.QueryString(''URN'')%26lt;%26gt;'''')
?Then
?if firstvar = false then
?firstvar = true
?''(URN LIKE ?)''
?else
''AND (URN LIKE ?)
?end if
end if
If (Request.QueryString(''SchoolName'')%26lt;%26gt;'''')
?Then
?if firstvar = false then
?firstvar = true
?''(SchoolName LIKE ?)''
?else
''AND (SchoolName LIKE ?)
?end if
end if

%26gt;If (Request.QueryString(''URN'')%26lt;%26gt;'''')
%26gt;?Then
%26gt;?if firstvar = false then
%26gt;?firstvar = true
%26gt;?''(URN LIKE ?)''
%26gt;?else
%26gt; ''AND (URN LIKE ?)

If you are combining conditions in the where clause with AND, you can simplify the above code by including a condition that is always true and including the first AND with that condition. Then you do not have to keep track of whether the field is the firstvar. For example, use

''Select * from mytable where 1=1 AND''

This will eliminate many lines of code, especially with 13 fields. But fot that many fields I would also consider building a routine that loops through the text fields rather than hardcoding them.

Great point bregent.

Hi,

many thanks for your help. I did also get some help from another forum which went along similar lines but slightly different, like this:

''SELECT * FROM Table WHERE 1=1 AND''

If Trim(rsResults__varURN) %26lt;%26gt; ''%'' Then sql = sql %26amp; ''AND URN LIKE ? ''

If Trim(rsResults__varSchoolName) %26lt;%26gt; ''%'' Then sql = sql %26amp; ''AND SchoolName LIKE ? ''

The thing that I hadn't considered was that I also had to do the same with the parameters, so I also had to include:

If Trim(rsResults__varURN) %26lt;%26gt; ''%'' Then rsResults_cmd.Parameters.Append rsResults_cmd.CreateParameter(''param1'', 200, 1, 255, ''%'' + rsResults__varURN + ''%'') ' adVarChar

etc.

This seems to work properly anyway. Thankfully! So thanks again.

In terms of indices, do I just dreate the index on the database in the SQL server Management console?

Use the console, it is easier than a script.?Glad you got good info.