Code not pulling enough records

[Jeff]

Hey gang.
i have a code that i will list. when varM = 8 or 16, the script works fine,
and pulls the top 8 or top 16, but if it =32 or 64, it is only pulling the
top 17 records from the DB.
db is access and this is MS server.
here is the code

<%
if varm = 8 then
set admin6 = conn.execute("select top 8 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 16 then
set admin6 = conn.execute("select top 16 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 32 then
set admin6 = conn.execute("select top 32 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 64 then
set admin6 = conn.execute("select top 64 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else
end if
end if
end if
end if

IF NOT admin6.EOF AND NOT admin6.BOF THEN

do while not admin6.eof

varnm = admin6.fields.item("username").value
varch = admin6.fields.item("iCHECK").value

if varch = "Yes" then
rowcolor = "#4477aa"
fontcolor = "#ffffff"
else
rowcolor = "#FFFFFF"
fontcolor= "#000000"
end if

%>

what i need to know, is if there is another way to pull a certain amount of
records from a DB. the reason it is in mulitples of 8, is because this is
for a tournament site, so the brackets are set in multiples of 8
any ideas??

[Jeff]

i even tried without the if statements by putting the variable in the
statement.
set admin6 = conn.execute("select top " & varm & " username, iCHECK from
members2_tourney where tourney_id = " & varID & "")

i tried using response.write to see what is being passed, and the correct
information is being passed, it just isn't getting 32 or 64 records.
is there a limit on what can be used in a TOP function??

"Jeff" <gi*****@adelphia.net> wrote in message
news:fN******************************@adelphia.com ...

Hey gang.
i have a code that i will list. when varM = 8 or 16, the script works
fine, and pulls the top 8 or top 16, but if it =32 or 64, it is only
pulling the top 17 records from the DB.
db is access and this is MS server.
here is the code

<%
if varm = 8 then
set admin6 = conn.execute("select top 8 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 16 then
set admin6 = conn.execute("select top 16 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 32 then
set admin6 = conn.execute("select top 32 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 64 then
set admin6 = conn.execute("select top 64 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else
end if
end if
end if
end if

IF NOT admin6.EOF AND NOT admin6.BOF THEN

do while not admin6.eof

varnm = admin6.fields.item("username").value
varch = admin6.fields.item("iCHECK").value

if varch = "Yes" then
rowcolor = "#4477aa"
fontcolor = "#ffffff"
else
rowcolor = "#FFFFFF"
fontcolor= "#000000"
end if

%>

what i need to know, is if there is another way to pull a certain amount
of records from a DB. the reason it is in mulitples of 8, is because this
is for a tournament site, so the brackets are set in multiples of 8
any ideas??

[Bob Barrows [MVP]]

Jeff wrote:

Hey gang.
i have a code that i will list. when varM = 8 or 16, the script works
fine, and pulls the top 8 or top 16, but if it =32 or 64, it is only
pulling the top 17 records from the DB.
db is access and this is MS server.
here is the code

<%
if varm = 8 then
set admin6 = conn.execute("select top 8 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 16 then
set admin6 = conn.execute("select top 16 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 32 then
set admin6 = conn.execute("select top 32 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 64 then
set admin6 = conn.execute("select top 64 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else
end if
end if
end if
end if
OMG
Do this:
dim sql
if varm > 0 then
sql="select top " & varm & " username, iCHECK from " & _
"members2_tourney where tourney_id = " & varID & ""
Response.write sql
Set admin6=conn.execute(sql,,1)
If not admin6.EOF then 'no need to check both EOF and BOF

IF NOT admin6.EOF AND NOT admin6.BOF THEN

do while not admin6.eof

varnm = admin6.fields.item("username").value
varch = admin6.fields.item("iCHECK").value

if varch = "Yes" then
rowcolor = "#4477aa"
fontcolor = "#ffffff"
else
rowcolor = "#FFFFFF"
fontcolor= "#000000"
end if

%>

what i need to know, is if there is another way to pull a certain
amount of records from a DB.
No. Not from Access, anyways.
I'm happy to see that you decided to use TOP. Lesser programmers would have
pulled ALL the records from the database and processed only the ones they
needed.
the reason it is in mulitples of 8, is
because this is for a tournament site, so the brackets are set in
multiples of 8
irrelevant :-)
any ideas??

What you have above should work. There is no limitation on TOP that I know
of. If you are really only getting 17 records when running the query from
asp, wihile the same query run in Access returns 32, then I need to see a
repro.

--
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]

ok, here is the response.write

select top 32 username, iCHECK from members2_tourney where tourney_id = 12

so it should be selecting the top32 based of the variable. however, that
isn't what it is showing on the page.
here is the page. i just threw in some names so i could test stuff

http://gig-gamers.com/tourney-zone/t...?tourney_id=12
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...

Jeff wrote:

Hey gang.
i have a code that i will list. when varM = 8 or 16, the script works
fine, and pulls the top 8 or top 16, but if it =32 or 64, it is only
pulling the top 17 records from the DB.
db is access and this is MS server.
here is the code

<%
if varm = 8 then
set admin6 = conn.execute("select top 8 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 16 then
set admin6 = conn.execute("select top 16 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 32 then
set admin6 = conn.execute("select top 32 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 64 then
set admin6 = conn.execute("select top 64 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else
end if
end if
end if
end if

OMG
Do this:
dim sql
if varm > 0 then
sql="select top " & varm & " username, iCHECK from " & _
"members2_tourney where tourney_id = " & varID & ""
Response.write sql
Set admin6=conn.execute(sql,,1)
If not admin6.EOF then 'no need to check both EOF and BOF

IF NOT admin6.EOF AND NOT admin6.BOF THEN

do while not admin6.eof

varnm = admin6.fields.item("username").value
varch = admin6.fields.item("iCHECK").value

if varch = "Yes" then
rowcolor = "#4477aa"
fontcolor = "#ffffff"
else
rowcolor = "#FFFFFF"
fontcolor= "#000000"
end if

%>

what i need to know, is if there is another way to pull a certain
amount of records from a DB.

No. Not from Access, anyways.
I'm happy to see that you decided to use TOP. Lesser programmers would
have pulled ALL the records from the database and processed only the ones
they needed.

the reason it is in mulitples of 8, is
because this is for a tournament site, so the brackets are set in
multiples of 8

irrelevant :-)

any ideas??

What you have above should work. There is no limitation on TOP that I know
of. If you are really only getting 17 records when running the query from
asp, wihile the same query run in Access returns 32, then I need to see a
repro.

--
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]

in case you wanted to know how i got the data for the waiting list, here is
the code

set admin7 = conn.execute("select id, username, iCHECK from members2_tourney
where id not in (select top " & varm & " id from members2_tourney where
tourney_id = " & varID & ")")
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...

Jeff wrote:

Hey gang.
i have a code that i will list. when varM = 8 or 16, the script works
fine, and pulls the top 8 or top 16, but if it =32 or 64, it is only
pulling the top 17 records from the DB.
db is access and this is MS server.
here is the code

<%
if varm = 8 then
set admin6 = conn.execute("select top 8 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 16 then
set admin6 = conn.execute("select top 16 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 32 then
set admin6 = conn.execute("select top 32 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 64 then
set admin6 = conn.execute("select top 64 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else
end if
end if
end if
end if

OMG
Do this:
dim sql
if varm > 0 then
sql="select top " & varm & " username, iCHECK from " & _
"members2_tourney where tourney_id = " & varID & ""
Response.write sql
Set admin6=conn.execute(sql,,1)
If not admin6.EOF then 'no need to check both EOF and BOF

IF NOT admin6.EOF AND NOT admin6.BOF THEN

do while not admin6.eof

varnm = admin6.fields.item("username").value
varch = admin6.fields.item("iCHECK").value

if varch = "Yes" then
rowcolor = "#4477aa"
fontcolor = "#ffffff"
else
rowcolor = "#FFFFFF"
fontcolor= "#000000"
end if

%>

what i need to know, is if there is another way to pull a certain
amount of records from a DB.

No. Not from Access, anyways.
I'm happy to see that you decided to use TOP. Lesser programmers would
have pulled ALL the records from the database and processed only the ones
they needed.

the reason it is in mulitples of 8, is
because this is for a tournament site, so the brackets are set in
multiples of 8

irrelevant :-)

any ideas??

What you have above should work. There is no limitation on TOP that I know
of. If you are really only getting 17 records when running the query from
asp, wihile the same query run in Access returns 32, then I need to see a
repro.

--
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]

Bob, I figured out the problem, now I need to figure a work around.
the problem is, if the varM = 32 but there are less than 32 records, it
won't return all of them.
would it be best to do a count first to see how many there are??
or how should I approach this?
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eV**************@TK2MSFTNGP09.phx.gbl...

Jeff wrote:

Hey gang.
i have a code that i will list. when varM = 8 or 16, the script works
fine, and pulls the top 8 or top 16, but if it =32 or 64, it is only
pulling the top 17 records from the DB.
db is access and this is MS server.
here is the code

<%
if varm = 8 then
set admin6 = conn.execute("select top 8 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 16 then
set admin6 = conn.execute("select top 16 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 32 then
set admin6 = conn.execute("select top 32 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else if varm = 64 then
set admin6 = conn.execute("select top 64 username, iCHECK from
members2_tourney where tourney_id = " & varID & "")
else
end if
end if
end if
end if

OMG
Do this:
dim sql
if varm > 0 then
sql="select top " & varm & " username, iCHECK from " & _
"members2_tourney where tourney_id = " & varID & ""
Response.write sql
Set admin6=conn.execute(sql,,1)
If not admin6.EOF then 'no need to check both EOF and BOF

IF NOT admin6.EOF AND NOT admin6.BOF THEN

do while not admin6.eof

varnm = admin6.fields.item("username").value
varch = admin6.fields.item("iCHECK").value

if varch = "Yes" then
rowcolor = "#4477aa"
fontcolor = "#ffffff"
else
rowcolor = "#FFFFFF"
fontcolor= "#000000"
end if

%>

what i need to know, is if there is another way to pull a certain
amount of records from a DB.

No. Not from Access, anyways.
I'm happy to see that you decided to use TOP. Lesser programmers would
have pulled ALL the records from the database and processed only the ones
they needed.

the reason it is in mulitples of 8, is
because this is for a tournament site, so the brackets are set in
multiples of 8

irrelevant :-)

any ideas??

What you have above should work. There is no limitation on TOP that I know
of. If you are really only getting 17 records when running the query from
asp, wihile the same query run in Access returns 32, then I need to see a
repro.

--
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]]

Jeff wrote:

Bob, I figured out the problem, now I need to figure a work around.
the problem is, if the varM = 32 but there are less than 32 records,
it won't return all of them.
Huh? It will return the top 32 records that satisfy your criteria. If only
17 records satisfy the criteria, that is all that will get returned. Why did
you expect anything different? Did you expect it to return 15 "empty"
records somehow?

would it be best to do a count first to see how many there are?? Why would you need to do that? When you process the recordset, you will find
out how many records there are ...
or how should I approach this?

Perhaps if you explain your requirements in a little more depth, I might be
able to make a suggestion.

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"

[Jeff]

you are correct once again bob. i got it to work, and all is well as far as
that.
now if i may continue on this topic, one more delema then i will be done for
a while.
in that admin_tourney table. i have a field called pos_id this is a numeric
field, that has a value of zero to start. what i want to do, is assign a
random number between 1 however many players are in there. this will be the
position assignments in the tourney bracket.

in other words, i want to randomize the players, so they do not get put into
the bracket in the order that they signed up.

in assigning this random number, they get placed into the tourney bracket
according to that number. so my question is this,
do i call for the data, then assign the random number, then put them back?
or could i make a temp table and assign it there?

or what would be the best way to do this?

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:e4**************@TK2MSFTNGP10.phx.gbl...

Jeff wrote:

Bob, I figured out the problem, now I need to figure a work around.
the problem is, if the varM = 32 but there are less than 32 records,
it won't return all of them.

Huh? It will return the top 32 records that satisfy your criteria. If only
17 records satisfy the criteria, that is all that will get returned. Why
did you expect anything different? Did you expect it to return 15 "empty"
records somehow?

would it be best to do a count first to see how many there are??

Why would you need to do that? When you process the recordset, you will
find out how many records there are ...

or how should I approach this?

Perhaps if you explain your requirements in a little more depth, I might
be able to make a suggestion.

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]]

Jeff wrote:

in assigning this random number, they get placed into the tourney
bracket according to that number. so my question is this,
do i call for the data, then assign the random number, then put them
back?

That's what I would do. This would be one of the rare cases where I would
use a recordset to maintain the data. You can mitigate the inefficiency by
disconnecting the recordset:

set rs=createobject("adodb.recordset")
rs.cursorlocation = adUseClient
rs.open sql,conn,adOpenStatic,adLockBatchOptimistic,adCmdT ext
set rs.activeconnection=nothing
'do your updates, then
set rs.activeconnection = conn
rs.updatebatch

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"

[Jeff]

as always, thanks for the help Bob!
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

Jeff wrote:

in assigning this random number, they get placed into the tourney
bracket according to that number. so my question is this,
do i call for the data, then assign the random number, then put them
back?

That's what I would do. This would be one of the rare cases where I would
use a recordset to maintain the data. You can mitigate the inefficiency by
disconnecting the recordset:

set rs=createobject("adodb.recordset")
rs.cursorlocation = adUseClient
rs.open sql,conn,adOpenStatic,adLockBatchOptimistic,adCmdT ext
set rs.activeconnection=nothing
'do your updates, then
set rs.activeconnection = conn
rs.updatebatch

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"

[McKirahan]

"Jeff" <gi*****@adelphia.net> wrote in message
news:fN******************************@adelphia.com ...

Hey gang.
i have a code that i will list. when varM = 8 or 16, the script works fine, and pulls the top 8 or top 16, but if it =32 or 64, it is only pulling the
top 17 records from the DB.
db is access and this is MS server.
here is the code

[snip]

How about simplifying the code:

<%
Const cSQL = "SELECT TOP # username, iCHECK FROM members2_tourney WHERE
tourney_id = "
Set admin6 = conn.execute(Replace(cSQL,"#",varm) & varID)
Do While Not admin6.EOF
varnm = admin6("username").Value
varch = admin6("iCHECK").Value
If varch = "Yes" Then
rowcolor = "#4477aa"
fontcolor = "#ffffff"
Else
rowcolor = "#FFFFFF"
fontcolor= "#000000"
End If
%>

Or at least use "ElseIF" (no space) instead of "Else If".

Are you sure "iCHECK" returns "Yes"?
A Response.Write(varch) would confirm it.