Using database in ASP

ASP has one of the most important functions, which is that it allows you to connect to the database very easily. Usually it is connected to an Access or a SQL database. Because Access is the easiest to get started, and Access may already be installed on your machine, we will use Access in the following examples. Once you learn the core technical methods of connecting ASP and Access databases, when you start using SQL server, you will find that the key technologies required for both are basically the same.

Create a data source name (DSN)
You can make your database available for use in ASP by creating a system DSN for your database in the control panel. You can create several DSNs on your local computer, each DSN corresponding to a different database you use. After setting up the DSN, you can test your page on your local server. If your website is

served by an ISP, and the ISP supports ASP, then it is very likely that it will provide a GUI interface to create a DSN for your database.

In Windows 95/98/NT, open the Control Panel (Start Menu -> Settings -> Control Panel) and double-click ODBC to enter.
Select the system DSN and click Add.
Select "Microsoft Access Driver" and click Finish.
Fill in the data source name. This is the name you give your database, so it is the same operation as an alias.
Click the Select button in the database selection to browse the location in the system where the Access database you created is stored.
Click OK
The new DSN will now show up in the system DSN and will be available on your local server.

Connecting to the database Let's set up a DSN-less connection and see how to connect to the database. When you create a DSN, you already store some information about the database, so you don't need to repeat it every time you need to use some information such as database type, name, storage location and optional properties, users and passwords.

To create a DSN-less connection, you need to provide the same information. The following example shows how to establish a DSN-less connection to a database called products:

<%
StrConnect = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:dbproducts.mdb"
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.OpenStrConnect
%>

The second line defines the driver and physical path of the database. In order to use a DSN-less connection, you need to know the actual file location (absolute path). Server.MapPath provides a simple working environment for anyone using hosting services to find those hard-to-find actual access paths.

If we have created a system DSN named products, the connection code should be:

<%
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open "products"
%>

Now that the database is open, what can you do? The first thing is of course to read a series of records in the database and put them into your page. But, before that, you need a recordset.

Recordset
A recordset is all the information stored on a special database table. Therefore, when you open this recordset, the contents of all rows and columns in the table are accessible. You need to open this recordeset just like you need to open the database connection. Their commands are similar:

Set objRec = Server.CreateObject ("ADODB.Recordset")

objRec.Open "downloadable", strConnect, 0,1,2

creates a recordset (objRec) named downloadable table, which is defined in strConnect in the products database. Using Recordset open, we can loop through this table and display all of its contents on the screen. Alternatively, we can test the contents of specific fields, or just write to the screen what we care about.

Each column represents a field. So, if the database table looks like this:

Then, we have the contents of the following fields: ProductID, SKU, Name, and File. Your table will most likely have many additional fields, which may contain many things, such as price or product (item) description. But this schematic diagram can provide you with the most basic concept of database tables.

Filling in the contents of recordset is very easy using recordset. If you want to loop through the database and print all the information to the screen, you can do the following:

While NOT objRec.EOF
' says to do this as long as we haven't reached the end of the file
Response.WriteobjRec("ProductID") & ", "
Response.WriteobjRec("SKU") & ", "
Response.WriteobjRec("Name") & ", "
Response.WriteobjRec("File") & "<BR>"
objRec.MoveNext
Wend;
Even if you have not used loops in this way, you can still read this code to write information to comma-delimited strings, and when a new row is created in the database table, create a new row to record that row in the table. You can use the same method to write data to an HTML table. By using Response.Write to add your TABLE tags, there are a few things to keep in mind:

your HTML tags and the content in quotes.
If your tags or content use quotes, be sure to use double quotes:
<FONT SIZE=""+2"">.
Use & to connect variables and HTML/content information to select fields in the recordset. Assume that our products database also contains a field called OS. Assume that this field is a platform delimiter. Again, let's assume that the data stored in this field can only be the following: Windows NT, Windows 95, Windows 98, Windows, Mac, Unix, or Linux.

Next, we can confirm which fields we need to print to the screen and which fields we want to ignore. Alternatively, we can select which fields use one format and other fields use a different format, for example, using different colors.

Using a simple If... loop can give us more control over the database. First let's print records about Windows NT products:

<TABLE BORDER=0 WIDTH=600>

<TR><TD COLSPAN=4 ALIGN=CENTER><FONT SIZE="+1"<<B>Windows NT Products</B></FONT></TD></ TR>

<%
While NOT objRec.EOF

If objRec("OS") = "Windows NT" THEN ' specifies the criteria

Response.Write "<TR><TD BGCOLOR=""#FFFF66"">" & objRec("ProductID") & " </TD>"
Response.Write "<TD>" & objRec("SKU") & "</TD>"
Response.Write "<TD>" & objRec("Name") & "</TD>"
Response.Write "<TD>" & objRec("File") & "</TD></TR>"

end if
objRec.MoveNext
Wend

%>
</TABLE>
Adding a Record Once you start using recordsets and ASP, you will really want to be able to add data to the database over the network. Adding content is important, for example, when you need your web visitors to leave their views and opinions, or when you want to conduct administrative updates.

The following code opens a recordset related to a database table with the names of books and their authors. You've probably seen this before, but this time, the last three specifications define different pointer types: adOpenStatic, adLockOptimistic, adCmdTable:

<% ' database connection already made; code not shown here
Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "books", bookdb, adOpenStatic, adLockOptimistic, adCmdTable
%>

(If you are not using the copy file of adovbs.inc, the third line should be: objRec.Open "books", bookdb, 3,3,2).

The recordset is now ready to receive data, you only need to tell it What to add. In this case, let's say we take the variables from the table: strBookTitle and strBookAuthor. Our table, books, has two fields called Title and Author, so we can add a new record by using the following statement:

<%
objRec.AddNew
ObjRec("Title") = strBookTitle
objRec("Author") = strBookAuthor
objRec.Update
%>

strBookTitle and strBookAuthor represent values ​​and are usually accessed by users. If you just want to test the add functionality, you can add a variable for title and author - just remember to use quotes. The first time you use it, you'll probably open your database immediately to make sure updates happen.

Recordset type In the objRec.Open example shown, you will find the words 0, 1, and 2 at the end. These numbers represent different pointer types. The type you use depends on what you will use it for. For example, if you don't need to modify or add any records, you can use a Lock type. When you plan to modify or update the database, the type you choose will be different.

0,1,2 actually represents:

adOpenForwardOnly, adLockReadOnly, adCmdTable.

Of course, if you already have a backup of adovbs.inc on your server, you can also use these words directly without using numbers. adovbs.inc contains these three constants and a list of other constants.