Loop results almost right...help.

Discussion in 'ASP General' started by eyoung@ncsa.uiuc.edu, Feb 8, 2006.

  1. Guest

    Can someone tell me what I've done wrong here...this kinda works...but
    no cigar!
    I want to loop threw each "Service Number" and get all entries before
    going on to the next.

    What looks something like this (didn't include all fields in example):

    5554930631
    Cellular Toll Charges 5553452212 11/30/05
    2.47
    Cellular Roming 9999999999 11/29/05
    0.69
    Cellular Air Time 5553455555 11/12/05
    3.45
    Cellular Air Time 9006661234 11/17/05
    9.99
    5556491514
    5556493465
    5556496458

    it works the first time threw but then just gives me the Service
    Number.



    <!--#include file="include.asp" -->
    <%
    Dim connect, rs, connect2, rs2
    Dim sSQL, sSQL2

    Set connect = Server.CreateObject("ADODB.Connection")
    Set rs = Server.CreateOBject("ADODB.Recordset")
    connect.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sTelecom
    connect.open

    Set connect2 = Server.CreateObject("ADODB.Connection")
    Set rs2 = Server.CreateOBject("ADODB.Recordset")
    connect2.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sTelecom
    connect2.open
    %>
    <%
    sSQL = "SELECT *" & _
    " FROM 06_Statements" & _
    " WHERE [Billing Date] = '" & Request.Form("Month") & "'" & _
    "And [Total Charge] > 0" & _
    " AND Description LIKE'%Cellular%'"
    set rs = Connect.Execute(sSQL)

    sSQL2 = "SELECT DISTINCT [Service Number]" & _
    " FROM 06_Statements" & _
    " WHERE [Billing Date] = '" & Request.Form("Month") & "'" & _
    " And [Total Charge] > 0" & _
    " AND Description LIKE'%Cellular%'" & _
    " ORDER BY [Service Number]"
    set rs2 = Connect2.Execute(sSQL2)
    %>
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html;
    charset=iso-8859-1">
    </head>

    <body>
    <table>
    <tr>
    <td>Service Number</td>
    <td>Subscriber</td>
    <td>Description</td>
    <td>To Number</td>
    <td>Duration</td>
    <td>Connect Date</td>
    <td>Amount</td>
    </tr>
    <%
    Do until rs2.eof
    %>
    <tr>
    <td><%=rs2("Service Number")%></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    </tr>
    <%
    Dim sBackgroundColor
    sBackgroundColor = "#ffffff"

    Do until rs.eof

    If rs("Service Number") = rs2("Service Number") Then
    If sBackgroundColor = "#ffffff" Then
    sBackgroundColor = "#f7f7f7"
    Else
    sBackgroundColor = "#ffffff"
    End If
    %>

    <tr bgcolor="<%=sBackgroundColor%>">
    <td><%=rs("Service Number")%></td>
    <td><%=rs("Subscriber User Name")%></td>
    <td><%=rs("Description")%></td>
    <td><%=rs("To Number")%></td>
    <td align="center"><%=rs("Duration")%></td>
    <td><%=rs("Connect Date")%></td>
    <td align="right"><%=rs("Total Charge")%></td>
    </tr>

    <% End If
    rs.MoveNext
    Loop %>

    <%
    rs2.MoveNext
    Loop %>
    </table>
    </body>
    </html>
     
    , Feb 8, 2006
    #1
    1. Advertising

  2. Guest

    oops...this thing cut off my rows..it should look like

    Cellular Toll Charges 5553452212 11/30/05 2.47
    Cellular Roming 9999999999 11/29/05 0.69
    Cellular Air Time 5553455555 11/12/05 3.45
    Cellular Air Time 9006661234 11/17/05 9.99
     
    , Feb 8, 2006
    #2
    1. Advertising

  3. Larry Bud Guest

    wrote:
    > Can someone tell me what I've done wrong here...this kinda works...but
    > no cigar!
    > I want to loop threw each "Service Number" and get all entries before
    > going on to the next.
    >
    > What looks something like this (didn't include all fields in example):
    >

    Geez, you're doing it the hard (and wrong) way.

    You need to create one SQL statement that joins the two tables.
     
    Larry Bud, Feb 8, 2006
    #3
  4. Guest

    Wow...that wasn't really helpful
     
    , Feb 8, 2006
    #4
  5. Guest

    My inner until loop is only being activated during the first loop of
    the outer until loop?
    Both loops work apart.
     
    , Feb 8, 2006
    #5
  6. wrote:
    > Wow...that wasn't really helpful

    Actually, it could have been, if you understood what was meant by joining
    the tables in your query. Is that the case? That you don't understand
    joining tables?
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 8, 2006
    #6
  7. Guest

    I didn't join tables because this is ONE table.

    One query statement gets distinct 'Service Numbers' for my outer loop
    The other query statment gets every record.

    I then loop using each distinct 'Service Number' getting the records
    associated with it before going to the next 'Service Number'

    Again this is only ONE table. Because I was wanting distinct elements
    for the outer loop I found it easier to query the table twice.
     
    , Feb 8, 2006
    #7
  8. wrote:
    > Can someone tell me what I've done wrong here...
    >
    > Set connect = Server.CreateObject("ADODB.Connection")
    > Set rs = Server.CreateOBject("ADODB.Recordset")
    > connect.ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & sTelecom
    > connect.open
    >
    > Set connect2 = Server.CreateObject("ADODB.Connection")
    > Set rs2 = Server.CreateOBject("ADODB.Recordset")
    > connect2.ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=" & sTelecom
    > connect2.open


    1. You don't need two idential connections here.


    > sSQL = "SELECT *" & _
    > " FROM 06_Statements" & _
    > " WHERE [Billing Date] = '" & Request.Form("Month") & "'" & _
    > "And [Total Charge] > 0" & _
    > " AND Description LIKE'%Cellular%'"
    > set rs = Connect.Execute(sSQL)
    >
    > sSQL2 = "SELECT DISTINCT [Service Number]" & _
    > " FROM 06_Statements" & _
    > " WHERE [Billing Date] = '" & Request.Form("Month") & "'" & _
    > " And [Total Charge] > 0" & _
    > " AND Description LIKE'%Cellular%'" & _
    > " ORDER BY [Service Number]"
    > set rs2 = Connect2.Execute(sSQL2)


    2. You omit the ORDER BY clause in the first query.
    3. Aside from ordering, the second recordset is a subset of the first, so it
    is unneccessary.


    > If rs("Service Number") = rs2("Service Number") Then


    4. Use of default properties. Take your task seriously -- compare values to
    values, not the inherently dicey objects to objects:

    If rs.Fields("Service Number").Value = ...



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, Feb 8, 2006
    #8
  9. Guest

    I placed the ORDER BY in my first query
    And I'm now comparing values instead of objects...I aget the same
    results.

    Granted two queries is not the best way to do something.

    However there is something wrong with the logic of the 2 statments (not
    queries) together.
    Both queries get the info I want, and both statements work appart. But
    the statements don't work together.
     
    , Feb 8, 2006
    #9
  10. wrote:
    > I didn't join tables because this is ONE table.


    Ah! Now we're getting somewhere.
    That makes no difference. Using table aliases, you can join the same table
    to itself as many times as you need to.

    Let's go back to your queries and try to see what you should be doing ...
    You don't need to join anything. You don't even need two queries! You're
    overcomplicating this.
    Try this (since I obviously cannot test this, there may be typos):


    sSQL = "SELECT [Service Number],[Subscriber User Name]," & _
    "[Description],[To Number],[Duration],[Connect Date], " & _
    "[Total Charge] "
    " FROM 06_Statements" & _
    " WHERE [Billing Date] = ?" & _
    "And [Total Charge] > 0" & _
    " AND Description LIKE '%Cellular%'" & _
    " ORDER BY [Service Number]"

    dim cmd, arData
    set cmd=createobject("adodb.command")
    cmd.commandtype = 1 'adCmdText
    cmd.commandtext = sSQL
    set cmd.activeconnection=Connect
    set rs=cmd.execute(,array(Request.Form("Month")))
    if not rs.eof then arData = rs.getrows
    rs.close: set rs = nothing
    Connect.close: set Connect= nothing

    if not isarray(arData) then
    response.write "no data"
    response.end
    end if
    %>
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html;
    charset=iso-8859-1">
    </head>

    <body>
    <table>
    <tr>
    <th>Service Number</th>
    <th>Subscriber</th>
    <th>Description</th>
    <th>To Number</th>
    <th>Duration</th>
    <th>Connect Date</th>
    <th>Amount</th>
    </tr>
    <%
    dim curServNum, newServNum, i, j
    for i = 0 to ubound(arData,2)
    newServNum=arData(0,i)
    if newServNum<>curServNum then
    curServNum = newServNum
    response.write "<tr><td colspan=""7"">" & _
    curServNum & "</td></tr>"
    end if
    response.write "<tr>"
    for j=1 to 6
    if j = 1 then
    response.write "<td colspan=""2"">"
    else
    response.write "<td>"
    end if
    response.write arData(j,i) & "</td>"
    next
    response.write "</tr>"
    next
    %>

    </table>
    </body>
    </html>

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 8, 2006
    #10
  11. Guest

    Ok, this is what I did not understand

    wrote:
    > Do until rs2.eof
    > Do until rs.eof
    > rs.MoveNext
    > rs2.MoveNext



    This is all anyone needs to know. Before you reach the second record of
    rs2,
    you have moved to the end of rs, so rs.EOF will be true from then on.

    --
    Thanks Dave
     
    , Feb 9, 2006
    #11
  12. Please do not top post on USENET when interlacing comments. It breaks the
    flow of the conversation for the reader.

    wrote:
    >>> Do until rs2.eof
    >>> Do until rs.eof
    >>> rs.MoveNext
    >>> rs2.MoveNext

    >>
    >> This is all anyone needs to know. Before you reach the
    >> second record of rs2, you have moved to the end of rs,
    >> so rs.EOF will be true from then on.

    >
    > Ok, this is what I did not understand


    I recognize that you thought recordset iteration was an analog to array
    iteration, and in many ways, it is. But even with arrays, your O(n*n)**
    nested looping is seriously frowned on whether you are using arrays or any
    other structure. This is the reason ALL of the responders have suggested
    that you change your approach.

    Think about it in terms of your goals. You want to produce a list. Ideally,
    your data structure should look like that list. Then you merely step through
    it once and display whatever is appropriate for the given row.

    Aggregating, summarizing and ordering your data can add overhead, and you
    will need to make decisions on the best place to do that. In general, your
    database is going to be better at those (as in MORE EFFICIENT) than you are.

    Nothing could be a better example of this than sorting. Databases use
    sophisticated sorting techinques that may take advantage of any number of
    algorithms, but you can generally no worse than expect O(n*log(n)) from the
    database for a full sort, while your approach -- a less-sorted "sorting and
    sieving" -- was a solid O(n*n).



    **http://en.wikipedia.org/wiki/Computational_complexity_theory#Overview

    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
     
    Dave Anderson, Feb 9, 2006
    #12
    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. Irmen de Jong
    Replies:
    0
    Views:
    264
    Irmen de Jong
    Oct 30, 2004
  2. Irmen de Jong
    Replies:
    2
    Views:
    346
    Irmen de Jong
    Nov 3, 2004
  3. richard

    almost working right

    richard, Apr 14, 2008, in forum: HTML
    Replies:
    2
    Views:
    376
    BootNic
    Apr 15, 2008
  4. a s
    Replies:
    16
    Views:
    4,635
    JustJohn
    Mar 8, 2011
  5. Isaac Won
    Replies:
    9
    Views:
    387
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page