Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping within a recordset

    Let me explain the situation:

    I have to make a dynamic list of events from the contents of a db using asp. The format is where the trouble lies. It is needed to be grouped by the month (like in access reports). Now I think I have worked out how to get it to display only future events, I just now need to group this information by month (the month and year are stored in seperate fileds as is the full date). The actual SQL "Group by" function is not suitable for this as far as I can work out, so does anyone have any ideas?

    TIA

  • #2
    New to the CF scene
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    well i did a little research and i thought i had the answer with data shapes. They seem to do what I want them to. Only thing is my code doesn't work, and froze out my server for a while (100% CPU usage ). Here's my code adapted from http://www.asp101.com/articles/chris...ng/default.asp

    Code:
    <%
    Dim strSQL, rst, strConnectionString, rstChild
    
    strConnectionString = "Provider=MSDataShape;dsn=fea;database=feadata;"
    
    strSQL = "SHAPE { SELECT monthid, month FROM months } " & _
        "APPEND ({ SELECT id, startdate, month, year, event FROM calendar ORDER BY startdate} " & _
        "AS Calendar RELATE monthid to month )"
    
    Set rst = Server.CreateObject("ADODB.Recordset")
    'open recordset
    rst.Open strSQL, strConnectionString
    
    Do While Not rst.EOF
        Response.Write(rst("month") & "<br>&nbsp;")
        Response.Write("Events:")
    
        'set object to child recordset and iterate through
        Set rstChild = rst("Calendar").Value
    
        If Not rstChild.EOF Then
            Do While Not rstChild.EOF
                Response.Write("&nbsp; &nbsp;" & rstChild("event"))
    
                rstChild.MoveNext
            Loop
            rst.MoveNext
        End If
    Loop
    
    rst.Close
    Set rst = Nothing
    %>
    any ideas? any help is greatly appreciated!

    <additional>
    I've just been messing about with access and the "group by" thing in there for reports is EXACTLY what I need to simulate in this asp page, and in actuality i need to group three times; year, month then list of events.

    HELP!!!
    </additional>
    Last edited by bigolslabomeat; 07-16-2002 at 11:15 AM.

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The problem I see in the example is that the "do while loop" is going to run forever because the rst.MoveNext is in a If clause. I would try commenting out things to debug and figure out which part is bombing out. Why can this not be done in your SQL?

  • #4
    New to the CF scene
    Join Date
    Jun 2002
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well spotted!! I moved that around and now its saying this:-

    "Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. "

    Any ideas what that means? I know its the right dsn and that the dsn works cos it's used on a load of other pages within the site.

    What did you mean by:
    Why can this not be done in your SQL?
    ?

    The actual SQL "group by" function didn't seem to be right for what i wanted it to do, it seemed you had to have it acting on an aggregate function, and I just want it to order by year then month


  •  

    Posting Permissions

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