3Back to ASP Examples Menu
3Back to Database Selection
3Back to Database Interface Wizard Home Page
4This script cobbled together by Lil Peck

Displaying All of the Form Variables - www.4guysfromrolla.com
Use of ASCII characters to replace reserved ASP characters
Simple database connection & display from ASP 101
Paging Function by Shannon Harmon on Planet Source Code

If you find this database interface generator wizard to be useful, please rate it or send me an email.

4Download this script generator set3NOTE: copy updated create_list_view.asp from here.

Here is your wizard-generated asp database display script.

'save as _list.asp for list of records and save as viewrecord.asp for single record

Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.addHeader "cache-control", "no-cache"
Response.addHeader "cache-control", "no-store"
Response.CacheControl = "no-cache"
'save as _list.asp for list of records and save as view.asp for single record
Dim conn ' ADO connection
Dim rs ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file

'dim CursorType,adOpenStatic
strDBPath = Server.MapPath("")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

Set rsCount = conn.Execute("SELECT count() AS myTotal FROM ")

' BEGIN RUNTIME CODE - paging function from http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=6875&lngWId=4
' Declare our vars
Dim iPageSize 'How big our pages are
Dim iPageCount 'The number of pages we get back
Dim iPageCurrent 'The page we want to show
Dim strOrderBy 'A fake parameter used to illustrate passing them
Dim strSQL 'SQL command to execute
'Dim conn 'The ADODB connection object
Dim objPagingRS 'The ADODB recordset object
Dim iRecordsShown 'Loop controller for displaying just iPageSize records
Dim I 'Standard looping var

' Get parameters
iPageSize = 3 ' Put number of records you want to have on a page

' Retrieve page to show or default to 1
If Request.QueryString("page") = "" Then
iPageCurrent = 1
iPageCurrent = CInt(Request.QueryString("page"))
End If

' This is where you read in parameters you'll need for your query.
' Read in order or default to id
If Request.QueryString("order") = "" Then
strOrderBy = "id"
strOrderBy = Replace(Request.QueryString("order"), "'", "''")
End If

' Make sure the input is one of our fields.
strOrderBy = LCase(Request.QueryString("order"))
Select Case strOrderBy
Case "last_name", "first_name", "sales"
' A little pointless, but...
strOrderBy = strOrderBy
Case Else
strOrderBy = ""
End Select

' Now we finally get to the DB work...

strSQL = "SELECT * FROM ORDER BY " & strOrderBy & ";"

'To show one single record for viewrecord.asp comment out above and uncomment below
'strSQL = "SELECT * FROM WHERE ="& request.querystring("key")&" ORDER BY " & strOrderBy & ";"

Set objPagingConn = Server.CreateObject("ADODB.Connection")

' Create recordset and set the page size
Set objPagingRS = Server.CreateObject("ADODB.Recordset")
objPagingRS.PageSize = iPageSize

' You can change other settings as with any RS
'objPagingRS.CursorLocation = adUseClient
objPagingRS.CacheSize = iPageSize

' Open RS
objPagingRS.Open strSQL, conn,3,3, adCmdText

' Get the count of the pages using the given page size
iPageCount = objPagingRS.PageCount

' 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
Response.Write "No records found!"
' Move to the selected page
objPagingRS.AbsolutePage = iPageCurrent

' Start output with a page x of n line
<font size="+1">Page <strong><%= iPageCurrent %></strong>
of <strong><%= iPageCount %> - Total <%=rsCount("myTotal")%> Records</strong></font>



'request form code

<% Do While iRecordsShown < iPageSize And Not objPagingRS.EOF %>

<td><form method="POST" action="_viewrecord.asp">
<input type="hidden" name="key" value="<%= objPagingRS.Fields("").Value %>">
<input type="submit" value="View">
<form method="POST" action="_editrecord.asp">
<input type="hidden" name="key" value="<%= objPagingRS.Fields("").Value %>">
<input type="submit" value="Edit">
<form method="POST" action="_deleterecord.asp">
<input type="hidden" name="key" value="<%= objPagingRS.Fields("").Value %>">
<input type="submit" value="Delete">

<% iRecordsShown = iRecordsShown + 1%><% objPagingRS.MoveNext %><% Loop %>



end if

' Close DB objects and free variables
Set objPagingRS = Nothing
Set conn = Nothing

' 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
<a href="_list.asp?page=<%= iPageCurrent - 1 %>&order=<%= Server.URLEncode(strOrderBy) %>">[&lt;&lt; Prev]</a>
End If

' You can also show page numbers:
For I = 1 To iPageCount
If I = iPageCurrent Then
<%= I %>
<a href="_list.asp?page=<%= I %>&order=<%= Server.URLEncode(strOrderBy) %>"><%= I %></a>
End If
Next 'I

If iPageCurrent < iPageCount Then
<a href="_list.asp?page=<%= iPageCurrent + 1 %>&order=<%= Server.URLEncode(strOrderBy) %>">[Next &gt;&gt;]</a>
End If