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
August 28, 2009

>If (Request.QueryString("URN")<>"")
>    Then
>    if firstvar = false then
>        firstvar = true
>        "(URN LIKE ?)"
>    else
>         "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.

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