query for "all"

[Rich Palarea]

I have a form that queries a database by "day".

The form values are the days of the week, but I want to add a value for "no
preference" where the results would be all values. I tried to string
together all of the days (i.e.: "Monday" AND "Tuesday" AND...) in the form
value, but the query can't evaluate it. It just returns results for the
first value.

Any idea how to do this...must be simple, right?

Thanks,
Rich

[Bob Barrows]

Rich Palarea wrote:

> I have a form that queries a database by "day".
>
> The form values are the days of the week, but I want to add a value
> for "no preference" where the results would be all values. I tried
> to string together all of the days (i.e.: "Monday" AND "Tuesday"
> AND...) in the form value, but the query can't evaluate it. It just
> returns results for the first value.
>
> Any idea how to do this...must be simple, right?
>
> Thanks,
> Rich

Option 1:
Change AND to OR:
Field = "Monday" OR Field = "Tuesday" ...

Option 2 (Better):
Use IN
.... Field IN ("Monday","Tuesday",...)

Option 2 (Best):
Don't add this field to the WHERE clause if the value is "no performance"

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Jeff Cochran]

On Thu, 30 Oct 2003 16:18:31 -0500, "Rich Palarea" <nospam@none.com>
wrote:

>I have a form that queries a database by "day".
>
>The form values are the days of the week, but I want to add a value for "no
>preference" where the results would be all values. I tried to string
>together all of the days (i.e.: "Monday" AND "Tuesday" AND...) in the form
>value, but the query can't evaluate it. It just returns results for the
>first value.
>
>Any idea how to do this...must be simple, right?

Just leave the WHERE clause for day of week off the query when "No
Preference" is selected on the form.

Jeff

[Rich Palarea]

Jeff:

Since I'm submitting values from a web form against a SELECT statement that
is already written, how would I go about modifying the SQL on the fly?

Thanks,
Rich


"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:3fa2633a.220473974@msnews.microsoft.com...

> On Thu, 30 Oct 2003 16:18:31 -0500, "Rich Palarea" <nospam@none.com>
> wrote:
>

> >I have a form that queries a database by "day".
> >
> >The form values are the days of the week, but I want to add a value for

"no

> >preference" where the results would be all values. I tried to string
> >together all of the days (i.e.: "Monday" AND "Tuesday" AND...) in the

form

> >value, but the query can't evaluate it. It just returns results for the
> >first value.
> >
> >Any idea how to do this...must be simple, right?

>
> Just leave the WHERE clause for day of week off the query when "No
> Preference" is selected on the form.
>
> Jeff

[Bob Barrows]

Rich Palarea wrote:

> Jeff:
>
> Since I'm submitting values from a web form against a SELECT
> statement that is already written, how would I go about modifying the
> SQL on the fly?
>

Wow! That really threw me. You'd better show us this code. It sounded as if
you were creating this SQL statement on the fly. I cannot understand what
you mean by "already written". Are you talking about a stored procedure?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Rich Palarea]

Here is how the form is setup:

<select size="1" name="MeetingDay">
<option>Monday</option>
<option>Tuesday</option>
<option>Wednesday</option>
<option>Thursday</option>
<option>Friday</option>
<option>Saturday</option>
<option>Sunday</option>
<option value=WHAT DO WE PUT HERE?>No Preference</option>
</select>

Here is the SQL that takes the form input to return results:

"SELECT * FROM View_Smallgroups WHERE (MeetingDay = '::MeetingDay::') ORDER
BY MeetingDay ASC,NGLocation ASC"

Thanks,
Rich

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OGuud5%23nDHA.2808@TK2MSFTNGP10.phx.gbl...

> Rich Palarea wrote:

> > Jeff:
> >
> > Since I'm submitting values from a web form against a SELECT
> > statement that is already written, how would I go about modifying the
> > SQL on the fly?
> >

> Wow! That really threw me. You'd better show us this code. It sounded as

