Beginning Active Server Pages

Using Databases

Unlike other server-side scripts such as PHP, Active Server Pages itself don't provide very many functions for accessing Databases, performing file operations, or anything else. However, you can use the Server.CreateObject (identical to the CreateObject command in VB), to create other ActiveX objects. For database access, we generally use ADO. Another of the great benefits of using ASP ... if you know ADO, you'll find database access in ASP pages a breeze. Also, if you have a VB component which retreives information from a database, you can use that too.

This topic is one of the widest in ASP, and we're only going to touch on the surface, but lets get started. For this example, we're going to assume that you have a DSN connection set up, which is the easiest way, and then we don't need to worry what sort database you are going to use either.

DSN Connections
If you don't know how to set up a DSN connection, follow these steps.

  1. Click on Start Menu | Programs | Adminstrative Tools | Data Sources (ODBC)
  2. Click the System DSN tab, and click Add...
  3. Select the driver for your database. This will be 'Microsoft Access Driver' for a Access database, or 'SQL Server' for an MS SQL database. Click Finish.
  4. A configuration dialog will appear. This will vary from driver to driver, and I have assumed you are using an Access database. If you're using MS SQL, you should know how to do this anyway!
  5. Enter a name for your DSN, such as 'test_db'.
  6. Click Select... and choose the database you want the connection to use.
  7. Click OK.

First, you need to create an instance of an ADO connection. We'll also declare the connection variable:

Dim cConn
Set cConn = Server.CreateObject ("ADODB.Connection")

Next, we need to establish a connection with the database. When using ADO, you can simply call

cConn.Open "dsn_name","username","password"

DSN_name is the name of the DSN connection you set up in the System DSN control panel. Username and Password is used to access the DB. If your database doesn't have a password, these can be left blank. For this example, use

cConn.Open "test_db","",""

unless you have a different DSN connection you want to use. (See the note above for information on how to create one).

Now you have an open connection, we can execute SQL statements using the Execute method:

cConn.Execute "My SQL commands"

For the moment, we'll only concern ourselves with inserting data to the database. See Getting Database Content, later in this tutorial for information on retreiving it. If you don't know any SQL, or are a bit rusty, you might want to take a quick look at our SQL commands tutorial before continuing.

You might also like...

Comments

About the author

James Crowley

James Crowley United Kingdom

James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audien...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.” - Dennis Ritchie