Ok. So you've learned ASP and you can make some pretty cool and interactive web pages. But sooner or later you are going to have a need to use ASP to access a database of some sorts.
Written by Rich Smith Rating: / 102 August 13, 2003
There are many applications that would benefit from database integration. Examples might include message forums, product listings, and user sign-in databases among other things.
The purpose of these articles is to introduce you to the base concepts of accessing and utilizing a database with ASP. With this knowledge, you should be able to begin creating dynamic data-driven websites using the ASP architecture.
The default method to communicate with a database from within ASP is to use ADO. ADO stands for ActiveX Data Objects, and is an application program interface from Microsoft that lets a programmer writing Windows applications get access to a relational or non-relational database from both Microsoft and other database providers.
ADO can be utilized in many different development languages for Windows, including Visual Basic and Visual C++. As an example, you might using ADO to connect your Visual Basic application to an Oracle Database, or your Visual C++ application to SQL Server. In our case, we will focus on its use within ASP.
In addition, in our examples we will utilize a Microsoft Access database as the back-end data repository. There are many web hosting providers available that will host your ASP websites, but most of them charge extra for usage of a robust database like SQL2000. On the other hand, most of these providers offer the usage of MS Access databases at a very small fee, if any at all.
When accessing a database using ADO, the first thing you need to do is make a connection. Creating a connection to a database can sometimes be the hardest part of building an ASP application. When you create a connection object, you can reference the database one of two ways. The first way is to fully qualify the database name, the driver, and location within the connection string for the database. The second is to create a DSN (Data-source name) for the database and refer to it by name in the connection string.
For example, connecting to an access database using a fully qualified connection string may look like this:
Being that information about database driver, name, and location are specified when a DSN is created in the system control panel, you only need to specify the DSN name when utilizing it. For example, to reference a DSN called "MyDB", the connection information would look like this:
ADO is an object-oriented programming interface. In layman's terms, this means you will be working with different "objects" in order to communicate with the database. The first of these objects you will work with is the Connection Object.
Lets take a look at a code sample that creates a database connection object:
<% ConnectionString = "DSN=MyDB"
set conn = server.createobject("adodb.connection") conn.open ConnectionString %>
In the above example, you can see that we create an object called "conn", which is defined as an ADO connection. Once we have an object for the connection, we attempt to open that connection using the open method of the object.
One thing I'd like to mention here is you will see that I am referencing the "connection" object of the "adodb" class in my createobject method. By default that statement will fail, since IIS does not know that that object is or how to make it. There are two ways to make this information known to IIS.
The first is to create an object reference to the ADO library inside your GLOBAL.ASA file. See my article on the GLOBAL.ASA to see how this is done.
Secondly, you can use the ADOVBS.INC module. This is an include file that comes with IIS, that you can include in any ASP page. It contains the appropriate declarations to be able to used the ADO objects within your code.
When you are finished with the reference to the database connection object, it is important to correctly close and destroy it. Not doing so can potentially cause issues on your web server, due to memory resources that have been allocated but not properly released.
Follows is an acceptable method for closing and destroying a ADO connection object:
<% Conn.close Set conn = nothing %>
As you can see, the first thing we did was close the actual connection to the database. Next, we set the object to a value of "nothing", which destroys the actual object reference and releases the memory back to the operating system.
Once you have established your connection to the database, you are able to begin putting the pieces in place to add information to the database, or to search and extract existing data for presentation on your website.
In the next article in this series, we will go into recordset objects, and how you use them to pull information out of your database.
ASP.NET Export to Excel and Word using VB.NET and C#
