Multiple Values in an ASP query selection

[nicks60610]

I am writing a simple query where a user can select a report based on a shift, i.e. 1,2 or 3. Now they want to be able to view all shifts in one report, i.e. someone selects "All" and it shows shifts 1, 2 and 3 for that particular date.

I have a drop down where the values are set for 1, 2 and 3 and then on my ASP page this value is carried over and queried against the DB (see below):

HTML Page:

<td width=&quot;89%&quot;><select size=&quot;1&quot; name=&quot;Shift&quot;>
<option>Please Select Shift</option>
<option value=&quot;1&quot;>1</option>
<option value=&quot;2&quot;>2</option>
<option value=&quot;3&quot;>3</option>


EQ_EVENTS.PRODUCTION_LOST = '&quot; & Request.QueryString(&quot;Shift&quot & &quot;'

This works fine for the individual shifts, but I am not quite sure how to transfer a value for &quot;All&quot; over to the ASP page so that it queries and finds anything during that date no matter which shift they worked. Any ideas??

 

[GaryC123]

Add your other option - All - and set the value of it to 1,2,3

Then amend your sql

sql=&quot;select * from tablename where shiftfield IN (&quot; & request(&quot;shift_select_box&quot & )&quot;

http://www.intouchmedialtd.com

 

[Turkbear]

Hi,
Try changing to this:

<td width=&quot;89%&quot;><select size=&quot;1&quot; name=&quot;Shift&quot;>
        <option>Please Select Shift</option>
        <option value=&quot;All&quot;>All</option>
        <option value=&quot;1&quot;>1</option>
        <option value=&quot;2&quot;>2</option>
        <option value=&quot;3&quot;>3</option>

Test for All before setting the where clause:

If  Request.QueryString("Shift") = 'All' then
EQ_EVENTS.PRODUCTION_LOST <> &quot;&quot;
else
EQ_EVENTS.PRODUCTION_LOST = '&quot; & Request.QueryString(&quot;Shift&quot;) & &quot;'
End if

This will return all records that have a non-null value in EQ_EVENTS.PRODUCTION_LOST

Depending on your database you could do something like this: ( this is for Oracle)

If  Request.QueryString("Shift") = 'All' then
EQ_EVENTS.PRODUCTION_LOST like '%'
else
EQ_EVENTS.PRODUCTION_LOST = '" & Request.QueryString("Shift") & "'
End if

For SqlServer, you can use '*' instead of the '%' , I think..

 

[nicks60610]

Turkbear, I tried your suggestion, but all I get is EOF or BOF, so it does not find all rows where PRODUCTION_LOST is not null, I am sure it is a syntax issue on the Then statement that I need to manipulate, but thanks for the tip - I think it will work great!

 

[Funka]

you could make the dropbox a multiple-selection listbox, such as:

<select size=&quot;3&quot; name=&quot;Shift&quot; multiple=&quot;multiple&quot;>
<option value=&quot;1&quot;>1</option>
<option value=&quot;2&quot;>2</option>
<option value=&quot;3&quot;>3</option>
</select>
<p>(Hold down the CTRL key to select more than one shift)</p>

and then use GaryC123's exact suggestion to alter your SQL WHERE clause to use an IN statement instead of comparing equivalents.

This is perfectly valid SQL:
SELECT * FROM tablename WHERE shiftfield IN (1)
which is just as valid as
SELECT * FROM tablename WHERE shiftfield IN (2,3)
and
SELECT * FROM tablename WHERE shiftfield IN (1,2,3)
etc...

this gives you even more flexibility than you originally asked for.

good luck!
-f!

 

[GaryC123]

Well just setting the value of All to 1,2,3 will suffice

<select size=&quot;3&quot; name=&quot;Shift&quot;>
<option value=&quot;1&quot;>1</option>
<option value=&quot;2&quot;>2</option>
<option value=&quot;3&quot;>3</option>
<option value=&quot;1,2,3&quot;>All</option>
</select>

http://www.intouchmedialtd.com

 

[ca8msm]

Or.....if you have the value as 'All' you can simply test for it's value and amend the sql statement accordingly.

i.e.

<select size=&quot;3&quot; name=&quot;Shift&quot;>
  <option value=&quot;All&quot;>All</option>
  <option value=&quot;1&quot;>1</option>
  <option value=&quot;2&quot;>2</option>
  <option value=&quot;3&quot;>3</option>
</select>

and....

If Request.QueryString("Shift")="All" Then
 strSQL = "SELECT * FROM TABLE"
Else
 strSQL = "SELECT * FROM TABLE WHERE EQ_EVENTS.PRODUCTION_LOST = '" & Request.QueryString("Shift") & "'"
End If

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[nicks60610]

I cannot seem to get it to show all shifts using either of the methods mentioned last; I tried using &quot;1&quot; OR &quot;2&quot; OR &quot;3&quot; and that did not work either. It is almost as if I need a &quot;BETWEEN&quot; statement for it to work, i.e. BETWEEN 1 and 3

 

[nicks60610]

So has anyone successfully written a query statement in Oracle/ASP asking for this:

If Request.QueryString(&quot;Shift&quot = &quot;All&quot; then
EQ_EVENTS = &quot;Between 1 and 3&quot; (for demonstration)
else
EQ_EVENTS = Request.QueryString(&quot;Shift&quot
End if

So I am trying to find a way to have it choose anything that falls between the value 1 and the value 3, but if I do &quot;1,2,3&quot; it is looking for all 3 values in that field, or if I do &quot;1&quot; or &quot;2&quot; or &quot;3&quot; it will only find the last value, &quot;3&quot;.

I greatly appreciate any help on this since nothing seems to want to work....

 

[nicks60610]

I tried this, but it seems to &quot;run on&quot; and run my second SQL statement rather than stop after the first one....any suggestions as to what I am missing?

sql2 = &quot;SELECT SY_WO_STATUS.DESCRIPTION, EQ_EVENTS.START_TIME, EQ_EVENTS.WO_BASE, EM_EMPLOYEE.EMPLOYEE_ID, WO_WORK_ORDER.ENTERED_BY, WO_WORK_ORDER.DEPARTMENT, WO_WORK_ORDER.REQUESTOR, WO_WORK_ORDER.STATUS, WO_WORK_ORDER.ENTERED_DATE, WO_WORK_ORDER.WO_BASE, WO_WORK_ORDER.EQUIPMENT,WO_WORK_ORDER.DESCRIPTION, WO_COMMENTS.LONG_DESCRIPTION

FROM WO_WORK_ORDER, WO_COMMENTS, EM_EMPLOYEE, EQ_EVENTS, SY_WO_STATUS

WHERE WO_WORK_ORDER.STATUS = SY_WO_STATUS.CODE AND WO_WORK_ORDER.PLANT = SY_WO_STATUS.PLANT AND EQ_EVENTS.WO_BASE = WO_WORK_ORDER.WO_BASE AND EM_EMPLOYEE.EMPLOYEE_ID = WO_WORK_ORDER.ENTERED_BY AND EM_EMPLOYEE.PLANT = WO_WORK_ORDER.PLANT AND WO_WORK_ORDER.PLANT = WO_COMMENTS.PLANT AND WO_WORK_ORDER.WO_BASE = WO_COMMENTS.WO_BASE AND WO_WORK_ORDER.WO_BASE NOT LIKE 'MP%' AND WO_WORK_ORDER.ENTERED_DATE = TO_DATE('&quot; & Request.QueryString(&quot;Day&quot & &quot;','MM/DD/YY') AND WO_WORK_ORDER.DEPARTMENT = '&quot; & Request.QueryString(&quot;Department&quot & &quot;' ORDER BY EQ_EVENTS.START_TIME&quot;

If Request.QueryString(&quot;Shift&quot = &quot;All&quot; Then
Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open &quot;Provider=MSDAORA.1;Password=xxxxxx;User ID=xxxxx;Data Source=xxxxxxxx;Persist Security Info=True;&quot;
Set Rs = Server.CreateObject(&quot;ADODB.Recordset&quot
Rs.Open sql2, conn, 3, 3
objConn.Close
Else
SHIFT_VAR = Request.QueryString(&quot;Shift&quot
End If

Basically it is running a different SQL statement that does not discriminate the search based on Shift, so when I run this statement &quot;Alone&quot; using static data for date and department, it shows me all 3 shifts, but when I try this, it does not find anything, and I think it is because it continues to run the code after this and eventually runs the second SQL statement, finds that SHIFT = &quot;All&quot;, does not understand and fails.

 

[nicks60610]

I was finally able to resolve this, I set the &quot;All&quot; parameter in my second page to a wild card and it accepted it without any problems. Thanks for all your help,