Help showing multiple categories [Archive]

Click to See Complete Forum and Search --> : Help showing multiple categories

jrthor2

07-18-2003, 07:38 AM

I have a schedules page on my site, that when a user selects a certain schedule, it displays that schedule. How would I change my code below to allow it there is no category selected, it would show all the categories each in its own table?
An example of how I want it to look is here Example (http://www.zluth.org/schedules/) . I have this working, but I have 7 different select statements on the page, and thought it might be better to use just 1 if possible.

My Code:

<%
Dim rs
Dim conn
Dim SQLstmt
Dim DSNtemp

Set conn = server.createobject("adodb.connection")
DSNtemp="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/db/schedules.mdb")
conn.Open DSNtemp

today = Month(Date) & "/" & Day(Date) & "/" & Year(Date)
cat_show = Request.QueryString("category")

If (cat_show) <> "" Then
SQLstmt = "SELECT * from Schedules where CATEGORY = '" & cat_show & "' and begin_dte <= #" & today & "# and end_dte >= #" & today & "# order by begin_dte desc, [time] desc"
Else
SQLstmt = "SELECT * from Schedules where begin_dte <= #" & today & "# and end_dte >= #" & today & "# order by begin_dte desc, [time] desc"
End If

'Response.Write(SQLstmt)
Set rs = conn.execute(SQLstmt)
%>
<html>
<head>
<title>Zion Evangelical Lutheran Church - Schedules</title>

</head>

<body <% Response.Write(bodytag) %>>
<table width="100%" border="0" cellspacing="0" cellpadding="2">
<tr>
<td rowspan="2" valign="top">

</td>
<td width="100%" valign="top">

</td>
</tr>
<tr>
<td width="100%" valign="top" height="450">
<% if NOT rs.EOF Then %>
<TABLE width="70%" cellSpacing=0 cellPadding=0 border=0>
<TBODY>
<TD noWrap bgColor=330099 height=15 rowSpan=2>
<FONT face=verdana
color=white size=-2>  <B><%=cat_show%> Schedule</B></FONT></TD>
<TD height="15" vAlign=top><IMG height=15 alt=""
src="/images/tr14x15_1.gif" width=14></TD>
</TR>
</TBODY>
</TABLE>
<table width="100%" border="1" cellspacing="0" cellpadding="5" bordercolor="330099" bgcolor="#FFFFFF">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="0" align="center" width="100%">
<tr>
<td width="33%"><u><strong>Date</strong></u></td>
<td width="33%"><u><strong>Time</strong></u></td>
<td width="33%"><u><strong>Name</strong></u></td>
</tr>
<%
x=0
Dim iRecordCount,mycolor
iRecordCount = 0
mycolor = "#ffffff"
Do while NOT rs.EOF
If mycolor = "#ffffff" Then
mycolor = "#d7d7ff"
Else
mycolor = "#ffffff"
End If
id = rs("ID")
begin_dte = rs("BEGIN_DTE")
end_dte = rs("END_DTE")
schedule_dte = rs("SCHEDULE_DTE")
category = rs("CATEGORY")
strtime = rs("TIME")
strname = rs("NAME")
%>
<tr bgcolor="<%=mycolor%>">
<td valign="top">
<%
Response.Write kaos_date("%N %d%s, %Y", schedule_dte)
%>
</td>
<td valign="top"><%=strtime%></td>
<td valign="top"><%=strname%></td>
</tr>
<%
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set conn = Nothing
Set SQLstmt = Nothing

Else
Response.Write ("No " & category & " Schedules available")
%>
<% End if %>
</table>
</td>
</tr>
</table>
<br>
</td>
</tr>
</table>

<tr>
<td colspan="2">

</td>
</tr>

</body>
</html>

Helj

07-18-2003, 10:15 AM

rs.Filter = "Category = '" & myCategory & "'"

jrthor2

07-18-2003, 10:17 AM

Could you explain a little moer? Where to put that, etc. I'm a little new to asp.

jrthor2

07-19-2003, 08:00 PM

