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

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

This topic is closed to new replies. Start a new post to keep the conversation going.

15 replies

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

Translate
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.

Translate
poneacre
Participating Frequently
July 14, 2009

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.

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