ASP Recordsets

[rdove]

This tutorial is going to cover populating a basic recordset using ASP.

First, you are going to need to determine your connection string. Commonly used databases used with ASP are SQL Server and MS Access, other databases may be used, so you will have to consult google for a connection string example.

This site has a lot of good references for conenction strings:
http://www.able-consulting.com/ADO_Conn.htm

These are some examples of what a connection string might look like. You will also notice that I have already created my ADO Connection object to be used when creating a recordset.


For MS Access:

Set conn = Server.createobject("ADODB.CONNECTION")

Conn.open = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath("DB.mdb") & ";" _
& "Jet OLEDB;"

SQL Server:

Set conn = Server.createobject("ADODB.CONNECTION")

Conn.open = "Driver={SQL Server};" & _ 
            "Server=MyServerName;" & _
            "Database=myDatabaseName;" & _
            "Uid=;" & _
            "Pwd="

This code block will create a recordset for use based on the SQL statement you send.

Set rs = Server.CreateObject("ADODB.Recordset")
SQL = "Select First_Name, Last_Name from Customer"
rs.open SQL, conn

To gain access to the records in the recordset you might use a loop like the code below. This will output the first and and last name of all the records in the Customer table until the end of the file has been reached. If you notice the rs.MoveNext, this statement is essential in the loop because failing to move to the next record will cause an infinite loop because you will be stuck on the first record.

While Not rs.EOF
  response.write(rs("First_Name") & " " & rs("Last_Name") & vbcrlf)
  rs.MoveNext
Wend

And finally closing all your recordsets and connections and releasing them from memory.

rs.close
conn.close
Set rs = Nothing
Set conn = Nothing

This tutorial should give you a basic idea on how a recordset works and how you can create one.

Tutorial written by:
Ryan Wischmeyer, 2004, Indianapolis, IN