Display all when first selection is made on drop down menu

I am using SQL, ASP classic and VBscript on my results page.

On the search page I have a drop down menu listing a list of clients, it displays the name of the client and passes on the ID of the client to page # 2

The query on page # 2 filters out using the ID.

I am using Dreamweaver to create my recordsets.

The sql reads:  Select * from table where  b.userid = MMColParam11

The first item of the list of clients says "All clients", which is manually entered, the rest come from a recordset and all have names and ID's.

When I filter text fields I can use the '%' to get all records for "All" or when no selection is made, in this case they are integer numbers, I get an error if I use % ... is there a way alternative to pass a value that will return ALL records like when comparing a text ?
LVL 1
AleksAsked:
Who is Participating?
 
Rainer JeschorCommented:
Hi,

I think you have either to change the query in a way to use something like
Select * from table where  b.userid > MMColParam11

Open in new window

passing the value 0 to MMColParam11
OR
you change the query like
Select * from table where  b.userid = MMColParam11 OR 1=1

Open in new window

adding a condition which is always true.

HTH
Rainer
0
 
AleksAuthor Commented:
I pass 0 then great, all come up, but if I pass a value like 120, then instead of just getting 120, I get 120 and any other ID above 120 correct ?... that won't work.

It was to be the ID that is selected and if none then ALL.

I can pass '0' and if that is the case I would need to return all ... can this be done as a condition ?

if MMColParam11 = 0 then (no requirement to match b.userid),
if MMColParam11 <>) then 'WHERE b.userid = MMColParam11

Just an idea but I am not sure if that can be done in a query.
0
 
Rainer JeschorCommented:
Hi,
I meant that in your code where you create your query, you use something like
yourSelectQuery = "Select * from table where  b.userid"
If MMColParam11 = 0 Then
  yourSelectQuery = yourSelectQuery  & ">0"
Else
  yourSelectQuery = yourSelectQuery  & "=" & MMColParam11
End If

Open in new window


This will return everything if the value is 0 OR it returns the provided userid

HTH
Rainer
0
 
AleksAuthor Commented:
1=1 won't work, if I make a selection and an ID is passed it displays ALL results. This should ONLY happen if MMColParam11 is '0', which is the default value when nothing is passed
0
 
AleksAuthor Commented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.