Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Using ASP to process drop-down box data into SQL statement

Reply
Thread Tools

Using ASP to process drop-down box data into SQL statement

 
 
=?Utf-8?B?S2l3aU5FVCB1ay5nZW9jaXRpZXMuY29tL2hhcm9vbm5ldDIwMDIv?=
Guest
Posts: n/a
 
      09-28-2005
Hello folks, I don't know what they've done to the ordinary ASP newsgroup, I
hope you can answer this question. I am trying to make this piece of code:

If Len(Trim(primary))=0 Then
SQL(1)="Spice = '" & spice & "' And Type = '" & dish_content & "'"
ElseIf Len(Trim(spice))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "' And Type = '" & dish_content
& "'"
ElseIf Len(Trim(dish_content))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "' And Spice = '" & spice & "'"
ElseIf (Len(Trim(primary)) And Len(Trim(spice)))=0 Then
SQL(1)="Type = '" & dish_content & "'"
ElseIf (Len(Trim(spice)) And Len(Trim(dish_content)))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "'"
ElseIf (Len(Trim(primary)) And Len(Trim(dish_content)))=0 Then
SQL(1)="Spice = '" & spice & "'"
Else SQL(1)="Primary_Dish_Type = '" & primary & "' And Spice = '" & spice &
"' And Type = '" & dish_content & "'"
End If

Return records when I select more than one ANY on the drop-down boxes from
this form:

<form method="post" action="OnLineShop.asp">
<table>
<td>Primary Dish Type?
<select name="primary">
<option value="" selected>ANY</option>
<option value="Starter">Starter</option>
<option value="Main Course">Main Course</option>
<option value="Accompaniments">Accompaniments</option>
</select></td>
<td>Spice?
<select name="spice">
<option value="" selected>ANY</option>
<option value="None">None</option>
<option value="Mild">Mild</option>
<option value="Medium">Medium</option>
<option value="Medium+">Medium+</option>
<option value="Medium or Hot">Medium or Hot</option>
<option value="Hot">Hot</option>
<option value="Very Hot">Very Hot</option>
</select>
<!-- <input name="txtData" type="text" id="txtData"> -->
</td>
<td>Dish Content?
<select name="dish_content">
<option value="" selected>ANY</option>
<option value="Vegetarian">Vegetarian</option>
<option value="Vegetable">Containing Vegetables</option>
<option value="Mushroom">Mushroom</option>
<option value="Prawn">Prawn</option>
<option value="King Prawn">King Prawn</option>
<option value="Chicken">Chicken</option>
<option value="Lamb">Lamb (general)</option>
<option value="Keema">Keema</option>
<option value="Mixed">Mixed</option>
<option value="Fish">Fish</option>
<option value="Dairy">Dairy</option>
<option value="Chicken Tikka">Chicken Tikka</option>
<option value="Lamb Tikka">Lamb Tikka</option>
<option value="Tandoori King Prawn">Tandoori King Prawn</option>
</select></td>
<td><input type="submit" name="Submit" value="Go"></td>
</p>
</table>
</form>

Have I missed something from that code?
 
Reply With Quote
 
 
 
 
cbDevelopment
Guest
Posts: n/a
 
      11-13-2005
Your IF statement is only processing one potential ANY. A better way to
process this situation (with much less code and future flexibility) is to
build your WHERE clause as you go:

if primary<>"" then sql=sql & " primary_dish_type='" & primary & "' AND"
if spice<>"" then sql=sql & " spice='" & spice & "' AND"
if dish_content<>"" then sql=sql & " type='" & dish_content & "' AND"
if right(sql,3)="AND" then sql=left(sql,len(sql)-3)

The last IF will trim off the trailing AND. I left the AND in the third
IF to make it easier if you added more conditions in your filter later.
You could just as easily remove it.

So the basic logic is that if the user picks one of more ANY values, they
are not included in the WHERE clause, otherwise the values are included.

Hope this helps.