if

> you were creating this SQL statement on the fly. I cannot understand what
> you mean by "already written". Are you talking about a stored procedure?
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bob Barrows]

Rich Palarea wrote:

> Here is how the form is setup:
>
> <select size="1" name="MeetingDay">
> <option>Monday</option>
> <option>Tuesday</option>
> <option>Wednesday</option>
> <option>Thursday</option>
> <option>Friday</option>
> <option>Saturday</option>
> <option>Sunday</option>

Huh? none of these options have values. How are you passing them to the next
page?

> <option value=WHAT DO WE PUT HERE?>No
> Preference</option> </select>
>
> Here is the SQL that takes the form input to return results:
>
> "SELECT * FROM View_Smallgroups WHERE (MeetingDay =
> '::MeetingDay::') ORDER BY MeetingDay ASC,NGLocation ASC"
>
> Thanks,
> Rich

So change it to:

strSQL = "SELECT * FROM View_Smallgroups "
If Request.Form("MeetingDay") <> "No Preference" then
strSQL = strSQL & " WHERE MeetingDay = '" & _
Request.Form("MeetingDay") & "'"
end if
strSQL = strSQL & " ORDER BY MeetingDay ASC,NGLocation ASC"
'Let's make sure the statement is correct:
Response.Write strSQL

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Rich Palarea]

Bob - sorry, the options do have values of Monday, Tuesday, etc. (and they
are working). Bad post - my bad!

I'll try your SQL solution and report back. Thanks!

Rich
"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eGL4NI$nDHA.1708@TK2MSFTNGP12.phx.gbl...

> Rich Palarea wrote:

> > Here is how the form is setup:
> >
> > <select size="1" name="MeetingDay">
> > <option>Monday</option>
> > <option>Tuesday</option>
> > <option>Wednesday</option>
> > <option>Thursday</option>
> > <option>Friday</option>
> > <option>Saturday</option>
> > <option>Sunday</option>

>
> Huh? none of these options have values. How are you passing them to the

next

> page?
>

> > <option value=WHAT DO WE PUT HERE?>No
> > Preference</option> </select>
> >
> > Here is the SQL that takes the form input to return results:
> >
> > "SELECT * FROM View_Smallgroups WHERE (MeetingDay =
> > '::MeetingDay::') ORDER BY MeetingDay ASC,NGLocation ASC"
> >
> > Thanks,
> > Rich

>
> So change it to:
>
> strSQL = "SELECT * FROM View_Smallgroups "
> If Request.Form("MeetingDay") <> "No Preference" then
> strSQL = strSQL & " WHERE MeetingDay = '" & _
> Request.Form("MeetingDay") & "'"
> end if
> strSQL = strSQL & " ORDER BY MeetingDay ASC,NGLocation ASC"
> 'Let's make sure the statement is correct:
> Response.Write strSQL
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[MSFT]

Hi Rich,

Where did you compose the query string, client script or serve side ASP
code? I think you may first check the value of combobox "MeetingDay" and
then determine the query string just like what Bob suggested. Is there any
concern with this way?

Luke
Microsoft Online Support

Get Secure! [url]www.microsoft.com/security[/url]
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Rich Palarea]

Luke:

It was done through the FrontPage DRW (2002). Bob's solution worked great!
So, the values in the combo box, for example, are instantiated inside of the
FrontPage code within the wizard.

Thanks,

Rich
"MSFT" <lukezhan@online.microsoft.com> wrote in message
news:J6$iWcqoDHA.2088@cpmsftngxa06.phx.gbl...

> Hi Rich,
>
> Where did you compose the query string, client script or serve side ASP
> code? I think you may first check the value of combobox "MeetingDay" and
> then determine the query string just like what Bob suggested. Is there any
> concern with this way?
>
> Luke
> Microsoft Online Support
>
> Get Secure! [url]www.microsoft.com/security[/url]
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>