<%
'*******************************************************
'* *
'* ASP code based on sample code from: *
'* ASP 101 Sample Code - http://www.asp101.com *
'* *
'* JavaScript code based on sample code from: *
'* http://www.cs.wpi.edu/~kal/elecdoc/johnmc/ *
'* Jack McCrorey's WPI Electronics Document class *
'* Final Project *
'* *
'* This code was customized for the use by the *
'* St. Bonaventure School Star Reader activity *
'* by Mike Novotny. *
'* *
'*******************************************************
%>
Star Reader Books
<%
Dim DataConn, cmdDC, rsDC
Dim strSortBy, strOrder, strSearch, strSearchField, strSearch2
Dim strlLevel, strhLevel
Dim Update_Date
' Retrieve QueryString Variables and convert them to a usable form
strSortBy = Request.QueryString("sortby")
IF strSortBy = "" THEN strSortBy = "Title"
IF strSortBy = "Level" THEN strSortBy = "Book_Level"
strOrder = Request.QueryString("order")
IF strOrder <> " DESC" THEN strOrder=""
strSearch = Request.QueryString("SearchString")
strSearch2 = REPLACE (strSearch, "'", "''")
strSearchField = Request.QueryString("SearchField")
IF strSearchField = "" OR strSearchField = "Level" THEN strSearchField = "Book_Level"
strlLevel = Request.QueryString("LowLevel")
strhLevel = Request.QueryString("HighLevel")
IF LEN(strlLevel) = 0 OR LEN(strhLevel) = 0 THEN
strlLevel = ""
strhLevel = ""
END IF
%>
<% IF LEN(strSearch) > 2 OR strlLevel <> "" THEN %>
<% IF StrlLevel <> "" THEN %>
<% IF StrlLevel = "0" THEN %>
Books Less Than Level 1
<% ELSE %>
<% IF StrlLevel = "13" THEN %>
Books Greater Than Level 12
<% ELSE %>
Level <%=strlLevel%> Book List
<% END IF %>
<% END IF %>
<% ELSE %>
Results for <%=strSearchField%> search of "<%=strSearch%>", sorted by <%=strSortBy%>.
<% END IF %>
<%
' Create and establish data connection
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.ConnectionTimeout = 15
DataConn.CommandTimeout = 30
'Access connection code
DataConn.Open "DBQ=" & Server.MapPath("documents\star.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;", "username", "password"
' Create and link command object to data connection then set attributes and SQL query
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConn
cmdDC.CommandType = 1
' Get the last update date from the database
cmdDC.CommandText = "SELECT Date FROM Last_Update;"
Set rsDC = Server.CreateObject("ADODB.Recordset")
rsDC.Open cmdDC, , 0, 1
Update_Date = rsDC.Fields("Date")
rsDC.Close
Set rsDC = Nothing
' Perform database search, build table with results
IF StrlLevel <> "" THEN
cmdDC.CommandText = "SELECT * FROM tests WHERE ( Book_Level < (" & strhLevel & " ) AND Book_Level >= " & strlLevel & " ) Order by Book_Level, Author, Title;"
ELSE
cmdDC.CommandText = "SELECT * FROM tests WHERE (" & strSearchField & " Like '%" & strSearch2 & "%' ) ORDER BY " & strSortBy & strOrder & ";"
END IF
' Create recordset and retrieve values using command object
Set rsDC = Server.CreateObject("ADODB.Recordset")
' Opening record set with a forward-only cursor (the 0) and in read-only mode (the 1)
rsDC.Open cmdDC, , 0, 1
%>
Test Number
Book Title
Author
Book Level
Point Value
<%
' Loop through recordset and display results
If Not rsDC.EOF Then rsDC.MoveFirst
Do While Not rsDC.EOF
%>
<%= rsDC.Fields(1) %>
<%= rsDC.Fields(2) %>
<%= rsDC.Fields(3) %>
<%= FormatNumber( rsDC.Fields(4), 1)%>
<%= FormatNumber( rsDC.Fields(5), 1)%>
<%
rsDC.MoveNext
Loop
%>
<%
' Close Data Access Objects and free DB variables
rsDC.Close
Set rsDC = Nothing
' can't do a "cmdDC.Close" !
Set cmdDC = Nothing
DataConn.Close
Set DataConn = Nothing
%>