ADO for the Beginner - Working with Table Recordsets

[hari]

Now that we have created a database connection, we can move on to creating a table recordset.

If we use our ChuckNorrisVictims database as an example, we can access a table in it using the following method. Let's say there is a table called Victims, holding a list of everyone Chuck Norris has brutalized yet allowed to live.


<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/website/chucknorrisvictims.mdb  "


set rs=Server.CreateObject("ADODB.recordset")

rs.Open "Victims", conn

%>

How to Create an Add SQL Recordset

Another method of accessing our Victims table is to use SQL, like so:


<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/website/chucknorrisvictims.mdb  "


set rs=Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Victims", conn

%>

In the above code sample we use a SQL statement to retrieve a list of data from our Victims table in the ChuckNorrisVictims database.

How to Retrieve Data from a Recordset

Let's say we want to extract some data from our Victims table. Now that we have opened the table, we can do so:


<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/website/chucknorrisvictims.mdb  "


set rs.Server.CreateObject(ADODB.recordset")

rs.Open "Select * from Victims", conn


for each x ni rs.fields

response.write(x.name)

response.write(" = ")

response.write(x.value)

next

%>

This will return all of the data in the Victims table.

Now that have retrieved the data, let's display it. We can do this in several different ways.