I don't know why I'm having such an issue with this, but I have a psuedo-event calendar that I'm setting up. I have two tables: tblEvent and tblEventCategory. Each event is associated with an event category. What I would like to do is display the category and the category description which are stored in the event category table. Then I would like to count the events that coorelate to that event category.

Something like:

Event Category (9) *this would denote the number of events for the category*
Event Description

I'm having problems laying out my SQL statement anyone have an idea. Here are the table structures if it helps.

<u>tblEventCategory</u>
PK_EventCategoryID
EventCategoryTitle
EventCategoryDescription

<u>tblEvent</u>
PK_EventID
FK_EventCategoryID
EventDate
EventTitle
EventDescription


Thanks in advance

&lt;%
Dim strTitle
strTitle = Request.Form("title"

set rsEvents = conn.execute("SELECT COUNT(tblEventCategory.EventCategoryTitle) AS antal FROM tblEventCategory where tblEventCategory.EventCategoryTitle = '" & strTitle & "' ;"

%&gt;

Thank you for replying. That's not exactly what I need, but I'm sure it will get me started.

I'm actually going to display the category and category description of the page and loop through the records so all categories are displayed. There isn't actually a drop down box to select a category or title for an event. Instead I need the SQL statement to count the records in the events table that correspond to the category.

So when the category page is displayed it will have the category title and category description for all records and then count the number of records in the events table that correspond that way if there are no records a user won't have to click on the category to explore and find nothing available. The actually layout would look something like this on the page.


Spring Events (7)
These are events in the spring

Summer Events (0)
These are events in the summer

Fall Events (2)
These are events in the fall

Winter Events (8)
These are events in the winter

Other Events (0)
These are events if you live on another planet

Does this make sense?

I need to create a recordset that will allow me to count the events that where the FK_EventCategoryID = PK_EventCategoryID, but also allows me to display the Category and the Category description. Is this going to require two recordsets?

Thanks in advance.

exactly the sample codes i gave you to count how many events corresponding to the event title. but that codes applies only for counting and yes, you'll need to use two recordsets for that.

anyway, i'll work on the sample codes tomorrow.

here's the sample code of your problem. i just hope this will help you.

here's the demo link (again, my server is always down... lol.. try to revisit it usual.)

n.1asphost.com/micahsantos/freescripts/asp/recordset/recordset.asp

i've based it all in your visual output, so, i came up with 3 asp files:

DB_CONN.ASP
Desc: Database Connection
----------------------------------------------------
&lt;%

Dim objConn
Dim strProvider

Sub OpenDb

strProvider = ""
strProvider = strProvider & "Provider=Microsoft.Jet.OLEDB.4.0;"
strProvider = strProvider & "Data Source=" & Server.MapPath("dbRec.mdb" & ";"
strProvider = strProvider & "Persist Security Info=False"
Set objConn = Server.CreateObject("ADODB.Connection"

objConn.Open strProvider

End Sub

Sub CloseDB
objConn.Close
Set objConn = Nothing
End Sub

%&gt;

RECORDSET.ASP
Desc: Displaying Category Title and Total Count of Events. If total count is zero, then no hyperlink
----------------------------------------------------
&lt;!--#include file="db_conn.asp"--&gt;

&lt;%

// Required DB Conn

Call OpenDB()

Dim rsCategory
Dim rsEvent


Set rsCategory = ObjConn.Execute("SELECT * FROM tblCat ORDER BY pk_EventCatId ASC"

While NOT rsCategory.EOF

Set rsEvent = objConn.Execute("SELECT Count(tblEvent.EventCatId) AS antal FROM tblEvent WHERE tblEvent.EventCatId = " & rsCategory("pk_EventCatId" & ";"

If rsEvent("antal" = 0 Then

response.write rsCategory("EventCatTitle" & " (" & rsEvent("antal" & "&lt;br&gt;"

Else

response.write "&lt;a href=events.asp?catId=" & rsCategory("pk_EventCatId" & " target='_parent'&gt;" & rsCategory("EventCatTitle" & " (" & rsEvent("antal" & "&lt;/a&gt;&lt;br&gt;"

End If

response.write rsCategory("EventCatDesc" & "&lt;br&gt;&lt;br&gt;"

rsCategory.MoveNext
Wend
rsCategory.Close
Set rsCategory = Nothing
rsEvent.Close
set rsEvent = Nothing

Call CloseDB()
%&gt;

EVENTS.ASP
Desc: Display events title
----------------------------------------------------
&lt;!--#include file="db_conn.asp"--&gt;

&lt;%

Dim rsEvents

%&gt;

&lt;%

// Required DB Conn

Call OpenDb()

Set rsEvents = ObjConn.Execute("SELECT * FROM tblEvent WHERE EventCatId = " & Request.Querystring("catId" & ""

While NOT rsEvents.EOF

response.write "Event Title: " & rsEvents("EventTitle" & "&lt;br&gt;"
response.write "Event Date: " & rsEvents("EventDate" & "&lt;br&gt;"
response.write "Event Description: " & rsEvents("EventDesc" & "&lt;br&gt;&lt;br&gt;"

rsEvents.MoveNext
WEnd
rsEvents.Close
set rsEvents = Nothing
%&gt;

I didn't use some of your variables instead. anyway, this serve as your guide to any possible solution you can derive.
any questions or suggestions, let me know okay!

Thanks so much for your help I think I've almost got it. It's working, but as it moves through the records it displays the categories and descriptions, but only shows the first record count for each category instead of looping through the count.

Any thoughts?

Thought I should possible show you the code at this point. I'm using Dreamweaver 8 so the code that's generated looks a little different than yours, but it seems to achieve the same goal.



&lt;!--#include file="../Connections/KEWired.asp" --&gt;
&lt;%
Dim rsCategory
Dim rsCategory_numRows

Set rsCategory = Server.CreateObject("ADODB.Recordset"
rsCategory.ActiveConnection = MM_KEWired_STRING
rsCategory.Source = "SELECT * FROM dbo.tblEventCategory ORDER BY dbo.tblEventCategory.PK_EventCatID ASC"
rsCategory.CursorType = 0
rsCategory.CursorLocation = 2
rsCategory.LockType = 1
rsCategory.Open()

rsCategory_numRows = 0
%&gt;
&lt;%
Dim rsEvent__varCatID
rsEvent__varCatID = "0"
If (rsCategory("PK_EventCatID" &lt;&gt; "" Then
rsEvent__varCatID = rsCategory("PK_EventCatID"
End If
%&gt;
&lt;%
Dim rsEvent
Dim rsEvent_numRows

Set rsEvent = Server.CreateObject("ADODB.Recordset"
rsEvent.ActiveConnection = MM_KEWired_STRING
rsEvent.Source = "SELECT Count(dbo.tblEvent.FK_EventCatID) AS antal FROM dbo.tblEvent WHERE dbo.tblEvent.FK_EventCatID = " + Replace(rsEvent__varCatID, "'", "''" + ""
rsEvent.CursorType = 0
rsEvent.CursorLocation = 2
rsEvent.LockType = 1
rsEvent.Open()

rsEvent_numRows = 0
%&gt;




&lt;%
While NOT rsCategory.EOF
If rsEvent("antal" = 0 Then

response.write rsCategory("EventCategory" & " (" & rsEvent("antal" & "&lt;br&gt;"

Else

response.write "&lt;a href=events.asp?catId=" & rsCategory("PK_EventCatID" & " target='_parent'&gt;" & rsCategory("EventCategory" & " (" & rsEvent("antal" & "&lt;/a&gt;&lt;br&gt;"

End If

response.write rsCategory("EventCategoryDescription" & "&lt;br&gt;&lt;br&gt;"

rsCategory.MoveNext
Wend
%&gt;

The link you displayed is totally what I need to happen, but for some reason it's not totally right I get something more like:


Spring Events (7)
These are events in the spring

Summer Events (7)
These are events in the summer

Fall Events (7)
These are events in the fall

Winter Events (7)
These are events in the winter

Other Events (7)
These are events if you live on another planet

When it should be like the previous example. It seems to only show the first records count while the categories are looping the count is not.

Thanks for all your help. I have it figured out, I had the recordset in the wrong place.