%@ LANGUAGE="VBSCRIPT" %>
<%
letter = request("letter")
alphabet = "0-9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z"
alphaary = split(alphabet,",")
%>
Collectorsshop.com CD Catalog
|
Main Page .. View
Cart .. E-mail .. Contact
Us
Main CD Catalog:
<%= letter %>
|
Please choose which page of the catalog you would like to view by pressing
the letters below
<%
anchrow = ""
for i = 0 to ubound(alphaary)
anchrow = anchrow & "" & _
alphaary(i) & " "
next
response.write anchrow
%>
|
<%
' Page constraints:
Const PAGE_SIZE = 1000 ' The number of displayed entries per page.
' Declare main variables
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.
Dim cnnSearch ' ADO connection
Dim rsSearch ' ADO recordset
Dim strDBPath ' path to the collectors shop Access database (*.mdb) file
Dim strSQL ' The Artist Letter SQL Query
Dim Search ' The letter string being looked for
Dim iPageCurrent ' The Artist page they're currently on
Dim iPageCount ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I ' Looping variable
' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")
' Retreive the term being searched for. I used
' the QS so people can bookmark results.
Search = Request.QueryString("letter")
' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
%>
<%
If letter <> "" Then
strDBPath = Server.MapPath("../Database/collectorsshop-webstore1.mdb")
' Create an ADO Connection to connect to the collectorsshop database.
Set cnnSearch = Server.CreateObject("ADODB.Connection")
' The Collectorsshop database:
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
' Building the query based on the value entered.
'this is for the artist search page
' bill 2/17/2002
' strSQL = "SELECT itemid,Artist, Album, Description, Price, Format" _
' & " FROM items " _
' & " WHERE (Artist LIKE '" & letter & "%' )" _
' & " AND (Newrelease =0)" _
' & " AND (DVD =0)" _
' & " ORDER BY Artist;"
letterWhere = " WHERE (Artist LIKE '" & letter & "%' )"
if letter = "0-9" then
letterWhere = " WHERE (left(artist,1) >= '0' and left(artist,1) <= '9')"
end if
strSQL = "SELECT itemid,Artist, Album, Description, Price, Format" _
& " FROM items " _
& letterWhere _
& " AND (Newrelease =0)" _
& " AND (DVD =0)" _
& " ORDER BY Artist;"
'response.write letter
'response.end
' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.PageSize = PAGE_SIZE
rsSearch.CacheSize = PAGE_SIZE
' Open our recordset
rsSearch.Open strSQL, cnnSearch, adOpenStatic, adLockReadOnly, adCmdText
' Get a count of the number of records and pages
' for use in building the header and footer text.
iRecordCount = rsSearch.RecordCount
iPageCount = rsSearch.PageCount
If iRecordCount = 0 Then
' Display no records error.
%>
No records found. Please try again.
<%
Else
' Move to the page we need to show.
rsSearch.AbsolutePage = iPageCurrent
' Show a quick status line letting people know where they are:
%>
<%= iRecordCount %> Records Found.
<%
' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
' For fun I'm combining some fields and showwing you can do more then
' just spit out the data in the form it is in in the table.
%>
Artist |
Title |
Format |
Price |
<%
Do While Not rsSearch.EOF And rsSearch.AbsolutePage = iPageCurrent
orderbutton = "" & _
"Order "
%>
<%= rsSearch.Fields("Artist").Value %> |
<%= rsSearch.Fields("Album").Value %> |
<%= rsSearch.Fields("Format").Value %> |
$<%= rsSearch.Fields("Price").Value %>.00 <%=orderbutton%> |
<%= rsSearch.Fields("Description").Value %> | |
|
<%
rsSearch.MoveNext
Loop
%>
Top
|
<%
' Now we need to show our navigation links:
' Show "previous" and "next" page links which pass the page to
' view our search parameter. You could also use form buttons
' but I find this looks better.
If iPageCurrent > 1 Then
%>
[<< Prev]
<%
End If
' You can also show page numbers:
For I = 1 To iPageCount
If I = iPageCurrent Then
%>
<%= I %>
<%
Else
%>
<%= I %>
<%
End If
Next 'I
If iPageCurrent < iPageCount Then
%>
[Next >>]
<%
End If
%>
<%
End If
' Close our recordset and connection and dispose of the objects
rsSearch.Close
Set rsSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If
%>
|