Well, I got it to work like this, but when I don't have a category, and display the results, it is not displaying them as each category in it's own seperate table as the link to my example shows. Below is the code for my entire page.

<%
Dim rs
Dim conn
Dim SQLstmt
Dim DSNtemp

Set conn = server.createobject("adodb.connection")
DSNtemp="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("/db/schedules.mdb")
conn.Open DSNtemp

today = Month(Date) & "/" & Day(Date) & "/" & Year(Date)
cat_show = Request.QueryString("category")

If (cat_show) <> "" Then
SQLstmt = "SELECT * from Schedules where CATEGORY = '" & cat_show & "' and begin_dte <= #" & today & "# and end_dte >= #" & today & "# order by begin_dte desc, [time] desc"
Else
SQLstmt = "SELECT * from Schedules where begin_dte <= #" & today & "# and end_dte >= #" & today & "# order by begin_dte desc, [time] desc"
End If

'Response.Write(SQLstmt)
Set rs = conn.execute(SQLstmt)
%>
<html>
<head>
<title>Zion Evangelical Lutheran Church - Schedules</title>

</head>

<body <% Response.Write(bodytag) %>>
<table width="100%" border="0" cellspacing="0" cellpadding="2">
<tr>
<td rowspan="2" valign="top">

</td>
<td width="100%" valign="top">

</td>
</tr>
<tr>
<td width="100%" valign="top" height="450">
<% if NOT rs.EOF Then %>
<TABLE width="70%" cellSpacing=0 cellPadding=0 border=0>
<TBODY>
<TD noWrap bgColor=330099 height=15 rowSpan=2>
<FONT face=verdana
color=white size=-2>  <B><%=cat_show%> Schedule</B></FONT></TD>
<TD height="15" vAlign=top><IMG height=15 alt=""
src="/images/tr14x15_1.gif" width=14></TD>
</TR>
</TBODY>
</TABLE>
<table width="100%" border="1" cellspacing="0" cellpadding="5" bordercolor="330099" bgcolor="#FFFFFF">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="0" align="center" width="100%">
<tr>
<td width="33%"><u><strong>Date</strong></u></td>
<td width="33%"><u><strong>Time</strong></u></td>
<td width="33%"><u><strong>Name</strong></u></td>
</tr>
<%
x=0
Dim iRecordCount,mycolor
iRecordCount = 0
mycolor = "#ffffff"
Do while NOT rs.EOF
If mycolor = "#ffffff" Then
mycolor = "#d7d7ff"
Else
mycolor = "#ffffff"
End If
id = rs("ID")
begin_dte = rs("BEGIN_DTE")
end_dte = rs("END_DTE")
schedule_dte = rs("SCHEDULE_DTE")
category = rs("CATEGORY")
strtime = rs("TIME")
strname = rs("NAME")
%>
<tr bgcolor="<%=mycolor%>">
<td valign="top">
<%
Response.Write kaos_date("%N %d%s, %Y", schedule_dte)
%>
</td>
<td valign="top"><%=strtime%></td>
<td valign="top"><%=strname%></td>
</tr>
<%
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set conn = Nothing
Set SQLstmt = Nothing

Else
Response.Write ("No " & category & " Schedules available")
%>
<% End if %>
</table>
</td>
</tr>
</table>
<br>
</td>
</tr>
</table>

<tr>
<td colspan="2">

</td>
</tr>

</body>
</html>

jrthor2

07-20-2003, 06:26 AM

