Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Another SQL issue with asp

Reply
Thread Tools

Another SQL issue with asp

 
 
Jeff
Guest
Posts: n/a
 
      01-03-2006
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?


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-03-2006
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"


 
Reply With Quote
 
 
 
 
Jeff
Guest
Posts: n/a
 
      01-03-2006

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-03-2006
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"


 
Reply With Quote
 
Jeff
Guest
Posts: n/a
 
      01-03-2006
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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
>



 
Reply With Quote
 
Larry Bud
Guest
Posts: n/a
 
      01-03-2006
> 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.

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-03-2006
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"


 
Reply With Quote
 
Jeff
Guest
Posts: n/a
 
      01-03-2006

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-03-2006
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Calling method from another class and SQL select max id issue teser3@hotmail.com Java 2 10-02-2007 10:27 PM
asp and sql statement in sql server db weiwei ASP General 3 09-22-2004 04:12 PM
How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server Belinda ASP General 4 06-11-2004 12:16 PM
ASP SQL - using variables in SQL select screen Ed Garcia ASP General 4 08-07-2003 07:41 PM



Advertisments