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

Discussion in 'ASP .Net' started by =?Utf-8?B?S2l3aU5FVCB1ay5nZW9jaXRpZXMuY29tL2hhcm9v, Sep 28, 2005.

  1. 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?
     
    =?Utf-8?B?S2l3aU5FVCB1ay5nZW9jaXRpZXMuY29tL2hhcm9v, Sep 28, 2005
    #1
    1. Advertising

  2. 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?S2l3aU5FVCB1ay5nZW9jaXRpZXMuY29tL2hhcm9vbm5ldDIwMDIv?=
    <KiwiNET uk.geocities.com/haroonnet2002/@discussions.microsoft.com>
    wrote in news::

    > 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?
    >
     
    cbDevelopment, Nov 13, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. SirPoonga
    Replies:
    2
    Views:
    779
    Ben Strackany
    Jan 7, 2005
  2. weiwei
    Replies:
    0
    Views:
    1,031
    weiwei
    Jan 5, 2007
  3. msimmons
    Replies:
    0
    Views:
    496
    msimmons
    Jul 16, 2009
  4. Replies:
    5
    Views:
    281
  5. Replies:
    3
    Views:
    295
Loading...

Share This Page