Another SQL issue with asp

Discussion in 'ASP General' started by Jeff, Jan 3, 2006.

  1. Jeff

    Jeff Guest

    hey guys. Thanks for all the help in the past. For some reason, ASP came
    fairly easy to me, but putting SQL in it.. well.. I lack.
    Anyway, here is what I have.

    set bam1 = conn.execute ("select distinct(username) as user, iscore,
    imeeting from viva where imeeting = " & var4 & " order by iscore asc")

    Now, this displays the person's name everytime they have played, and not one
    time only(distinct)

    what I want to do, is display the username(user) and the lowest iscore for
    that user, from the viva table where imeeting = var4 "var4 is the current
    meeting"

    can someone help me please?
     
    Jeff, Jan 3, 2006
    #1
    1. Advertising

  2. Jeff wrote:
    > hey guys. Thanks for all the help in the past. For some reason, ASP
    > came fairly easy to me, but putting SQL in it.. well.. I lack.
    > Anyway, here is what I have.
    >
    > set bam1 = conn.execute ("select distinct(username) as user, iscore,
    > imeeting from viva where imeeting = " & var4 & " order by iscore asc")
    >
    > Now, this displays the person's name everytime they have played, and
    > not one time only(distinct)
    >
    > what I want to do, is display the username(user) and the lowest
    > iscore for that user, from the viva table where imeeting = var4 "var4 is
    > the current meeting"
    >
    > can someone help me please?



    You need to read up on the difference between DISTINCT and GROUP BY.

    DISTINCT only guarantees that each record in a resultset will be unique.
    Since there are multiple records for each user, each containing a different
    score, each record is unique so they are all displayed.

    When you say "lowest iscore for that user" the word that should immediately
    pop into your head is "aggregate". When you need to aggregate results, you
    must use a GROUP BY query.

    set bam1 = conn.execute ("select username as user, MIN(iscore) " & _
    "from viva where imeeting = " & var4 & " GROUP BY username " & _
    "order by iscore asc")

    (I removed "imeeting" from the select statement: why retrieve that field
    when you know it will contain the value of var4?)

    I know I've mentioned it to you before, but it bears repeating: dynamic sql
    is not recommended if you want your app to be secure. Instead, you should
    use parameters. You can look up my previous replies for the links.


    --
    Microsoft MVP - ASP/ASP.NET
    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 [MVP], Jan 3, 2006
    #2
    1. Advertising

  3. Jeff

    Jeff Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Jeff wrote:
    >> hey guys. Thanks for all the help in the past. For some reason, ASP
    >> came fairly easy to me, but putting SQL in it.. well.. I lack.
    >> Anyway, here is what I have.
    >>
    >> set bam1 = conn.execute ("select distinct(username) as user, iscore,
    >> imeeting from viva where imeeting = " & var4 & " order by iscore asc")
    >>
    >> Now, this displays the person's name everytime they have played, and
    >> not one time only(distinct)
    >>
    >> what I want to do, is display the username(user) and the lowest
    >> iscore for that user, from the viva table where imeeting = var4 "var4 is
    >> the current meeting"
    >>
    >> can someone help me please?

    >
    >
    > You need to read up on the difference between DISTINCT and GROUP BY.
    >
    > DISTINCT only guarantees that each record in a resultset will be unique.
    > Since there are multiple records for each user, each containing a
    > different score, each record is unique so they are all displayed.
    >
    > When you say "lowest iscore for that user" the word that should
    > immediately pop into your head is "aggregate". When you need to aggregate
    > results, you must use a GROUP BY query.
    >
    > set bam1 = conn.execute ("select username as user, MIN(iscore) " & _
    > "from viva where imeeting = " & var4 & " GROUP BY username " & _
    > "order by iscore asc")
    >
    > (I removed "imeeting" from the select statement: why retrieve that field
    > when you know it will contain the value of var4?)
    >
    > I know I've mentioned it to you before, but it bears repeating: dynamic
    > sql is not recommended if you want your app to be secure. Instead, you
    > should use parameters. You can look up my previous replies for the links.
    >
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > 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"
    >


    Thanks for the fast reply Bob. I do know I have much to learn. I sortof dove
    into this head first.
    Anyway, the query you gave returns an error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that
    does not include the specified expression 'iscore' as part of an aggregate
    function.

    /GIG/viva/standings.asp, line 71

    I redid the statement to see if that would help... but was the same thing as
    a single line.
     
    Jeff, Jan 3, 2006
    #3
  4. Jeff wrote:
    >>
    >> set bam1 = conn.execute ("select username as user, MIN(iscore) " & _
    >> "from viva where imeeting = " & var4 & " GROUP BY username " & _
    >> "order by iscore asc")
    >>

    >
    > Thanks for the fast reply Bob. I do know I have much to learn. I
    > sortof dove into this head first.
    > Anyway, the query you gave returns an error:
    >
    > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'


    ODBC?
    http://www.aspfaq.com/show.asp?id=2126

    > [Microsoft][ODBC Microsoft Access Driver] You tried to execute a
    > query that does not include the specified expression 'iscore' as part
    > of an aggregate function.
    >

    Oops. It's the ORDER BY, whouch should read:

    "order by MIN(iscore) asc")


    --
    Microsoft MVP - ASP/ASP.NET
    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 [MVP], Jan 3, 2006
    #4
  5. Jeff

    Jeff Guest

    Thanks Bob. I am not into reading online, so are there some good txt books
    you can recommend for me that you know of??


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Jeff wrote:
    >>>
    >>> set bam1 = conn.execute ("select username as user, MIN(iscore) " & _
    >>> "from viva where imeeting = " & var4 & " GROUP BY username " & _
    >>> "order by iscore asc")
    >>>

    >>
    >> Thanks for the fast reply Bob. I do know I have much to learn. I
    >> sortof dove into this head first.
    >> Anyway, the query you gave returns an error:
    >>
    >> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    >
    > ODBC?
    > http://www.aspfaq.com/show.asp?id=2126
    >
    >> [Microsoft][ODBC Microsoft Access Driver] You tried to execute a
    >> query that does not include the specified expression 'iscore' as part
    >> of an aggregate function.
    >>

    > Oops. It's the ORDER BY, whouch should read:
    >
    > "order by MIN(iscore) asc")
    >
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > 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"
    >
     
    Jeff, Jan 3, 2006
    #5
  6. Jeff

    Larry Bud Guest

    > set bam1 = conn.execute ("select username as user, MIN(iscore) " & _
    > "from viva where imeeting = " & var4 & " GROUP BY username " & _
    > "order by iscore asc")
    >
    > (I removed "imeeting" from the select statement: why retrieve that field
    > when you know it will contain the value of var4?)
    >
    > I know I've mentioned it to you before, but it bears repeating: dynamic sql
    > is not recommended if you want your app to be secure. Instead, you should
    > use parameters. You can look up my previous replies for the links.


    How could one use parameters in a SQL statement when "var4" could be
    any numeric?

    Seems to be the OP could just check to make sure var4 is an INT, and if
    not, bail out of the page.
     
    Larry Bud, Jan 3, 2006
    #6
  7. Larry Bud wrote:
    >>
    >> I know I've mentioned it to you before, but it bears repeating:
    >> dynamic sql is not recommended if you want your app to be secure.
    >> Instead, you should use parameters. You can look up my previous
    >> replies for the links.

    >
    > How could one use parameters in a SQL statement when "var4" could be
    > any numeric?


    :)
    Easily:
    http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

    or, if one has an aversion to saved parameter queries:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    >
    > Seems to be the OP could just check to make sure var4 is an INT, and
    > if not, bail out of the page.


    :) This should be done whether using dynamic sql or not. Server-side
    validation of user inputs is the first layer of security, almost the most
    important. Never assume a form is being submitted from the page that you
    wrote.

    There are other reasons not to use dynamic sql. While security is the most
    important, ease of coding, performance and resource conservation are others.
    Using saved parameter queries will decrease network traffic and allow Jet to
    use compiled query plans instead of forcing it to compile new plans on each
    execution.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    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 [MVP], Jan 3, 2006
    #7
  8. Jeff

    Jeff Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Jeff wrote:
    >>>
    >>> set bam1 = conn.execute ("select username as user, MIN(iscore) " & _
    >>> "from viva where imeeting = " & var4 & " GROUP BY username " & _
    >>> "order by iscore asc")
    >>>

    >>
    >> Thanks for the fast reply Bob. I do know I have much to learn. I
    >> sortof dove into this head first.
    >> Anyway, the query you gave returns an error:
    >>
    >> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    >
    > ODBC?
    > http://www.aspfaq.com/show.asp?id=2126
    >
    >> [Microsoft][ODBC Microsoft Access Driver] You tried to execute a
    >> query that does not include the specified expression 'iscore' as part
    >> of an aggregate function.
    >>

    > Oops. It's the ORDER BY, whouch should read:
    >
    > "order by MIN(iscore) asc")
    >
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > 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"
    >


    This works fine. But now I have an issue. Using the data I retrieved from
    the viva table, I have created another variable called varHDCP on the asp
    page. This is based on a calculation from the table data. Is there a way
    that I can sort using this? It doesn't exist anywhere in a table. Or should
    I put it in a table, and then retrieve it?
     
    Jeff, Jan 3, 2006
    #8
  9. Jeff wrote:
    >
    > This works fine. But now I have an issue. Using the data I retrieved
    > from the viva table, I have created another variable called varHDCP
    > on the asp page. This is based on a calculation from the table data.
    > Is there a way that I can sort using this? It doesn't exist anywhere
    > in a table. Or should I put it in a table, and then retrieve it?


    Can it be calculated in the query? If it's not part of the query results,
    the query engine cannot sort by it. You would have to put the data into an
    array, or perhaps an ad hoc recordset, in order to sort by it.

    --
    Microsoft MVP - ASP/ASP.NET
    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 [MVP], Jan 3, 2006
    #9
    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. RedEye
    Replies:
    5
    Views:
    3,542
    Juan T. Llibre
    Jul 21, 2005
  2. Replies:
    2
    Views:
    661
  3. ecoolone
    Replies:
    0
    Views:
    798
    ecoolone
    Jan 3, 2008
  4. mattdaddym

    asp .net 1.1 + sql weird issue

    mattdaddym, Jan 31, 2008, in forum: .NET
    Replies:
    0
    Views:
    410
    mattdaddym
    Jan 31, 2008
  5. RedEye
    Replies:
    2
    Views:
    140
    RedEye
    Jul 14, 2005
Loading...

Share This Page