Formatting Database Records in a HTML table

The code below will display all the records from our "tblFriends" table in the database 'Friends.mdb' and format each record returned in a HTML table. The database table has 3 columns, firstly an 'ID' autonumber field, secondly a textfield called 'FirstName' and lastly another textfield called 'SurName'.

Call our file 'format_records.asp'

<%@ Language="VBScript" %>
<% Option Explicit %>
<html>
<head>
<title>Formatting database records in a HTML table</title>
</head>
<body>
<%
'declare your variables
Dim Connection, Recordset
Dim sSQL, sConnString

'declare SQL statement that will query the database
sSQL="SELECT * FROM tblFriends"

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Friends.mdb")

'create an ADO connection and recordset
Set connection = Server.CreateObject("ADODB.Connection")
Set recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database
connection.Open sConnString

'Open the recordset object, execute the SQL statement
recordset.Open sSQL, connection

'lets create a HTML table to house and format our results
response.write "<table width='100%' border='1'>"
'Now lets determine whether there are any records
If Recordset.EOF Then
Response.Write "<tr><td>No records returned.</td></tr>"
Else
'If there are records then loop through the fields and format in table rows & cells
 Do While Not recordset.EOF
Response.Write "<tr><td>" & recordset("ID") & "</td>"
Response.Write "<td>" & recordset("FirstName") & "</td>"
Response.Write "<td>" & recordset("SurName") & "</td></tr>"
'move on to the next record
Recordset.MoveNext
Loop
'Close the HTML table
response.write "</table>"
End If

'close the connection and recordset objects and free up resources
Recordset.Close
Connection.Close
Set Recordset = Nothing
Set Connection = Nothing
%>
</body>
</html>

The above code will display the ID number, the FirstName and SurName of all the entries in your database table. If there are no entries then the message 'No records returned.' will be displayed. Each record is displayed in its own table cell.



Previous: Connecting to a MSSQLServer database
LinksPro - Directory and Link Management Software

Get the best asp web hosting provider now and save 30%

Advertisements



MembersPro

MembersPro PayPal - ASP Membership software

Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.