By using the Count property of the Recordset Fields Collection we can dynamically retrieve a recordset's data. The code below is a generic script that will retrieve the field name and the values in each row. Just make sure you change the connection string 'sConnString' to the location of your database and modify the SQL statement to Select from your table name.
<%@ Language="VBScript" %>
<% Option Explicit %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Dynamically Retrieve Recordset Data</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>
<body>
<%
'declare your variables
Dim oConnection, oRecordset, sSQL
'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("myDatabase.mdb")
'create an ADO connection and recordset object
Set oConnection=Server.CreateObject("ADODB.Connection")
Set Recordset=Server.CreateObject("ADODB.Recordset")
'Open the connection to the database
oConnection.open(sConnString)
'SQL statement that will query the database
sSQL = "SELECT * FROM myTable"
'Open the recordset object
oRecordset.open sSQL, oConnection
'first of all determine whether there are records
If Not oRecordset.EOF Then
Response.write "<table border=""1""><tr>"
'Loop through the number of recordset fields and retrieve the render the field name
For i = 0 To oRecordset.Fields.Count - 1
Response.write "<td><strong>" & oRecordset(i).Name & "</strong></td>"
Next
Response.write "</tr>"
'Loop through the recordset rows
Do While not oRecordset.eof
Response.write "<tr>"
'Loop through the number of recordset fields and get the recordset value
For i = 0 To oRecordset.Fields.Count - 1
If (Not IsNull(oRecordset(i).Value)) then
Response.write "<td>" & oRecordset(i).Value & "</td>"
Else
Response.write "<td> </td>"
End If
Next
Response.write "</tr>"
oRecordset.movenext
Loop
Response.write "</table>"
Else
Response.write "<p>No Records</p>"
End If
%>
Get the best asp web hosting provider now and save 30%
Plug and play ASP membership script that integrates with PayPal to let you charge recurring membership fees.
