Application of Database in ASP

web front end six thousand nine hundred and eight 13 years ago (2010-10-14)

ASP One of the most important functions is that it allows you to connect very easily data base It is usually connected to an access or SQL database. Because Access is the easiest to start, and your machine may already have Access installed, we will use Access as an example in the following examples. Once you learn the core technology of ASP and Access database connection, when you start using SQL server, you will find that the key technologies required by both are basically the same.
When you want to connect to the database, you need to The server Open this database. You can use the data source name (DSN) or script The language uses a DSN less connection method to connect and open the database.

Create a data source name (DSN)
You can set up a system DSN for your database in the control panel so that your database can be connected and used in ASP. You can computer Create several DSNs on the. Each DSN corresponds to a different database you use. After establishing the DSN, you can test your page on your local server. If your website is created by

If the ISP provides services and the ISP supports ASP, it will probably 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 of the Access database you created in the system.
Click OK
Now, the new DSN will now be displayed in the system DSN and can be used on your local server.

Connect Database
Let's establish 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 some information every time you need to use it, such as database type, name, storage location and options, user and password.

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:\db\products.mdb"
Set objConn = Server. CreateObject ("ADODB.Connection")
objConn. OpenStrConnect

The second line defines the drive and physical path of the database. In order to use a DSN less connection, you need to know the actual file storage location (absolute path). Server. MapPath provides a simple working environment for anyone using host services to find out the actual access paths that are difficult to find.

If we have established a system DSN and named it 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 of course is to read a series of records in the database and put them on your page. But before that, you need a recordset.

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

Set objRec = Server. CreateObject ("ADODB.Recordset")
objRec. Open "downloadable", strConnect, 0,1,2

This statement creates a recordset (objRec) called downloadable table, which is defined in strConnect of the products database. With Recordset open, we can cycle through the table and display all its contents on the screen. Alternatively, we can test the content of specific fields, or just write the content we focus on on the screen.

Each column represents a field. Therefore, if the database table is as follows:

Product ID
  Product A
  install_ a.exe
  Product B
  Install_ b.exe

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

Fill in the recordset content
It is very easy to use recordset. If you want to cycle through the database and print all the information to the screen, you can follow the steps below:

 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 haven't used loop in this way, you can still read this code to write information into the comma delimited string. When a new row is created in the database table, you can recreate a new row to record that row in the table. You can use the same method to write data into HTML tables. By using Response To add your TABLE tag to Write, remember the following:

Your HTML tags and quotes.
If your label or content uses quotation marks, pay attention to using double quotation marks:
<FONT SIZE=""+2"">.
Use&to connect variables and HTML/content information
Select the fields in the recordset
Suppose that our products database also contains a field called OS, and that this field is a platform delimiter. Similarly, let's assume that the data stored in this field can only be the following data: 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. Or, we can choose which fields use one format and the other fields use other formats, such as different colors.

Use a simple If, Loop can give us more database control rights. First, let's print records about Windows NT products:


<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

Add a record
Once you start using recordset and ASP, you will be eager to add data to the database through the network. It is very important to add content, for example, when you need your web visitors to leave their views and opinions, or when you want to manage updates.

The following code opens a recordset, which is a database table with books and their author names. You may have seen this before, but this time, the pointer types defined in the last three specifications are different: adOpenStatic, adLockOptimal, adCmdTable:

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

(If you do not use the copy file of, the third line should be objRec.Open "books", bookdb, 3, 3, 2)

Recordset is now ready to receive data. You just need to tell it what to add. In this case, suppose we take the variables from the table: strBookTitle and strBookAuthor. Our table and books have 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, which are usually accessed by users. If you just want to test the add function, you can add a variable to title and author -- just remember to use quotation marks. When you first use it, you may immediately open your database to ensure that updates occur.

Recordset Type
On the displayed objRec In the Open example, you will find the words 0, 1, 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 means:

adOpenForwardOnly, adLockReadOnly, adCmdTable

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