Code not pulling enough records

Discussion in 'ASP General' started by Jeff, Jan 23, 2006.

  1. Jeff

    Jeff Guest

    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, Jan 23, 2006
    #1
    1. Advertising

  2. Jeff

    Jeff Guest

    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" <> wrote in message
    news:...
    > 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, Jan 23, 2006
    #2
    1. Advertising

  3. 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], Jan 23, 2006
    #3
  4. Jeff

    Jeff Guest

    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/tourney/players.asp?tourney_id=12


    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > 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, Jan 23, 2006
    #4
  5. Jeff

    Jeff Guest

    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]" <> wrote in message
    news:...
    > 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, Jan 23, 2006
    #5
  6. Jeff

    Jeff Guest

    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]" <> wrote in message
    news:...
    > 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, Jan 23, 2006
    #6
  7. 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], Jan 23, 2006
    #7
  8. Jeff

    Jeff Guest

    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]" <> wrote in message
    news:...
    > 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, Jan 23, 2006
    #8
  9. 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,adCmdText
    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"
     
    Bob Barrows [MVP], Jan 23, 2006
    #9
  10. Jeff

    Jeff Guest

    as always, thanks for the help Bob!


    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > 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,adCmdText
    > 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, Jan 23, 2006
    #10
  11. Jeff

    McKirahan Guest

    "Jeff" <> wrote in message
    news:...
    > 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.
     
    McKirahan, Jan 24, 2006
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luke Airig
    Replies:
    0
    Views:
    812
    Luke Airig
    Dec 31, 2003
  2. Replies:
    3
    Views:
    700
  3. jobs
    Replies:
    0
    Views:
    1,511
  4. Dan

    Delete records or update records

    Dan, May 10, 2004, in forum: ASP General
    Replies:
    1
    Views:
    474
    Ray at
    May 10, 2004
  5. Replies:
    3
    Views:
    679
    Anthony Jones
    Nov 2, 2006
Loading...

Share This Page