Codefixer - Dynamically Retrieve Recordset Data without Knowing Table Field Names

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
%>

If you have any code snippets to share with full credit given then send an email to Codesnippets - You'll receive full credit and a link back to your site.