How do I return all results from my database from a querystring value?

Hi

I have a Listbox that filters my recordset by code e.g. FF, CL.  The problem I have is that in the list box I want the user to be able to select 'ALL' to display all the records with all the different code from the dropdown.  I'm sure I have have just passed '%' as the value before and this just brings up all the field but its not working.  
Does this only work with number fields?

My URL string:

http://localhost/webs/report.asp?type=sale&datefrom=1-1-2008&dateto=5-2-2008&code=%&submit=filter
<select name="code" id="code">
                        <option value="%">ALL</option>
                        <option value="CL">CL</option>
                        <option value="E">E</option>

Open in new window

LVL 9
djpazzaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
Including code=% OR code=CL OR code=E on the querystring will not automatically give you results from the database. It all boils down to what you are doing with the sent querystring on the receiving script.
On your script you could something like:
Dim code, qry
code = Request("code")
IF "%"=trim(code) Then
 code = ""
End IF
 qry = "SELECT * FROM Tablename where fieldname LIKE " & code & "%"
then query your db based on qry. If you have further questions. post your code.
0
djpazzaAuthor Commented:
By passing the codes in the URL I can just see the matches from the database where the field code has the querystring code.  I'm not using LIKE as I need it to be an extact match to the codes as some codes are simular to others.

Below is some sample data, I want to return all records with all codes.


My SQL statement:

SELECT *
FROM Cars  INNER JOIN Car_models  ON cars.model = car_models.id
WHERE type = 'MMCol5' AND code='MMCol6' AND dateadded >= datevalue('MMCol2') AND dateadded <= datevalue('MMCol3')
ORDER BY MMCol4 DESC

I'm using Dreamweaver so the MMCol values are just referenced to the request.querystring value.

CID   CODE  TYPE       DATEADDED
1     CL    sale       28/01/2008
2     E     purchase   29/01/2008

Open in new window

0
hieloCommented:
>>By passing the codes in the URL I can just see the matches from the database where the field code has the querystring code.
Yes. This part was obvious. I know what you are trying to do but not sure what SQL construct you were using

>> I'm not using LIKE
That's why you "%" does not work. These wild card operators work only if you use the LIKE clause (fieldname LIKE 'a%'), but do not work if you use equality (fieldname='a%').

For your purposes you want to check for the code first and if you see % then don'd include it on the query at all:

Dim qry
qry = ""
IF "%" = Trim(Request("code")) Then

qry = qry & " SELECT * " &
qry = qry & " FROM Cars  INNER JOIN Car_models  ON cars.model = car_models.id " &
qry = qry & " WHERE type = 'MMCol5' AND dateadded >= datevalue('MMCol2') AND dateadded <= datevalue('MMCol3') " &
qry = qry & " ORDER BY MMCol4 DESC " &

Else

qry = qry & " SELECT * " &
qry = qry & " FROM Cars  INNER JOIN Car_models  ON cars.model = car_models.id " &
qry = qry & " WHERE type = 'MMCol5' AND code='MMCol6' AND dateadded >= datevalue('MMCol2') AND dateadded <=  datevalue('MMCol3') " &
qry = qry & " ORDER BY MMCol4 DESC " &

End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.