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.

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

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