Grouping within a recordset

[bigolslabomeat]

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

[bigolslabomeat]

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 :eek: ). Here's my code adapted from http://www.asp101.com/articles/chris/datashaping/default.asp


<%
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>

[allida77]

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?

[bigolslabomeat]

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