ASP Database Query & Output
<%
'Declare Variables
Dim dcnDB 'As ADODB.Connection
Dim rsDB 'As ADODB.Recordset
Dim sqlQuery 'As String
'Find path of database file
filePath = Server.MapPath("ADVISOR.MDB")
'Open Database Connection
Set dcnDB = Server.CreateObject("ADODB.Connection")
'dcnDB.Open "DBName" - This is for an ODBC Datasource (if set up) or
' use the below code for opening a Access 2000 DB
dcnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&filePath
dcnDB.Open
'Query and create recordset
sqlQuery = "SELECT * FROM Students ORDER BY LastName,FirstName"
Set rsDB = Server.CreateObject("ADODB.Recordset")
Set rsDB = dcnDB.Execute(sqlQuery)
%>
<!-- this is the HTML part (creating a table to output records) -->
<html>
<head>
<title>Students database records</title>
</head>
<body>
<table width="400" cellpadding=0 cellspacing=0 border=0>
<tr>
<td valign="top" colspan=3>
<!-- navigation row -->
<a href="insert_students.asp">Add Student</a> |
<a href="query_students.asp">View Records</a>
<br><br>
Below are the current students in the database:
<br><br>
</td>
</tr>
<!-- header row -->
<tr>
<td valign="top">Student ID</td>
<td valign="top">First Name / Init.</td>
<td valign="top">Last Name</td>
<td valign="top">Update</td>
<td valign="top">Delete</td>
</tr>
<!-- horizontal bar row -->
<tr>
<td colspan=5><hr></td>
</tr>
<!-- output rows from database -->
<% Do Until rsDB.EOF %>
<tr>
<td valign="top"><%=rsDB("StudentID")%></td>
<td valign="top"><%=rsDB("FirstName")%> <%=rsDB("MiddleInit") %></td>
<td valign="top"><%=rsDB("LastName")%></td>
<td valign="top">
<!-- dynamically add variable values to hyperlink -->
<a href="update_students.asp?StudentID=<%=rsDB("StudentID")%>">Update</a>
</td>
<td valign="top">
<!-- dynamically add variable values to hyperlink -->
<a href="delete_students.asp?StudentID=<%=rsDB("StudentID")%>"><font color="red">Delete</font></a>
</td>
</tr>
<%
rsDB.MoveNext
Loop
%>
</table>
</body>
</html>
<%
'Close Connection and clean up
dcnDB.Close
%>
Next