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

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.

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