Professional Web Applications Themes

how to build SQL query with multiple, optional variablesfrom query string - Dreamweaver AppDev

hello. I'm pretty new to the world of dynamic web design and need some direction/advice. I'm building a catalogue using .asp VB, a SQL Server database and am assembling the pages in Dreamweaver MX2004. I am passing variables in the query string and I am able to build a statement that uses multiple variables from the query string to determine which results to return in my main recordset. eg: wine.asp&country=italy&producer=15&vintage=2003 This would be based on a statement along the lines of: SELECT * FROM TABLE WHERE country=var 1 AND producer=var2 AND vintage=var3 where these variables are extracted from the corresponding ...

  1. #1

    Default how to build SQL query with multiple, optional variablesfrom query string

    hello.

    I'm pretty new to the world of dynamic web design and need some
    direction/advice.

    I'm building a catalogue using .asp VB, a SQL Server database and am
    assembling the pages in Dreamweaver MX2004.

    I am passing variables in the query string and I am able to build a statement
    that uses multiple variables from the query string to determine which results
    to return in my main recordset.

    eg: wine.asp&country=italy&producer=15&vintage=2003

    This would be based on a statement along the lines of:

    SELECT * FROM TABLE WHERE
    country=var 1 AND producer=var2 AND vintage=var3 where these variables are
    extracted from the corresponding elements in the query string.

    What I want to know is if it is possible to create a single SQL SELECT
    statement that will allow the flexibility to just specify one or two of these
    variables and ignore those that aren't specified.

    ie, if someone just wants to search for wine of a 2003 vintage, regardless of
    country or producer.

    I have to specify defualt vales when defining the variables - is it possible
    to define a default value that means the variable will be ignored if no
    specific value is specified?

    Basically, I want to be able to have a SELECT statement that can accomodate
    multiple variables, but which will still work if only one is specified.

    Is this possible? If anyone can give me any pointers about what I need to do
    to achieve this I would be really grateful.

    Many Thanks
    tedstar:confused;

    tedstar Guest

  2. #2

    Default Re: how to build SQL query with multiple, optionalvariables from query string

    by the way, an OR statement doesn't give me what I need.

    If someone is viewing australian wines I want them to be able to filter these
    to just those from a certain year or producer, so I guess I'm asking how do I
    go about filtering results using SQL?

    cheers


    tedstar Guest

  3. #3

    Default Re: how to build SQL query with multiple, optional variables from query string

    If the default value you define is the name of the field, then it will have
    the effect of ignoring the comparison IF you have no NULL values in that
    column. A null value compared to anything, even another null, is unknown,
    and an unknown doesn't pass with AND.

    "tedstar" <com> wrote in message
    news:dd82h1$5ea$macromedia.com... 


    Lionstone Guest

  4. #4

    Default Re: how to build SQL query with multiple, optional variables from query string

    You can create out your own string for the Query and use it instead of the
    one DW creates.

    Basically after you have the page appearing like you want locate the line
    that contains the Select statement and change it so that something like

    rsUser.Source = "SELECT * FROM tblMembers WHERE UserID = " +
    Replace(rsUser__MMColParam, "'", "''") + ""

    becomes

    rsUser.Source = strSQL

    Then above the recordset code you start to define this strSQL. You would
    start with something like

    dim strSQL
    strSQL = "SELECT * FROM TABLE WHERE 1=1"
    if request.querystring("country") <> "" then
    strSQL = strSQL & " and country = '" & request.querystring("country") &
    "'"
    End if
    if request.querystring("producer") <> "" then
    strSQL = strSQL & " and producer = " & request.querystring("producer")
    End if
    if request.querystring("vintage") <> "" then
    strSQL = strSQL & " and vintage = " & request.querystring("vintage")
    End if

    You can then delete the variable definitions created by DW.

    --
    Paul Whitham
    Certified Dreamweaver MX2004 Professional
    Team Macromedia Volunteer for Dreamweaver

    Valleybiz Internet Design
    www.valleybiz.net

    "tedstar" <com> wrote in message
    news:dd84ep$8i8$macromedia.com... 


    Paul Guest

  5. #5

    Default Re: how to build SQL query with multiple, optionalvariables from query string

    thanks a lot for these suggestions.

    I actually stumbled upon a solution of sorts - I altered the SELECT statement
    to be along the lines of;

    SELECT * FROM TABLE
    WHERE Country LIKE 'var1' and Vintage LIKE 'var2'

    where the default values for var1 and var2 are '%'

    there's no doubt at least one good reason why this is not he best route to go
    down but it does appear to give me what I need and enables me to filter my
    results as required.

    thanks a lot for your time and help

    edText

    tedstar Guest

  6. #6

    Default Re: how to build SQL query with multiple, optional variables from query string

    That's fine if your data is text. If you're using numbers, then you should
    probably go with the field names as I suggested.

    "tedstar" <com> wrote in message
    news:ddaao8$j27$macromedia.com... 


    Lionstone Guest

Similar Threads

  1. Replies: 1
    Last Post: February 27th, 12:04 AM
  2. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  3. How to build query string?
    By Artco News in forum PHP Development
    Replies: 3
    Last Post: November 4th, 12:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139