=?Utf-8?B?S2l3aU5FVCB1ay5nZW9jaXRpZXMuY29tL2hhcm9vbm5ldD IwMDIv?=
<KiwiNET uk.geocities.com/haroonnet2002/@discussions.microsoft.com>
wrote in news:(E-Mail Removed):

> Hello folks, I don't know what they've done to the ordinary ASP
> newsgroup, I hope you can answer this question. I am trying to make
> this piece of code:
>
> If Len(Trim(primary))=0 Then
> SQL(1)="Spice = '" & spice & "' And Type = '" & dish_content
> & "'"
> ElseIf Len(Trim(spice))=0 Then
> SQL(1)="Primary_Dish_Type = '" & primary & "' And Type = '"
> & dish_content
> & "'"
> ElseIf Len(Trim(dish_content))=0 Then
> SQL(1)="Primary_Dish_Type = '" & primary & "' And Spice = '"
> & spice & "'"
> ElseIf (Len(Trim(primary)) And Len(Trim(spice)))=0 Then
> SQL(1)="Type = '" & dish_content & "'"
> ElseIf (Len(Trim(spice)) And Len(Trim(dish_content)))=0 Then
> SQL(1)="Primary_Dish_Type = '" & primary & "'"
> ElseIf (Len(Trim(primary)) And Len(Trim(dish_content)))=0 Then
> SQL(1)="Spice = '" & spice & "'"
> Else SQL(1)="Primary_Dish_Type = '" & primary & "' And Spice = '"
> & spice &
> "' And Type = '" & dish_content & "'"
> End If
>
> Return records when I select more than one ANY on the drop-down boxes
> from this form:
>
> <form method="post" action="OnLineShop.asp">
> <table>
> <td>Primary Dish Type?
> <select name="primary">
> <option value="" selected>ANY</option>
> <option value="Starter">Starter</option>
> <option value="Main Course">Main Course</option>
> <option value="Accompaniments">Accompaniments</option>
> </select></td>
> <td>Spice?
> <select name="spice">
> <option value="" selected>ANY</option>
> <option value="None">None</option>
> <option value="Mild">Mild</option>
> <option value="Medium">Medium</option>
> <option value="Medium+">Medium+</option>
> <option value="Medium or Hot">Medium or Hot</option>
> <option value="Hot">Hot</option>
> <option value="Very Hot">Very Hot</option>
> </select>
> <!-- <input name="txtData" type="text" id="txtData"> -->
> </td>
> <td>Dish Content?
> <select name="dish_content">
> <option value="" selected>ANY</option>
> <option value="Vegetarian">Vegetarian</option>
> <option value="Vegetable">Containing Vegetables</option>
> <option value="Mushroom">Mushroom</option>
> <option value="Prawn">Prawn</option>
> <option value="King Prawn">King Prawn</option>
> <option value="Chicken">Chicken</option>
> <option value="Lamb">Lamb (general)</option>
> <option value="Keema">Keema</option>
> <option value="Mixed">Mixed</option>
> <option value="Fish">Fish</option>
> <option value="Dairy">Dairy</option>
> <option value="Chicken Tikka">Chicken Tikka</option>
> <option value="Lamb Tikka">Lamb Tikka</option>
> <option value="Tandoori King Prawn">Tandoori King
> Prawn</option>
> </select></td>
> <td><input type="submit" name="Submit" value="Go"></td>
> </p>
> </table>
> </form>
>
> Have I missed something from that code?
>


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Fetching & Inserting Data into a column of TEXT Data type in SQL server 2000 Using ASP.NET Bhavesh ASP .Net 5 07-18-2007 07:39 AM
Fetching & Inserting Data into a column of TEXT Data type in SQL server 2000 Using ASP.NET Bhavesh ASP .Net 1 07-17-2007 09:05 AM
Inserting & Fetching Data into a column of TEXT Data type in SQL server 2000 Using ASP.NET Bhavesh ASP .Net 0 07-16-2007 11:15 AM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
Tools to extract data from SQL database and convert it into XML & insert XML data into SQL databases Harry Zoroc XML 1 07-12-2004 10:10 PM



Advertisments