Results 1 to 8 of 8

Thread: LOOP confusion

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    LOOP confusion

    I am trying to build a nested loop from a SQL query in asp. I haven't done this in a while and am a bit fuzzy. Thought I'd show you guys what i've done to see if i'm on the right track.

    1) Do a query to find the number of rows to be iterated thru:

    <font color=448800>strSQL = "Select Count(Categories) from tblOutlookContacts"
    Set Result = objConn.Execute(strSQL)
    Result.MoveFirst
    NumCategories = Result.Fields(0)</font color=448800>

    2) Do a new query to open the db with the fields desired, move to first record, load the Categories for the first row into a varaible:

    <font color=448800>strSQL = "SELECT Categories, CRCGType, MemberType, FullName, MailingAddress FROM tblOutlookContacts ORDER BY Categories"
    myRS.Open strSQL, objConn, adOpenStatic, adCmdTable
    myRS.MoveFirst

    CountiesCovered = myRS.Fields("Categories")</font color=448800>

    3) Start a For...Next loop with the total number of rows set as the loop limit, response.write the loaded Varaible value:
    <font color=448800>for i = 0 to NumCategories

    Response.Write "Counties Covered: " & CountiesCovered & "<b r>"
    </font color=448800>
    4) TEST that the variable value hasn't changed; if it has, print a new line with the new value:

    <font color=448800>if CountiesCovered <> CountiesCovered then
    Response.Write "Counties Covered: " & myRS.Fields("Categories") & "<b r>"
    end if</font color=448800>

    5) Between the test on the initial variable and the myRS.NextRecord and is increment of i to i +1 there is a 'body' section that should print out for each record where the Categories value has NOT CHANGED:
    <font color=448800>Do While CountiesCovered = CountiesCovered

    Response.Write "Member Type: " & myRS.Fields("MemberType") & ",b r." & _
    "CRCG Type: " & myRS.Fields("CRCGType") & "<b r>" & _
    "Full Name: " & myRS.Fields("FullName") & "<b r>" & _
    "Mailing Address: " & myRS.Fields("MailingAddress") & "<b r>"

    myRS.MoveNext

    CountiesCovered = myRS.Fields("Categories")
    Response.Write "NextRecord Categories: " & CountiesCovered & "<b r><hr>"

    LOOP
    i = i + 1

    next
    </font color=448800>

    When I run all this I get only the first printout of the CountiesCovered variable and then all the rest of the records as called for in the body format. Something is not working in the handling of the CountiesCovered variable, it would seem. What I want is a page to display showing all the information for each set of rows where the myRS.Fields("Categories") is the same. I'd actually like to group on at least one more field value but for now would be happy to have at least this much working.

    Perhaps my use of For-Next with the embedded Do LOOP is incorrect? Or do I need to re-assign the variable in a different place? (I've tried before the LOOP and after the i = i + 1 statement but i'm sure it should be after myRS.MoveNext.

    Is there a glaring error here? I'm continuing to work on this but wanted to see if someone might spot an obvious flaw in the logic being used.

    TIA!

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: LOOP confusion

    I usually do this the other way around...

    While not rs.EOF
    If ... Then... Else... End If
    detail processing
    rs.MoveNext
    Wend

    By having the "header" outside your Do loop, I think you are running it once only. Maybe it needs to be repeated after you increment i.

    Back in early April I posted some threads on the Access board involving ways to generate "leading" subtotals to create a hierarchical, clickable tree display in a web page using just JavaScript (no ActiveX controls). One way was to use data shaping. This wasn't the most efficient time-wide, but it's relatively easy to work with. I don't have an archive of those threads, but here is some sample code:

    <pre>Dim objConn, strConnect, objCommand, objRS
    ' Set up connection object and OLEDB string for SQL Server database
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Provider = "MSDataShape" 'Use Shaping provider
    objConn.Properties("Data Provider").Value = "SQLOLEDB"
    objConn.Properties("Data Source").Value = "XXXXXXXX"
    objConn.Properties("User ID").Value = "XXXXXXXX"
    objConn.Properties("Password").Value = ""
    ' Open the connection
    objConn.Open 'strConnect

    ' Set up command (query) object
    Set objCommand = Server.CreateObject("ADODB.Command")
    objCommand.ActiveConnection = objConn
    objCommand.CommandType = adCmdText
    objCommand.CommandText = "SHAPE {" & _
    "SELECT Employee.EmpInitials AS Tkpr, Client.CliNickName, Client.CliSysNbr, " & _
    "Matter.MatNickName, Matter.MatSysNbr, " & _
    "Sum(BilledTime.BTActualHrsWrk) AS HrsLogged, Sum(BilledTime.BTAmount) AS ValueLogged, " & _
    "Sum(0) AS HrsWIP, Sum(BilledTime.BTHrsOnBill - " & _
    "BilledTime.BTActualHrsWrk) AS HrsWO, Sum(BilledTime.BTHrsOnBill) AS HrsBilled " & _
    "FROM ((BilledTime INNER JOIN Matter ON BilledTime.BTMatter = Matter.MatSysNbr) " & _
    "INNER JOIN Client ON Matter.MatCliNbr = Client.CliSysNbr) INNER JOIN Employee ON " & _
    "BilledTime.BTTkpr = Employee.EmpSysNbr " & _
    "WHERE (BilledTime.BTDate Between '" & strDateStart & "' And '" & strDateEnd & "') AND " & _
    "((BilledTime.BTBillableFlg = 'Y') OR (BilledTime.BTRate * (BilledTime.BTHrsOnBill - " & _
    "BilledTime.BTActualHrsWrk) > 0)) " & _
    "GROUP BY Employee.EmpInitials, Client.CliNickName, Client.CliSysNbr, " & _
    "Matter.MatNickName, Matter.MatSysNbr " & _
    "HAVING Employee.EmpInitials IN " & strAttyList & _
    "UNION ALL " & _
    "SELECT Employee.EmpInitials AS Tkpr, Client.CliNickName, Client.CliSysNbr, " & _
    "Matter.MatNickName, Matter.MatSysNbr, " & _
    "Sum(UnbilledTime.UTActualHrsWrk) AS HrsLogged, Sum(UnbilledTime.UTAmount) AS ValueLogged, " & _
    "Sum(UnbilledTime.UTActualHrsWrk) AS HrsWIP, Sum(UnbilledTime.UTHoursToBill - " & _
    "UnbilledTime.UTActualHrsWrk) AS HrsWO, Sum(0) AS HrsBilled " & _
    "FROM (UnbilledTime INNER JOIN (Matter INNER JOIN Client ON Matter.MatCliNbr = " & _
    "Client.CliSysNbr) ON UnbilledTime.UTMatter = Matter.MatSysNbr) INNER JOIN Employee ON " & _
    "UnbilledTime.UTTkpr = Employee.EmpSysNbr " & _
    "WHERE (UnbilledTime.UTDate Between '" & strDateStart & "' And '" & strDateEnd & "') AND " & _
    "((UnbilledTime.UTBillableFlg = 'Y') Or (UnbilledTime.UTRate * (UnbilledTime.UTActualHrsWrk - " & _
    "UnbilledTime.UTHoursToBill) > 0)) " & _
    "GROUP BY Employee.EmpInitials, Client.CliNickName, Client.CliSysNbr, " & _
    "Matter.MatNickName, Matter.MatSysNbr " & _
    "HAVING Employee.EmpInitials IN " & strAttyList & _
    "ORDER BY Employee.EmpInitials, Client.CliNickName, Matter.MatNickName, Matter.MatSysNbr" & _
    "} AS rsDetail"

    ' Set up recordset and run query
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.CacheSize = 20
    objRS.Open objCommand, , adOpenForwardOnly, adLockReadOnly

    ' Check for nothing found before setting up table
    If objRS.EOF Then 'recordset is empty
    Response.Write "<p CLASS=""bld"">Boo! No time found.</p>"
    Else 'build aggregate parent recordsets by reshaping
    Dim objRS2, objRS3, objRS4, objRS5, objRS6
    Set objRS2 = Server.CreateObject("ADODB.Recordset")
    objRS2.Open _
    "SHAPE rsDetail " & _
    "COMPUTE rsDetail, SUM(rsDetail.HrsLogged) AS HrsLogged, " & _
    "SUM(rsDetail.ValueLogged) AS ValueLogged, SUM(rsDetail.HrsWIP) AS HrsWIP, " & _
    "SUM(rsDetail.HrsWO) AS HrsWO, SUM(rsDetail.HrsBilled) AS HrsBilled " & _
    "BY Tkpr, CliNickName, CliSysNbr, MatNickName, MatSysNbr", objConn

    Set objRS3 = Server.CreateObject("ADODB.Recordset")
    objRS3.Open _
    "SHAPE " & objRS2.Properties("Reshape Name") & " AS rsMatters " & _
    "COMPUTE rsMatters, SUM(rsMatters.HrsLogged) AS HrsLogged, " & _
    "SUM(rsMatters.ValueLogged) AS ValueLogged, SUM(rsMatters.HrsWIP) AS HrsWIP, " & _
    "SUM(rsMatters.HrsWO) AS HrsWO, SUM(rsMatters.HrsBilled) AS HrsBilled " & _
    "BY Tkpr, CliNickName, CliSysNbr", objConn

    Set objRS4 = Server.CreateObject("ADODB.Recordset")
    objRS4.Open _
    "SHAPE " & objRS3.Properties("Reshape Name") & " AS rsClients " & _
    "COMPUTE rsClients, SUM(rsClients.HrsLogged) AS HrsLogged, " & _
    "SUM(rsClients.ValueLogged) AS ValueLogged, SUM(rsClients.HrsWIP) AS HrsWIP, " & _
    "SUM(rsClients.HrsWO) AS HrsWO, SUM(rsClients.HrsBilled) AS HrsBilled " & _
    "BY Tkpr", objConn

    ' Create recordset objects to drill down into Client Totals and Matter Totals
    Set objRS5 = Server.CreateObject("ADODB.Recordset")
    Set objRS6 = Server.CreateObject("ADODB.Recordset")

    ' Loop through RS and output results
    While Not objRS4.EOF
    ' Write Tkpr summary line and cumulate grand totals [omitted]
    ' Drill down to (filtered) client subtotals
    Set objRS5 = objRS4("rsClients").Value
    While Not objRS5.EOF
    ' Write Client summary line [Response.Write statements omitted]
    ' Drill down to (filtered) matter subtotals
    Set objRS6 = objRS5("rsMatters").Value
    While Not objRS6.EOF
    '[Response.Write statements omitted]
    objRS6.MoveNext
    Wend
    objRS5.MoveNext
    Wend
    objRS4.MoveNext
    Wend
    'Clean up
    Set objRS6 = Nothing
    Set objRS5 = Nothing
    Set objRS4 = Nothing
    Set objRS3 = Nothing
    Set objRS2 = Nothing
    End If
    ' Clean up
    objConn.Close
    Set objRS = Nothing
    Set objCommand = Nothing
    Set objConn = Nothing</pre>

    This example is overkill, but it shows how far you can take this. I went from two tables of raw data to a hierarchy by Timekeeper/Client/Matter with subtotals and grand totals across five columns. It let me lay out the document so that I could click a totals row to reveal the next level of subtotals, and so on. Eventually I found a lower overhead way to do this, but for your purposes, you might consider whether the shaping provider might help.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP confusion

    hmm... MSDataShapes. Interesting! never heard of that before. I will have to look into this. It sounds a little like the DataGrid control in .NET

    As it stands, I have a few concerns about the solution I have currently worked out. I have a Do...LOOP to cycle thru all the rows and an If...Then to compare the value of the grouping variable as it changes from the initial value to the one after MoveNext. On problem with this is I have to explictly printout the values of the first record before going into the loop structure and I'm concerned about a situation where the first record has more than one row to be printed as detail data. I could just be doing something a little wrong tho. The other thing, which I find truly mysterious, is that even though I declare Do While NOT EOF...Loop the process 'crashes' at the end by trying to step into a row at the end doesn't exist (because EOF is true). I tried wrapping the MoveNext with If Not EOF Then to keep it from an illegal increment, but that doesn't keep the code from wildly going where no code should go. I tried using your suggested While Not...Wend and the same thing happens. Strange! This is not a huge problem, it's just not correct.

    OTOH, I'd like to have a little more control over the results and would like to have more than one level of grouping, and the DataShapes method might give me more options. I'll have to check it out. Thanks for the tip!

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP confusion

    After looking into the issue, this Shape stuff seems to be precisely what I'd like. I am having trouble implementing the SHAPES string correctly and thought I'd ask if you noticed a particular problem:
    <font color=448800>
    objCommand.CommandText = "SHAPE " & _
    "{SELECT Categories, CRCGType, MemberType FROM tblOutlookContacts} " & _
    "APPEND ({SELECT Categories, FullName, Email1Address FROM tblOutlookContacts} " & _
    "RELATE Categories TO Categories) AS objRSCategories"</font color=448800>

    This gives me some output but bombs at the line

    <font color=448800>SET objRSCategories = objRS("Categories").Value</font color=448800>

    With an <font color=red>Object Required</font color=red> asp error.

    Apparently it doesn't like my setting of Categories and a GroupBy value (GroupBy in the sense of what Access reports do, not SQL). I am looking at dozens of pages re. Shapes and am a little confused on how, when and if to use RELATE. In this particular case, I'm reading from one table and trying to implement groupings. Ideally, I want a grouping on Categories and CRCGType with the contact information details for that grouping.

    Thanks for your help!

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: LOOP confusion

    I'm lost on what your query does. Can you combine SELECT and APPEND in the same query? I'm not sure my brain (let alone the shaping provider) can handle this. I think it also might not like the internal curly braces and/or the lack of a closing curly brace.

    The general idea, in my limited, three-month old experience, is to start with all of the detail and wrap that in one shaped dataset. You can use GROUP BY clauses in the first query, but after that, you can aggregate at higher levels of abstraction (or group on different fields) by incorporating the first shaped dataset into a second, third, etc.

    In my experiments I used SQL Books Online, which I downloaded from msdn or technet somewhere, and Chapter 13 of the invaluable ADO 2.6 Programmer's Reference, from WROX.

    Anyway, Charlotte knows way more about this than I do, so if you want to continue the debugging process in the Access forum, you can get some expert assistance there.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP confusion

    Yes, you can use SELECT and APPEND in the same query -- that's the point of DataShapes. From what I gather, you SELECT items that may be used as a parent elements (or a node) and APPEND items that may be used a child elements to the parent element (or node). And if you look closely, there is no hanging curly brace. Here's an example of a Shapes query that looks very similar, curly braces and all: http://support.microsoft.com/default.aspx?...&NoWebContent=1

    What I am trying to do in this query is, in a SINGLE TABLE, list all the contact info across rows for any set of rows with the same info in 'Categories'. The examples I have seen all use 2 tables and Shapes to create parent/child relationships, which makes me wonder if I am trying to do something Shapes isn't supposed; I might have to go back to some kind of sorting procedure as i have been doing, or split the source table to get this puppy to work.

    One interesting point made by MS is that the Shapes provider is what underlies the Group By stuff in Access Reports, and that means to me whatever Access reports can do I should be able to do programmatically using Shapes. And I have been able to put together an Access report that does what I want using one table. I don't want to use Access reports because they suck for editing post-production. Also i have some special formatting needs for the final product that should be easy to realize using asp once I get this grouping problem solved.

    Anyhow, I'll look for the SQL book online that you mentioned. I can also re-post a SHAPES question to the Access lounge. This project has taken me all over the map, as I have asked questions about using Word mergefield criteria (thinking that was all that was needed) to asking about using SQL GROUP BY (thinking that's how Access did it's reports) to just trying to implement something in asp using loops (which does work kinda-sorta but I'm mighty tempted by this Shapes alternative). So that's Word, Access, Web Scripting formums for one problem. Sheesh!

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: LOOP confusion

    Steve, it sounds as though you simply want a normal SELECT and loop, and you don't really need anything more complicated because you are not actually aggregating anything, just grouping/sorting.

    I think the problem with your original code is that this line belongs in the If...End If loop in Step 4, not in the Do...Loop loop in Step 5:

    CountiesCovered = myRS.Fields("Categories")

    That should fix the original problem. Sorry I didn't see this originally, I couldn't find the beginning of the For...Next loop (it's the green text; really).

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LOOP confusion

    thanks but for the moment i am exploring the world of data shaping. i am, for the moment, out of the LOOP.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •