The power of Active Server Pages comes through when we tie databases to our web sites. To connect to a database, ASP uses what is called ActiveX Data Objects, or ADO for short. ADO comes with ASP, and can be used easily from your ASP pages.

In this tutorial we will show you how to connect to our Access database called 'examples.mdb' and retrieve all the records from the table 'cars'.

At first we must create an instance of the connection object and feed it the location of the database we wish to connect to and the driver that we intend to use. For these purposes we will use a connection string. You can choose an ODBC or an OLEDB connection string. We will use OLEDB for our example as it's faster and more stable. Next, we should open the connection to the database:

This one's like a double whammie! Welcome to the world of hard core SQL Server stored procedures and to the NextRecordset method of ADO's recordset object... Before we go on, this requires SQL Server. WON'T WORK WITH ACCESS.

That said, the goal for this example was easy... Get a list of all current tables in the database created by your's truly, get a list of dependent stored procedures for each table and do it all in a stored procedure! Easy...

There are basically 2 parts to this example. The calling application (a VBScript class called SQLServerTools) and the SQL Server stored procedure: sp_Admin_ExampleTablesDependencies (included with the class). The VBScript class is easy and contains only 1 method. That method is a great example of how to use the NextRecordset method of ADO to capture a second (or 50th) recordset returned by a procedure. For example, did you know this was valid SQL:

"SELECT * FROM table1;SELECT * FROM table2;"

Well it is, SQL Server knows that this statement should produce two separate and distinct recordsets however if you execute that and don't use NextRecordset, you will only get that first RS back. The stored procedure I wrote to do all the work is the second part of the example. That procedure returns (2 * num of tables) recordsets which is variable and based on the number of tables found in sysobjects for your particular db. Bottom line, that procedure always returns more than 1 recordset.

Right now, as you read this, I estimate that the procedure returned as many as 30 recordsets that were looped through with NextRecordset... But that's not the real magic. The class only exists to call the procedure and work with it's results. The stored procedure itself does all the work.

I've commented both the app and the stored procedure to the max but I'll give you an overview of the stored procedure... The stored procedure uses a cursor to loop through the records of a recordset returned by the system table: sysobjects. The process used is almost the same as using ADO to manipulate a recordset but right in the db itself... this means huge speed bonus and, by writing a stored procedure, all that logic is forever contained within and isn't spilled out into the class where it doesn't belong.

So anyways, while the procedure is looping through each table returned by sysobjects, it's simultaneously calling the system stored procedure: sp_depends. sp_depends is a handy function that returns a recordset of names representing every stored procedure that references the entered table! sp_depends can be used on any object, not just tables, but that is another discussion for another day...

ASP is a pseudo-programming language aimed at HTML development. It allows web pages to do more than contain just static content. By placing ASP tags in with your HTML tags, you can have a page that interacts with the user. The page can make decisions based on logic and user input. If you're familiar with HTML, then you know that an HTML tag uses <> around it's tags. For example this text will be bold would make this text will be bold. ASP is similar in that it uses delimiter tags like HTML. However, the tags differ slightly. An ASP delimiter tag starts with <% and ends with %>.

Databases are a way of organizing and keeping your data. The data stored in databases can be anything from user email addresses to binary files. Databases have become so popular in the past decade that it is almost unimaginable to not to use them on the web.

Outputting data from two tables is almost as easy as displaying from one. This example utilizes two tables with state information on them. The first table (state_order) contains state abbreviations (state), state names (statename) the year each state was admitted into the union (year) and the numeric order they entered in (entered) . The second table (state_capitals) contains state abbreviations (state), state names (state) and state capitals (capital). The where statement (state_order.state = state_capitals.state) looks to make a match on the field that each table has in common to ouput the correct capital with all the info from the state_order table