If you go to this page example (http://www.zluth.org/schedules/) and see how the tables are split up accordingto categories, that's how I want it to display. Instead, it is displaying the results like this example2 (http://www.zluth.org/schedules/index2.asp) .

It is lumping all the schedules into 1 table and not displaying them per category.

jrthor2

07-20-2003, 08:19 AM

Could you show me how to do the 2 sub routines? Not quite following you.

jrthor2

07-21-2003, 08:08 AM

Ok, getting close. When there is no category selected, it displays the firs table correct, but then it creates a table for each row it pulls back, here is what it looks like (http://www.zluth.org/schedules/index3.asp)

Here is my modified code:

<% @Language=VBScript %>
<% Option Explicit %>
<%
Dim conn, rs, SQLstmt, db, DSNtemp, bodytag
Dim today, cat_show, iRecordCount, mycolor
Dim id, begin_dte, end_dte, schedule_dte, category, strtime, strname
db = "/db/schedules.mdb"
%>


<%

today = Month(Date) & "/" & Day(Date) & "/" & Year(Date)
cat_show = Request.QueryString("category")

SQLstmt = "SELECT * from Schedules where "
If cat_show <> "" Then
SQLstmt = SQLstmt & ("CATEGORY = '" & cat_show & "' and ")
End If
SQLstmt = SQLstmt & ("begin_dte <= #" & today & "# and end_dte >= #" & today & "# ")
SQLstmt = SQLstmt & ("order by schedule_dte desc, [time] desc")

'Response.Write(SQLstmt)
Set rs = conn.execute(SQLstmt)
%>
<html>
<head>
<title>Zion Evangelical Lutheran Church - Schedules</title>

</head>

<body <% Response.Write(bodytag) %>>
<table width="100%" border="0" cellspacing="0" cellpadding="2">
<tr>
<td rowspan="2" valign="top">

</td>
<td width="100%" valign="top">

</td>
</tr>
<tr>
<td width="100%" valign="top" height="450">
<% Sub StartCategory(cat) %>
<TABLE width="70%" cellSpacing=0 cellPadding=0 border=0>
<TBODY>
<TD noWrap bgColor=330099 height=15 rowSpan=2>
<FONT face=verdana
color=white size=-2>  <B><%=cat%> Schedule</B></FONT></TD>
<TD height="15" vAlign=top><IMG height=15 alt=""
src="/images/tr14x15_1.gif" width=14></TD>
</TR>
</TBODY>
</TABLE>
<table width="100%" border="1" cellspacing="0" cellpadding="5" bordercolor="330099" bgcolor="#FFFFFF">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="0" align="center" width="100%">
<tr>
<td width="33%"><u><strong>Date</strong></u></td>
<td width="33%"><u><strong>Time</strong></u></td>
<td width="33%"><u><strong>Name</strong></u></td>
</tr>
<% End Sub
Sub EndCategory() %>
</table>
</td>
</tr>
</table>
<br>
<% End Sub
'
If NOT rs.EOF Then
cat_show = rs("CATEGORY").Value
Call StartCategory(cat_show)
iRecordCount = 0
mycolor = "#ffffff"
Do while NOT rs.EOF
If mycolor = "#ffffff" Then
mycolor = "#d7d7ff"
Else
mycolor = "#ffffff"
End If
id = rs("ID").Value
begin_dte = rs("BEGIN_DTE").Value
end_dte = rs("END_DTE").Value
schedule_dte = rs("SCHEDULE_DTE").Value
category = rs("CATEGORY").Value
strtime = rs("TIME").Value
strname = rs("NAME").Value
if category <> cat_show Then
Call EndCategory()
cat_show = category
Call StartCategory(cat_show)
End If
%>
<tr bgcolor="<%=mycolor%>">
<td valign="top">
<%
Response.Write kaos_date("%N %d%s, %Y", schedule_dte)
%>
</td>
<td valign="top"><%=strtime%></td>
<td valign="top"><%=strname%></td>
</tr>
<%
rs.MoveNext
Loop
Call EndCategory()
Else
Response.Write ("No " & category & " Schedules available")
End If
'
rs.Close
Set rs = Nothing
Set conn = Nothing
Set SQLstmt = Nothing
%>
<br>
</td>
</tr>
</table>

<tr>
<td colspan="2">

</td>
</tr>

</body>
</html>

jrthor2

07-21-2003, 08:32 AM

I've tried, but no matter where I put that If, it displays the same results

jrthor2

07-21-2003, 01:50 PM

I got this fixed by adding order by category to my sql statement.