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? 8 1350
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:u5*************@tk2msftngp13.phx.gbl... 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 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"
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl... 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"
> 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 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/micro...d322b882a604bd
or, if one has an aversion to saved parameter queries: http://groups-beta.google.com/group/...e36562fee7804e 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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl... 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 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" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Oscar |
last post by:
I am looking for a way to pass an ADO recordset that has been retrieved in
an ASP page to another HTML-page. Is there someone who can provide me with a
small sample or a link to see how this is...
|
by: Michael |
last post by:
As if we needed another string reversal question.
I have a problem with the following code, that I believe should work.
int StringReverse(char* psz)
{
char *p = psz;
char *q = psz +...
|
by: Tyler Style |
last post by:
Hullo - looking for a little advice here. I have a form on a page in one
domain submitting to a cgi in another domain. Weirdly, on some Windows XP
systems, a form on the page fails to submit/post...
|
by: christopher diggins |
last post by:
I have posted a C# critique at
http://www.heron-language.com/c-sharp-critique.html. To summarize I bring up
the following issues :
- unsafe code
- attributes
- garbage collection
-...
|
by: Paul |
last post by:
public class A
{
public A ()
{
// here I would like to call the second version of _ctor, how to
accomplish this ?
}
public A (int a, int b, int c)
{
|
by: Ben Rush |
last post by:
Hello World,
Okay, I have spent the day browsing the newsgroups and reading up on
article after article concerning ViewState corruption and so forth, and I
have a couple questions. We...
|
by: M O J O |
last post by:
Hi,
I want to expose a enum from another class, is that possible and how?
Here's an example
Public Class ClassMaster
Public Enum Colors
|
by: David Garamond |
last post by:
Has anyone tried using inet or cidr for storing GUID (or for storing
128bit numbers or hashes or similar stuffs)? It has a nice property in
that one can use hexadecimal notation (like...
|
by: Charles Leonard |
last post by:
I am having yet another issue with Windows Server 2003. This time, the web
service (a file import web service) appears to run except for one odd
message: "ActiveX component can't create object". ...
|
by: vbace2 |
last post by:
I have searched this forum, and the web, and I have not been able to find a solution to my issue. I may not have used the right search information to find the answer, but I found a lot of issues...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |