<% set conn=server.createObject("adodb.connection") conn.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\") & "\data\NothingToIt.mdb;UID=;PWD=;" if request("action") = "" or request("action") = "newsearch" then 'this means that the user has not yet requested a search, or is requesting a new search %>
<% 'this will select all series, even ones that currently offer no classes in them 'SQL="SELECT class_series_description, class_series, class_series_id FROM class_series_tbl;" 'this will select all series for which there is a class in the future... SQL = "SELECT DISTINCT [class_series_tbl].[class_series_id], [class_series_tbl].[class_series], " SQL =SQL & "[class_series_tbl].[class_series_description]" SQL=SQL & "FROM (class_series_tbl INNER JOIN class_tbl ON [class_series_tbl].[class_series_id]" SQL=SQL& " =[class_tbl].[class_series_id]) INNER JOIN class_datetime_tbl ON [class_tbl].[class_id]" SQL=SQL& " =[class_datetime_tbl].[class_id] WHERE [class_datetime_tbl].[class_datetime]>now();" SET RS=Server.CreateObject("adodb.recordset") rs.open SQL, conn response.write "" While not rs.eof response.write "" rs.movenext Wend response.write "

"&rs("class_series") &"
" &rs("class_series_description")& "

" If IsObject(RS) Then RS.Close Set RS = Nothing End IF %>

Search for classes by class series:



Search for classes by day of the week:


<% else 'Start of the Second and Third states. The else above signifies only that the user is 'not here for the first time or trying to initiate a new search 'if they are here, they get either a RESULTS set or a DETAIL set if request.querystring("action") = "detail" THEN SQL = "SELECT [class_datetime_tbl].[class_datetime_id], [class_datetime_tbl].[class_datetime], " SQL = SQL &"[class_datetime_tbl].[class_capacity], [class_datetime_tbl].[class_enrolled], " SQL = SQL &"[class_datetime_tbl].[class_status], [class_series_tbl].[class_series], " SQL = SQL &"[instructor_tbl].[instructor_name], [class_tbl].[class_photo], " SQL = SQL &"[class_tbl].[class_price], [class_tbl].[class_name], [class_tbl].[class_id]," SQL = SQL &"[class_tbl].[class_prerequisite], [class_tbl].[class_note], " SQL = SQL &"[class_tbl].[class_demo], [class_tbl].[class_new], [class_tbl].[class_description]" SQL = SQL &"FROM instructor_tbl INNER JOIN ((class_series_tbl INNER JOIN class_tbl ON " SQL = SQL &"[class_series_tbl].[class_series_id] =[class_tbl].[class_series_id]) INNER JOIN " SQL = SQL &"class_datetime_tbl ON [class_tbl].[class_id] =[class_datetime_tbl].[class_id]) " SQL = SQL &"ON [instructor_tbl].[instructor_id] =[class_datetime_tbl].[instructor_id] WHERE " SQL = SQL &"[class_datetime_tbl].[class_datetime_id] = " & request.querystring("record") &";" SET RS = Server.CreateObject("ADODB.recordset") RS.open SQL, CONN, 2 'Set up all of the boolean states.. 'New or not, Sold out or Not, Demonstration or not if rs("class_new") = TRUE then varNewClass = "New!" else varNewClass = "" end if if rs("class_demo") = TRUE then varDemoClass = "Demonstration" else varDemoClass = "Hands-On" end if if rs("class_status") = TRUE then varStatusClass = "THIS CLASS IS SOLD OUT" else varStatusClass = "THERE ARE STILL A FEW OPENINGS LEFT IN THIS CLASS. CALL US AT 775-826-2628 TO SIGN UP TODAY!" end if if rs("class_datetime") < NOW() then varStatusClass = "THIS CLASS HAS ALREADY COME AND GONE! CHECK BACK NEXT SEMESTER." else ' okay the class is in the future... release = cdate("5/22/2004 5:01:00 PM") end if if isNull(rs("class_prerequisite")) THEN varPreClass = "None" else varPreClass = rs("class_prerequisite") end if if LEN(rs("class_photo")) < 2 OR isNull(rs("class_photo")) THEN randomize randomValue = INT((7* RND) +1) varPicClass = "" else varPicClass = "" end if %>
<%=varNewClass%> <%=UCASE(rs("class_name"))%>
<%=formatdatetime(rs("class_datetime"),1) & " " & trimseconds(formatdatetime(rs("class_datetime"),3))%> <% if rs("class_datetime") > NOW() then 'is it in the future? if rs("class_status") = FALSE then 'is it sold out? '****************** 'test the class month semester against the current one if month(rs("class_datetime")) < 7 then 'belongs to the early semester classSemester = 1 else ' it must belong to the July or later semester classSemester = 2 end if 'test the current semester if month(now) < 7 then nowsemester = 1 else nowsemester = 2 end if if nowsemester = classsemester then 'class is of current semester ' signup always okay in this condition response.write "   " else 'class is of different semester, see if we've passed the signup date... if (now) > release then 'we must be in the interim week after signup day and before the new semester 'signup okay response.write "   " else'this class is for next semester, but we have not gotten to class signup day yet varStatusClass = "THIS CLASS IS NOT AVAILABLE FOR REGISTRATION UNTIL CLASS SIGNUP DAY ON "&formatDateTime(release,1)&". Come to Nothing To It! between 10 and 5 on signup day, or check the website immediately afterward to register." end if end if '****************** 'response.write "   " end if end if %>
<%=varStatusClass%>
<% if LEN(rs("class_note")) > 1 Then %> <% else %> <% end if%>
Price: <%=formatCurrency(rs("class_price"))%>
Type: <%=varDemoClass%>
Instructor: <%=rs("instructor_name")%>
Series: <%=rs("class_series")%>
Prerequisite: <%=varPreClass%>
 
Note: <%=rs("class_note")%> 
<%=varPicClass%>
DESCRIPTION: <%=rs("class_description")%>  
Have a question? Contact Us by Email
<% If IsObject(RS) Then RS.Close Set RS = Nothing End IF ELSE 'This area handles the two types of return screens (day of the week or by series) Response.write "
The Following Classes Match Your Request. Click the Class Name for Details. Re-sort the table by clicking the column headings.
" if request("action") = "byDOTW" then 'Handle the display by day of the week dayOTW = request("DOTW") if request.form("sort") <> "" then sortorderby = request.form("sort") else sortorderby = request.querystring("sort") end if 'SQL gets back all entries, VBS will sort out days of the week... SQL = "SELECT [class_datetime_tbl].[class_datetime], [class_datetime_tbl].[class_status], [class_datetime_tbl].[class_datetime_id], [class_tbl].[class_name], [class_tbl].[class_price], [class_tbl].[class_new]" SQL = SQL & " FROM class_tbl INNER JOIN class_datetime_tbl ON [class_tbl].[class_id] =[class_datetime_tbl].[class_id] WHERE [class_datetime_tbl].[class_datetime] >= (NOW()-14)" SELECT CASE lcase(sortOrderBy) CASE "" SQL = SQL & " ORDER BY class_tbl.class_name, class_datetime_tbl.class_datetime;" CASE "name" SQL = SQL & " ORDER BY class_tbl.class_name, class_datetime_tbl.class_datetime;" CASE "date" SQL = SQL & " ORDER BY class_datetime_tbl.class_datetime, class_tbl.class_name;" CASE "price" SQL = SQL & " ORDER BY class_tbl.class_price, class_datetime_tbl.class_datetime;" End SELECT SET RS = Server.CreateObject("ADODB.recordset") RS.open SQL, CONN, 2 dayOTWcount = 0 'Counts the number of matches so we can determine if there are no matches Response.write "" Response.write "" rowColor = 1'alternates the returned row colors While not RS.EOF if rs("class_new") = TRUE THEN varNewClass = "New!" else varNewClass = " " end if if rs("class_status") = TRUE THEN varStatusClass = "SOLD OUT" else varStatusClass = "Available" end if dayOTW = telldayofweek(rs("class_datetime")) If dayOTW = request("DOTW") THEN 'Write the records in a table... dayOTWcount = dayOTWcount + 1 'alternate the row colors if rowColor < 2 then varRowColor = "98C9FF" rowColor = rowColor + 1 else varRowColor = "#ffffff" rowColor = rowColor - 1 end if response.write "" else 'Hide the record from the user by doing nothing end if rs.movenext Wend 'take into consideration that there may have been no matches for that day of the week IF dayOTWcount = 0 then dayOTW = request.form("DOTW") response.write "" end if response.write "
 Class NameDate and TimePriceStatus
"&varNewClass&""&rs("class_name")&""&dayOTW&", "&trimseconds(rs("class_datetime"))&""&formatcurrency(rs("class_price"))&""&varStatusClass&"
Sorry, there are no classes available this semester on " & dayOTW & "s.
" If IsObject(RS) Then RS.Close Set RS = Nothing End IF else'***********************else between DOTW results or bySeries results****************** '******************by series results start here...***************************************** if request.form("series") <> "" then seriesresults = request.form("series") else seriesresults = request.querystring("series") end if if request.form("sort") <> "" then sortorderby = request.form("sort") else sortorderby = request.querystring("sort") end if SQL = "SELECT class_datetime_tbl.class_datetime, class_datetime_tbl.class_status, " SQL = SQL & "class_datetime_tbl.class_datetime_id, class_tbl.class_id, class_tbl.class_name, " SQL = SQL & "class_tbl.class_price, class_tbl.class_new FROM (class_series_tbl INNER JOIN " SQL = SQL & "class_tbl ON class_series_tbl.class_series_id = class_tbl.class_series_id) " SQL = SQL & "INNER JOIN class_datetime_tbl ON class_tbl.class_id = class_datetime_tbl.class_id " SQL = SQL & "WHERE ([class_series_tbl].[class_series_id] = "& seriesresults &") AND ([class_datetime_tbl].[class_datetime] >= (NOW()-14) )" SELECT CASE lcase(sortOrderBy) CASE "" SQL = SQL & " ORDER BY class_tbl.class_name, class_datetime_tbl.class_datetime;" CASE "name" SQL = SQL & " ORDER BY class_tbl.class_name, class_datetime_tbl.class_datetime;" CASE "date" SQL = SQL & " ORDER BY class_datetime_tbl.class_datetime, class_tbl.class_name;" CASE "price" SQL = SQL & " ORDER BY class_tbl.class_price, class_datetime_tbl.class_datetime;" End SELECT SET RS = Server.CreateObject("ADODB.recordset") RS.open SQL, CONN, 2 'take car of the booleans...classStatus and classNew Response.write "" Response.write "" rowColor=1 if RS.eof then response.write "" end if While not RS.EOF if rs("class_new") = TRUE THEN varNewClass = "New!" else varNewClass = " " end if if rs("class_dateTime") > NOW() THEN if rs("class_status") = TRUE THEN varStatusClass = "SOLD OUT" else varStatusClass = "Available" end if else varStatusClass = "Past!" end if 'alternate the row colors if rowColor < 2 then varRowColor = "#98C9FF" rowColor = rowColor + 1 else varRowColor = "#ffffff" rowColor = rowColor - 1 end if dayOTW = telldayofweek(rs("class_datetime")) if LEN(rs("class_name")) > 30 THEN classname = LEFT(rs("class_name"),27)&"..." else classname = rs("class_name") end if response.write "" rs.movenext Wend response.write "
 Class NameDate and TimePriceStatus
There are presently no classes in this series
"&varNewClass&""&classname&""&dayOTW&", "&trimseconds(rs("class_datetime"))&""&formatcurrency(rs("class_price"))&""&varStatusClass&"
" If IsObject(RS) Then RS.Close Set RS = Nothing End IF End if'end cooking between the two kinds of returns DOTW and bySeries End if'ends the choice between detail screets and return screens END IF'ends the case of presenting a form or either the return or detail recordsets Conn.Close Set Conn = Nothing %>