CyberCPU.net --

Connecting to an access database in ASP

Page read 3296 times

One of the main reasons a person would want to connect to a database is to collect or store information. There are a vast amount of things you can do with it. For instance my site runs on skins that are all stored in a database and my forums use a database. To start out with you are going to have to setup a connection to a database. There are lots of ways to do this but I use one that I like

Database Connection:

Set DBConnection = Server.CreateObject("adodb.connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=" & Server.Mappath("/data/database.mdb")
DSN = DSN & ";PWD="
DBConnection.Open DSN

This will connect to a database named database.mdb in the data directory of you website.

After this you can use SQL commands to manipulate data. I will go thru the main ones here

SQL Commands:

Select

With the Select command you can access information in the database.

Example:

SQL = "Select * From table Where field = 'data'"
Set RS = DBConnection.Execute(SQL)

Table = The database table you want data out of.

Field = The field within the table that you are looking for.

Data = The data you are quarrying.

Insert

With the Insert command you can add info to the database

Example:

SQL = "INSERT INTO table (field1,field2)"
SQL = SQL & "VALUES ('data1', 'data2')"
Set RS = DBConnection.Execute(SQL)

Table = The database table you want to add data to.

Field1 & Field2 = The fields within the table you want the data to go. You can have as many fields as are in the database

Data1 & Data2 = The data you want to add. You have to have data for each field you entered.

Update

With the Update command you can change data you already have stored in the database.

Example:

SQL = "UPDATE table SET field1 ='data1' WHERE field2 ='data2'"
Set RS = DBConnection.Execute(SQL)

Table = The table you want to change data in.

Field1 = The field in which you want the data to be changed.

Data1 = The changed data.

Field2 = The field in the table that usually has some kind of database ID or primary key but it can be any field that has an identifier to the dataset that has the field you want to change.

Data2 = The identifier that you are looking for in field2.

Delete

With the delete command you can delete a hole dataset.

Example:

SQL = "DELETE*FROM table WHERE field ='data'"
Set RS = DBConnection.Execute(SQL)

Table = The table you want to delete data out of.

Field = This field needs to have some kind of identifier in it like a database ID or primary key that will distinguish it from other's

Data = The identifier that will tell the server which dataset to delete

Usage:

I will now give some examples of how you can use these commands together to manipulate data.

In this example we will be starting an account, then listing its contents then updating it and finally deleting it.

The database will be called users.mdb and will have 3 Fields in the Table users

DBID (The primary key)
Name (The persons username)
email (The persons E-Mail address)

'Database connection

Set DBConnection = Server.CreateObject("adodb.connection")
DSN = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSN = DSN & "DBQ=" & Server.Mappath("users.mdb")
DSN = DSN & ";PWD="
DBConnection.Open DSN

'Getting data (You can get this data from a forum if you like)

username = "name"
Email = "someone@someware.com"

'Insert data into database

SQL = "INSERT INTO users (name,email)"
SQL = SQL & "VALUES ('"& username &"', '"& email &"')"
Set RS = DBConnection.Execute(SQL)

'List data in the database

SQL = "Select * From users Where name = '"& name &"'"
Set RS = DBConnection.Execute(SQL)

listname=RS("name")
listemail=RS("email")
DBID=RS("DBID")

Response.Write = " This is the data entered"
Response.Write = " Username:"& listname
Response.Write = " E-Mail:"& listemail
Response.Write = " Database ID:"& DBID

'Update the data in the database

newemail = "Someonenew@someware.com"

SQL = "UPDATE users SET email ='"& newemail &"' WHERE DBID = "& DBID
Set RS = DBConnection.Execute(SQL)

'List the new data

SQL = "Select * From users Where DBID = "& DBID
Set RS = DBConnection.Execute(SQL)

listname=RS("name")
listemail=RS("email")

Response.Write = " This is the data changed"
Response.Write = " Username:"& listname
Response.Write = " E-Mail:"& listemail
Response.Write = " Database ID:"& DBID

'Delete data

SQL = "DELETE*FROM users WHERE DBID ="DBID
Set RS = DBConnection.Execute(SQL)

Response.Write = " User deleted"

This is what your output should look like

This is the data entered
Username:name
E-Mail:someone@someware.com
DBID:1

This is the data changed
Username:name
E-Mail:Someonenew@someware.com
DBID:1
User deleted

Now you should have no problem manipulating data in ASP using an access database

If you find any errors let me know I wrote this article all out of memory and the script above has not been tested.