Skip to main content
I
iandobie
Known Participant
July 8, 2009
Question

SQL statement for search form

  • July 8, 2009
  • 1 reply
  • 2179 views

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

This topic has been closed for replies.

1 reply

P
poneacre
Participating Frequently
July 8, 2009

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

I
iandobieAuthor
Known Participant
July 14, 2009

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.

I
iandobieAuthor
Known Participant
August 25, 2009

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.


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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices