|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Another SQL issue with asp
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 wrote:
Show quote > hey guys. Thanks for all the help in the past. For some reason, ASP You need to read up on the difference between DISTINCT and GROUP BY.> 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? 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"
Show quote
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message Thanks for the fast reply Bob. I do know I have much to learn. I sortof dove news:u5GUPrHEGHA.984@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" > 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:
>> ODBC?>> 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' http://www.aspfaq.com/show.asp?id=2126 > [Microsoft][ODBC Microsoft Access Driver] You tried to execute a Oops. It's the ORDER BY, whouch should read:> query that does not include the specified expression 'iscore' as part > of an aggregate function. > "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?? Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:eggDExHEGHA.1032@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" >
Show quote
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message This works fine. But now I have an issue. Using the data I retrieved from news:eggDExHEGHA.1032@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" > 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:
> Can it be calculated in the query? If it's not part of the query results, > 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? 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" > set bam1 = conn.execute ("select username as user, MIN(iscore) " & _ How could one use parameters in a SQL statement when "var4" could be> "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. 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:
>> http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd>> 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: or, if one has an aversion to saved parameter queries: http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e > validation of user inputs is the first layer of security, almost the most > 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 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"  |
|||||||||||||||||||||||
Other interesting topics