Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Code not pulling enough records

Reply
Thread Tools

Code not pulling enough records

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


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



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


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



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



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



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


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



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


 
Reply With Quote
 
Jeff
Guest
Posts: n/a
 
      01-23-2006
as always, thanks for the help Bob!


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



 
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
L A county says enough is enough richard Computer Support 7 02-26-2008 03:27 AM
Datagrid paging using SQL - pulling back <n> records at a time with exact counts dbarker1@progressive.com ASP .Net 3 09-29-2006 12:58 AM
Enough is enough... Imhotep Computer Security 16 09-28-2005 03:36 PM
Enough is enough.... ajacobs2 Digital Photography 33 10-05-2003 12:14 PM
Resolution - when is Enough ENOUGH? (a personal view) VT Digital Photography 43 09-12-2003 11:15 AM



Advertisments