Create an SQL string from selected form items

Discussion in 'ASP General' started by davidharveyg@yahoo.co.uk, Dec 9, 2008.

  1. Guest

    Hi,

    I have an asp form with several search fields available.
    Each search field has a check box against it, i.e. the user can decide
    which fields should be included in the search.
    Some fields are text boxes, others are drop downs.

    I want to know how to create a simple SQL search string based on the
    fields the user selects (or rather based on the check boxes the users
    selects and the data in those fields to search on.

    I have the following in my ASP code

    customer = request.form("CustomerName") ...user can select from a list
    date_s = request.form("start") .... user enters a date mm/dd/yyyy
    date_e = request.form("end") .... user enters a date mm/dd/yyyy
    product = request.form("prod") .... user enters a code or part of a
    code
    serial = request.form("serial") .... user enters a number or part of a
    number
    fr = request.form("Fault-R") .... user enters a string to find
    matching
    ff = request.form("Fault-F") .... user selects from a drop down
    fn = request.form("Fault-N").... user enters a string to find matching
    ca = request.form("Cause-F").... user selects from a drop down
    cn = request.form("Cause-N").... user enters a string to find matching


    Obviously the user could select any combination of fields, so what is
    the best way of handling this ?

    Appreciate your help / advice

    Thanks

    David
     
    , Dec 9, 2008
    #1
    1. Advertising

  2. Bullschmidt Guest

    Perhaps this may hopefully give you some ideas:

    Classic ASP Design Tips - Search Criteria on Multiple Fields
    http://www.bullschmidt.com/devtip-searchcriteria.asp

    Example on a page receiving a post of a SQL statement based on two
    listboxes (Rep and Customer) that might each have a value of "" (i.e.
    blank) to show all with no criteria restrictions.

    ' Get posted form vars.
    Rep = Request.Form("Rep")
    Customer = Request.Form("Customer")

    ' Set strSQL.
    strSQL = "SELECT * FROM MyTable WHERE (1=1)"
    If Rep <> "" Then
    strSQL = strSQL & " AND (Rep='" & Rep & "')"
    End If
    If Customer <> "" Then
    strSQL = strSQL & " AND (Customer='" & Customer & "')"
    End If

    Response.Write "strSQLWhere: " & strSQLWhere

    And the (1=1) above is there as a placeholder (which doesn't affect the
    results since it is always true) as the SQL statement possibly may use
    AND with criteria after it or possibly the SQL statement may not have
    anything after it. For example a SQL statement without the (1=1) like
    this would not work: SELECT * FROM MyTable WHERE AND Customer='API'

    Best regards,
    J. Paul Schmidt, Freelance Web and Database Developer
    http://www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Bullschmidt, Dec 11, 2008
    #2
    1. Advertising

  3. "Bullschmidt" <> wrote in message
    news:...
    > Perhaps this may hopefully give you some ideas:
    >
    > Classic ASP Design Tips - Search Criteria on Multiple Fields
    > http://www.bullschmidt.com/devtip-searchcriteria.asp
    >
    > Example on a page receiving a post of a SQL statement based on two
    > listboxes (Rep and Customer) that might each have a value of "" (i.e.
    > blank) to show all with no criteria restrictions.
    >
    > ' Get posted form vars.
    > Rep = Request.Form("Rep")
    > Customer = Request.Form("Customer")
    >
    > ' Set strSQL.
    > strSQL = "SELECT * FROM MyTable WHERE (1=1)"
    > If Rep <> "" Then
    > strSQL = strSQL & " AND (Rep='" & Rep & "')"
    > End If
    > If Customer <> "" Then
    > strSQL = strSQL & " AND (Customer='" & Customer & "')"
    > End If
    >
    > Response.Write "strSQLWhere: " & strSQLWhere
    >
    > And the (1=1) above is there as a placeholder (which doesn't affect the
    > results since it is always true) as the SQL statement possibly may use
    > AND with criteria after it or possibly the SQL statement may not have
    > anything after it. For example a SQL statement without the (1=1) like
    > this would not work: SELECT * FROM MyTable WHERE AND Customer='API'
    >


    How are the from/to date types handle here?

    What happens when the user enters this criteria for Rep:-

    Fred'); Drop myTable; --

    ??

    You should read up on SQL injection attacks.

    The reason why this question hasn't been answered yet is doing this properly
    takes much more effort.

    > Best regards,
    > J. Paul Schmidt, Freelance Web and Database Developer
    > http://www.Bullschmidt.com


    Having read up on SQL Injection attacks I suggest you review your previous
    freelance work and provide any fixes necessary to your customers.

    --
    Anthony Jones - MVP ASP/ASP.NET
     
    Anthony Jones, Dec 11, 2008
    #3
  4. Guest

    On 11 Dec, 04:12, Bullschmidt <> wrote:
    > Perhaps this may hopefully give you some ideas:
    >
    > Classic ASP Design Tips - Search Criteria on Multiple Fieldshttp://www.bullschmidt.com/devtip-searchcriteria.asp
    >
    > Example on a page receiving a post of a SQL statement based on two
    > listboxes (Rep and Customer) that might each have a value of "" (i.e.
    > blank) to show all with no criteria restrictions.
    >
    > ' Get posted form vars.
    > Rep = Request.Form("Rep")
    > Customer = Request.Form("Customer")
    >
    > ' Set strSQL.
    > strSQL = "SELECT * FROM MyTable WHERE (1=1)"
    > If Rep <> "" Then
    >  strSQL = strSQL & " AND (Rep='" & Rep & "')"
    > End If
    > If Customer <> "" Then
    >  strSQL = strSQL & " AND (Customer='" & Customer  & "')"
    > End If
    >
    > Response.Write "strSQLWhere: " & strSQLWhere
    >
    > And the (1=1) above is there as a placeholder (which doesn't affect the
    > results since it is always true) as the SQL statement possibly may use
    > AND with criteria after it or possibly the SQL statement may not have
    > anything after it.  For example a SQL statement without the (1=1) like
    > this would not work: SELECT * FROM MyTable WHERE AND Customer='API'
    >
    > Best regards,
    > J. Paul Schmidt, Freelance Web and Database Developerhttp://www.Bullschmidt.com
    > Access Database Sample, Web Database Sample, ASP Design Tips
    >
    > *** Sent via Developersdexhttp://www.developersdex.com***


    _____________________________

    Many thanks J. Paul Schmidt,

    This method worked perfectly, although I had found an example of it a
    couple of days ago.
    Appreciate you taking the time to reply to my post :)

    Regards

    David Gordon.
     
    , Dec 12, 2008
    #4
    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. Celine
    Replies:
    3
    Views:
    395
    Rick Spiewak
    May 10, 2004
  2. Replies:
    0
    Views:
    559
  3. Bart Van Hemelen
    Replies:
    2
    Views:
    4,685
    Bart Van Hemelen
    Sep 4, 2006
  4. Iain
    Replies:
    3
    Views:
    944
  5. Keiron Waites
    Replies:
    1
    Views:
    123
    Keiron Waites
    Oct 21, 2003
Loading...

Share This Page