CD Trustee On The Web
ASP Scripts Sample Code
One of my biggest pet peeves is not being able to view at least some sample code prior to
downloading it. That's why I'm giving you an opportunity to see a small portion of what you'll be
getting when you download my scripts. The following are all the files that went into building my
New Arrivals page.
NewArrivals.asp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>This Month's New Arrivals</title>
<link rel="stylesheet" href="/Styles.css" type="text/css">
<meta name="description" content="">
<meta name="keywords" content="">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="MSSmartTagsPreventParsing" content="true">
<meta name="robots" content="">
<body>
<%
' BEGIN USER CONSTANTS
Dim CONN_STRNG
'Dim CONN_USER
'Dim CONN_PASS
' To use a DSN, the format is shown on the next line:
'CONN_STRNG = "DSN=CDTrust;"
CONN_STRNG = "DBQ=" & Server.MapPath("/db/CDTrust.mdb") & ";"
CONN_STRNG = CONN_STRNG & "Driver={Microsoft Access Driver (*.mdb)};"
' If you're using a DSN-less connection, be sure to specify the directory and
' filename where your CD Trustee database is stored on your web server
' If your DB is secured, you need to specify values here
CONN_USER = ""
CONN_PASS = ""
' END USER CONSTANTS
' Get parameters
TableTitle = "New Arrivals"
SelectDate = Date - 30
varWhere = "DateAdded > #" & SelectDate & "# "
VarURL = "NewArrivals.asp"
%>
<!--#include file="ArtistAlbumFormat.ssi" -->
</body>
</html>
ArtistAlbumFormat.ssi
<%
' Declare our variables
Dim iPageSize 'How big our pages are
Dim iPageCount 'The number of pages we get back
Dim strSQL 'SQL command to execute
Dim oRS 'The ADODB recordset object
Dim iRecordsShown 'Loop controller for displaying just iPageSize
'records
Dim I 'Standard looping var
Dim Page_Prev 'Starting page link
Dim Page_Next 'Ending page link
Dim Row_Start 'Starting row for this page
Dim Row_End 'Ending row for this page
Dim RowColor 'CSS class to use for this row
Dim TableTitle 'Title for this page
Dim varWhere 'Selection criteria for the SQL statement
' Number of records to display on each page. Note that you can set
' your page size to any amount you want. Bear in mind as you select
' this value that the larger the amount, the longer the time will be
' for your web page to load.
iPageSize = 20
' Retrieve page to show or default to 1
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
strSQL = "SELECT tblAlbums.Title AS title, "
strSQL = strSQL & "tblAlbums.TitleSort, "
strSQL = strSQL & "tblAlbums.MediaType AS format, "
strSQL = strSQL & "tblAlbums.AlbumID AS albumid, "
strSQL = strSQL & "tblAlbums.DateAdded as DateAdded, "
strSQL = strSQL & "tblAlbums.Genre AS genre, "
strSQL = strSQL & "tblAlbums.UserField1 as userfield1, "
strSQL = strSQL & "tblArtists.The AS artist_the, "
strSQL = strSQL & "tblArtists.Artist AS artist, "
strSQL = strSQL & "tblArtists.ArtistID as artistid, "
strSQL = strSQL & "tblArtists.SortName AS sortname "
strSQL = strSQL & "FROM tblArtists "
strSQL = strSQL & "INNER JOIN tblAlbums ON tblArtists.ArtistID = "
strSQL = strSQL & "tblAlbums.ArtistID "
strSQL = strSQL & "WHERE " & varWhere
strSQL = strSQL & "ORDER BY tblArtists.SortName, "
strSQL = strSQL & "tblAlbums.TitleSort, tblAlbums.MediaType;"
%>
<!--#include file="OpenRS.ssi" -->
<%
' If the request page falls outside the acceptable range,
' give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1
' Check page count to prevent bombing when zero results are returned!
If iPageCount > 0 Then
' Move to the selected page
oRS.AbsolutePage = iPageCurrent
End If
%>
<br><p class="title"><%=PageTitle%></p>
<center>
<table border="2" rules="cols" frame="vsides" bordercolor="#000000"
style="border-bottom: 2px solid" cellspacing="0" width="90%">
<tr>
<td class="toprow" colspan="4" valign="middle"><%=TableTitle %></td>
</tr>
<tr>
<td class="row2left" width="34%">Artist Name</td>
<td class="row2mid" width="58%">Album Title</td>
<td class="row2right" width="8%">Format</td>
</tr>
<%
'Write a No Records Found message for an empty recordset
If iPageCount = 0 Then
%>
<tr><td class="color1" colspan="4">No Records Found</td></tr></table>
<%
Else
' Loop through our records and ouput 1 row per record
iRecordsShown = 0
Do While iRecordsShown < iPageSize And Not oRS.EOF
If iRecordsShown mod 2 = 0 Then
RowColor = "color1"
Else
RowColor = "color2"
End If
%>
<tr>
<td class="<%=RowColor %>" width="34%" valign="middle">
<a href="MusicDBSearch.asp?BrowseArtistID=<%=oRS("artistid") %>"
title="Search By Artist - <%=oRS("artist_the") & oRS("artist")%>">
<%=oRS("artist_the") & " " & oRS("artist") %></a>
</td>
<td class="<%=RowColor %>" width="58%" valign="middle">
<a href=TrackList.asp?AlbumID=<%=oRS("albumid") %>
title="Track List: <%=oRS("artist_the") & " " & oRS("artist") & " - " & oRS("title")%>">
<%=oRS("title") %></a>
</td>
<td class="<%=RowColor %>" width="8%" valign="middle">
<%=oRS("format")%>
</td>
</tr>
<%
' Increment the number of records we've shown
iRecordsShown = iRecordsShown + 1
oRS.MoveNext
Loop
' Close DB objects and free variables
oRS.Close
Set oRS = Nothing
%>
</table>
<%
If iPageCount > 1 Then ' Write a table with all the page links
%>
<table border="2" cellspacing="0" width="90%" bordercolor="#FFFFFF">
<tr>
<td width="8%" bordercolor="#FFFFFF" valign="left">
<%
' Show "previous" and "next" page links which pass the page
' to view and any parameters needed to rebuild the query.
' You could just as easily use a form but you'll need to
' change the lines that read the info back in at the top of
' the script.
If iPageCurrent > 1 Then
Page_Prev = iPageCurrent - 1
%>
<a href="<%=varURL %>?page=<%=Page_Prev %>" title="Previous Page"><<Prev
<%
End If
%>
</a></td>
<td width="84%" align="center">Page <%=iPageCurrent %> of
<%=iPageCount %><br>
<%
'Show starting and ending record numbers for this page
Row_Start = iPageCurrent * iPageSize - iPageSize + 1
If iPageCurrent = iPageCount Then
Row_End = Row_Start + iRecordsShown - 1
Else
Row_End = iPageCurrent * iPageSize
End If
%>
Records <%=Row_Start%> - <%=Row_End%> of <%=iRecordCount %></td>
<td valign="right">
<%
If iPageCurrent < iPageCount Then
Page_Next = iPageCurrent + 1
%>
<a href="<%=varURL %>?page=<%=Page_Next %>" title="Next Page">Next>></a>
<%
End If
%>
</a></td>
</tr>
</table>
<form action="<%=varURL %>?page=<%=page %>" method="get">
<input type="submit" value="Go To Page">
<input type="hidden" size="4" name="artistbrowse"
value ="<%=varArtistBrowse %>">
<input type="text" size="4" name="page" value="">
</form>
<%
End If
End If
%>
OpenRS.ssi
<%
' Create and open our connection
Set objPagingConn = Server.CreateObject("ADODB.Connection")
objPagingConn.Open CONN_STRNG
' Create recordset and set the page size
Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.CursorType=3 'Static cursor
oRS.LockType=3 ' Optimistic lock
oRS.PageSize = iPageSize
' Open RS
oRS.Open strSQL, objPagingConn
' Get the count of the pages using the given page size
iPageCount = oRS.PageCount
' Get the count of records in the record set
iRecordCount = oRS.RecordCount
' Retrieve page to show or default to 1
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
%>
Website hosted by WebHost4Life
Accessibility