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.
- Click on Start Menu | Programs | Adminstrative Tools | Data Sources
(ODBC)
- Click the System DSN tab, and click Add...
- 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.
- 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!
- Enter a name for your DSN, such as 'test_db'.
- Click Select... and choose the database you want the connection to
use.
- 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.