Writing a query ...

Discussion in 'ASP General' started by bcap, Jul 25, 2009.

  1. bcap

    bcap Guest

    Hi,

    I am looking to write a piece of code better.

    Lets say I have a table called CustInfo with CustID, FirstName,
    LastName, and Subscription Level for simplicity.

    How can I create a query that I would be to:

    1) Get all records no matter what subscription level they are in
    2) Choose only records that have a selected status types
    3) Declare that there are no records

    Currently, I am using two queries (one that just selects all, another
    that needs a parameter) and making a decision based on if there is a
    status type. I'd like to learn a more efficent way to do this. Any
    advise would be appreciated!
     
    bcap, Jul 25, 2009
    #1
    1. Advertising

  2. bcap

    Evertjan. Guest

    bcap wrote on 25 jul 2009 in microsoft.public.inetserver.asp.general:

    > I am looking to write a piece of code better.


    Better than what?

    Show us your code, only the essence please.

    > Lets say I have a table called CustInfo with CustID, FirstName,
    > LastName, and Subscription Level for simplicity.


    No, let us hear what engine you are using.

    > How can I create a query that I would be to:
    >
    > 1) Get all records no matter what subscription level they are in
    > 2) Choose only records that have a selected status types
    > 3) Declare that there are no records


    Is this a school assignment?
    Did you read some of the many SQL tutorials?

    > Currently, I am using two queries (one that just selects all, another
    > that needs a parameter) and making a decision based on if there is a
    > status type.


    What is a "status type"?

    > I'd like to learn a more efficent way to do this. Any
    > advise would be appreciated!


    Learning is not letting someone else do the task!

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jul 25, 2009
    #2
    1. Advertising

  3. bcap

    bcap Guest

    Thank you for your reply Evertjan!

    I am using SQL Server with ASP Classic. This is not a school project
    (though I want to take some classes), I am a IT Project Manager trying
    to learn code and modify existing projects. Here is what the query
    currently looks like, it gets the job done. I want to learn and
    understand if this process can be done better.


    if request.querystring("StatusDisplay")="" then

    strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"

    Else

    strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
    SubLevel=" & request.querystring("sublevel")

    End If
     
    bcap, Jul 25, 2009
    #3
  4. bcap

    Bob Barrows Guest

    bcap wrote:
    > Thank you for your reply Evertjan!
    >
    > I am using SQL Server with ASP Classic. This is not a school project
    > (though I want to take some classes), I am a IT Project Manager trying
    > to learn code and modify existing projects. Here is what the query
    > currently looks like, it gets the job done. I want to learn and
    > understand if this process can be done better.
    >
    >
    > if request.querystring("StatusDisplay")="" then
    >
    > strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"
    >
    > Else
    >
    > strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
    > SubLevel=" & request.querystring("sublevel")
    >
    > End If


    Aside from using dynamic sql, there is nothing wrong with this. Other ways
    of doing it will decrease the amount of code, but will lead to less
    efficient queries. There are some good articles here:
    http://www.sommarskog.se/ - read the one about dynamic search conditions
    (and then read the one about dynamic sql)

    Here is my canned reply about dynamic sql:

    Further points to consider:
    Your use of dynamic sql is leaving you vulnerable to hackers using sql
    injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See these links for a better, more secure way to execute your queries by
    using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Using Command object to parameterize CommandText:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
    Select statement:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd


    Personally, I prefer using stored procedures,
    http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&



    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jul 25, 2009
    #4
  5. bcap

    Evertjan. Guest

    Bob Barrows wrote on 25 jul 2009 in
    microsoft.public.inetserver.asp.general:

    > bcap wrote:
    >> Thank you for your reply Evertjan!


    A bit strange, anwering me by name and not supplying one yourself,
    "bcap" not sounding like a usable [nick]name.

    >> I am using SQL Server with ASP Classic. This is not a school project
    >> (though I want to take some classes), I am a IT Project Manager
    >> trying to learn code and modify existing projects. Here is what the
    >> query currently looks like, it gets the job done. I want to learn
    >> and understand if this process can be done better.
    >>
    >>
    >> if request.querystring("StatusDisplay")="" then
    >>
    >> strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"
    >>
    >> Else
    >>
    >> strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
    >> SubLevel=" & request.querystring("sublevel")
    >>
    >> End If

    >
    > Aside from using dynamic sql, there is nothing wrong with this. Other
    > ways of doing it will decrease the amount of code, but will lead to
    > less efficient queries. There are some good articles here:
    > http://www.sommarskog.se/ - read the one about dynamic search
    > conditions (and then read the one about dynamic sql)


    > Further points to consider:
    > Your use of dynamic sql is leaving you vulnerable to hackers using sql
    > injection:


    I am not that afraid of sql-injection as long as one is constantly on
    guard.

    If your sublevel is an integer, this:

    strSql = "SELECT CustID, FName, LName, SubLevel FROM CustInfo "_
    "WHERE SubLevel = " & CInt(request.querystring("sublevel"))

    will prevent sql-injection adequately.

    When you have a series of levels, you could try:

    "WHERE SubLevel > " & CInt(request.querystring("sublevel"))


    I agree with Bob that the danger is imminent when you become sloppy.

    ===================

    Always first visualize your SQL result:

    <%
    strSql = "SELECT ....."
    response.write "<p> & strSql & "<p>":response.end
    ' set mD = conn.Execute(strSql)
    %>

    and only execute it when you are completely satisfied:

    <%
    strSql = "SELECT ....."
    ' response.write "<p> & strSql & "<p>":response.end
    set mD = conn.Execute(strSql)
    %>

    and even then keep a copy of your database
    for back-uping to a previous status quo.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jul 25, 2009
    #5
  6. bcap

    bcap Guest

    Thank you all for your time and thoughts, very much appreciated!
     
    bcap, Jul 25, 2009
    #6
  7. bcap

    Evertjan. Guest

    bcap wrote on 25 jul 2009 in microsoft.public.inetserver.asp.general:

    > Thank you all for your time and thoughts, very much appreciated!


    Please always quote on usenet.

    This is not an idle request,
    as it is pefectly unclear what you are replying on and to whom.

    Not all news servers fill their items ina perfectr and timely fassion, and
    some are deleting them more quickly than others.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jul 25, 2009
    #7
  8. bcap

    Dooza Guest

    bcap wrote:
    > Thank you for your reply Evertjan!
    >
    > I am using SQL Server with ASP Classic. This is not a school project
    > (though I want to take some classes), I am a IT Project Manager trying
    > to learn code and modify existing projects. Here is what the query
    > currently looks like, it gets the job done. I want to learn and
    > understand if this process can be done better.
    >
    >
    > if request.querystring("StatusDisplay")="" then
    >
    > strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo"
    >
    > Else
    >
    > strSql = " SELECT CustID, FName, LName, SubLevel FROM CustInfo WHERE
    > SubLevel=" & request.querystring("sublevel")
    >
    > End If


    SELECT CustID, FName, LName, SubLevel
    FROM CustInfo
    WHERE (@SubLevel IS NULL OR SubLevel = @SubLevel)

    @SubLevel is your input variable, if it is NULL, then it lists
    everything, if it isn't NULL, it tries matches it.

    I don't know how you would put this in Dynamic SQL, as I would use a
    Stored Procedure to do this normally.

    Dooza
     
    Dooza, Jul 27, 2009
    #8
    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. HNguyen
    Replies:
    4
    Views:
    2,458
    HNguyen
    Dec 21, 2004
  2. Sriv Chakravarthy
    Replies:
    0
    Views:
    837
    Sriv Chakravarthy
    Sep 18, 2003
  3. Bighead

    Writing a CGI to query DB

    Bighead, Nov 10, 2007, in forum: Python
    Replies:
    4
    Views:
    467
    Bighead
    Nov 11, 2007
  4. George Stout
    Replies:
    4
    Views:
    225
    George Stout
    Jan 21, 2004
  5. Anukul Singhal

    Writing a SQL Query in Active Record?

    Anukul Singhal, May 8, 2008, in forum: Ruby
    Replies:
    5
    Views:
    202
    Phillip Gawlowski
    May 8, 2008
Loading...

Share This Page