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

--

Phil Oneacre

phil@oneacre.name

Phone 404/210-2943

Fax 678/623-3274 (eFax)


Or, thinking along the lines of conditions, could the statement be constructed conditionally like this:

rsResults_cmd.CommandText = "SELECT URN, SchoolName, Add1, Add2, Add4, Add5, Postcode, Tel, Fax, Email, SchoolType, LEACode, Area, UID, LatestRWBooking, Confirmed

FROM dbo.Schools

WHERE"


If (Request.QueryString("URN") <>"") Then "(URN LIKE ?)" End If
If (Request.QueryString("SchoolName") <>"") Then "AND (SchoolName LIKE ?)" End If
If (Request.QueryString("Add1") <>"") Then "AND (Add1 LIKE ?)" End If
If (Request.QueryString("Add4") <>"") Then "AND (Add4 LIKE ?)" End If
If (Request.QueryString("Add5") <>"") Then "AND (Add5 LIKE ?)" End If

"ORDER BY SchoolName"

This code actually returns an error, but is the general idea OK or is it clutching at straws? And if the idea is reasonable, how could I make the code work?

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