Another SQL issue with asp

[Jeff]

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?

 

[Bob Barrows [MVP]]

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.

 

[Jeff]

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.

 

[Bob Barrows [MVP]]

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")

 

[Jeff]

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

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"

 

[Larry Bud]

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.

 

[Bob Barrows [MVP]]

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

 

[Jeff]

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?

 

[Bob Barrows [MVP]]

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.