This article will take you through the steps to get data from a database and present it on the screen in a nice and orderly fashion (table).
First you need to know weather you want a DSN or a DSN-Less connection. A DSN connection provides a link to the database using ODBC (DSN-Less uses ADO). I personally use DSN-Less as if you want to move it from server to server, it is easier. However the only difference is in the connection so, both will work here.

Step 1: Connecting

For beginners, this is the hardest phase, but I'm assuming that you can work around and use intelligence to find out what the statements are (this is not a copy and paste operation!). Well I'll give you the code and then I'll explain it.

DSN-LESS

Dim objConn             'Declare Variables Dim connStr Set objConn = Server.CreateObject("ADODB.Connection")       'Set objConn as an Object connStr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=F:\My Documents\aspaStuff\nwind.mdb; " objConn.Open(connStr)         'Use the object's method passing it a variable

Where to begin? After the declaring of variables we move onto the Set objConn line. The Set keyword is different from doing x = 1, set tells it to be more than a variable (usually an object). The Server.CreateObject tells the server to create an object and call it objConn, ADODB.Connection is an ADO (ActiveX Data Object) Connection, this sets up your connection to the database. Next comes the connStr. This line tells the ADO object what database driver it is using and where this database is. I'm using the northwind database stored on a directory on my hard drive, the IIS version is a little different (See: PWS and IIS ). The final line uses one of the object's methods to open the connection using the variable connStr (to tell it where to look and what to use).

DSN

Dim objConn Dim connStr Set objConn = Server.CreateObject("ADODB.Connection") connStr = "DSN=nWind" objConn.Open(connStr)

You're probably saying - "Hey! This is way easier than DSN-Less!" But if you've read the DSN tutorial, then you'll know better. DSN's can be much better but only if you are sitting at the server and are able to do it yourself and make sure its done right, as there are alot of things that can go wrong, I know, I had a run-in with a guy, where I showed him exactly what to do and he messed it up. But DSN does have better security with passwords if you're going to use one that has sensitive data and works better with SQL Server (Well that's any database server (Access is NOT a database server)).

Step 2: Get my data!

Now we've got our connection all sorted, we'll work on getting the data. Getting data doesn't mean storing it into a variable, it needs to be stored in a RecordSet. A RecordSet makes it eaisier to view data because it stores it like a table, in rows. There are several methods of using a RecordSet to get data, this is the best, because you only need 1 variable (becomes an object).

set Recordset1 = Server.CreateObject("ADODB.Recordset") Recordset1.ActiveConnection = objConn Recordset1.Source = "SELECT * FROM Customers" Recordset1.Open()

You know about the first line but then we set object properties that are then used when we call the Open() method. First we tell it where to get the connection, and because objConn already has one open, why not use it? Next we give it a SQL statement. Finally we call the Open() method. The open method basically uses the connection, asks the database for the data which matches the SQL string and stores it in itself. A recordset is a truly useful object, but it only stores 1 table. In ASP.NET they replaced RecordSet with DataSet which can hold more than 1 (See ASP.NET & Data ). You also must think of a RecordSet as a normal database table.

Step 3: Show me my data.

Now that all of our data is stored in our RecordSet (called RecordSet1) we must display it. Initially I'm just going shove it all onto the screen, quite messy but I'll clean it up later. To view data then you need these statements: NOTE: We are only going to show 1 column otherwise it gets a bit messy. When we move onto the next few steps I will be doing all of the columns.

While Not RecordSet1.EOF %><%=(Recordset1.Fields.Item("Address").Value)%><% Response.Write("<br>") RecordSet1.MoveNext Wend

This is a simple While Statement. The Not is the same as <> and RecordSet1.EOF is a Boolean property that tells us if it is at the bottom record or not. The next line closes off the ASP tag only to open it with a Response.Write tag and to make it more clear I did this. Recordset1.Fields.Item("") is just that, a field in the current row, called Address in this case, then we get the value of that cell.
This will return the following screenshot:

Wrap up

I'll wrap up this section here before we start doing the tables.