Advanced ASP programming (20): display data records of ADO Components

Learning ASP is approaching the end of the long journey: Wait for the asp ado component to complete. Of course, this is also the most important step. Almost all the previous learning is for this ultimate goal. OK. Here we will give you a detailed and systematic understanding of each other.

1. Have a database

The biggest difference between static pages and dynamic pages is that databases are used. For the comparison between the advantages of a WEB program using databases and not using databases, we will not analyze them carefully. The main focus is on how to use the database and how to skillfully use the database, how to use databases more flexibly ...... The operations on the database are mainly performed on the database content:Display, insert, modify, update, query, and delete.

Of course, these methods can not be eaten in one bite. You must chew and understand and digest them slowly. Of course, first of all, it is very important to have a database first. Otherwise, everything is empty talk, and it is difficult for a clever daughter-in-law to have a good meal.

Generally, the database we call is actually a database file, which is created by some database management systems (DBMS. Currently, common DBMS is commonly heard of ACCESS, SQLSERVER, MYSQL, and ORACLE. Of course, for personal websites, ACCESS is sufficient for small enterprises. For smaller websites, ACCESS is sufficient for SQL SERVER or MYSQL of Microsoft companies, it is worth noting that MYSQL is generally perfectly integrated with another network programming language PHP. Of course, ORACLE is used for larger models. Haha, I used SYBASE Database and Informix database of UNIX system when I was learning PB ...... DBMS is just like a cool -_-!

Now, ACCESS is directly used: 1. Easy to use; 2. Easy to get started; 3. There is no better choice for beginners than this.

Everything has to begin with the actual operation.

1. Open the ACCESS database, select create database, name it cnbruce. mdb, and save it to a dedicated folder database.

2. Double-click "create table with designer" in the newly created database container. In the pop-up table 1 window, enter "cn_id" for the field name, and select "automatic ID" for the data type ", select the key button in the toolbar above to set this field as the primary key.

Enter the field "cn_title", select "text" for the data type, enter "cn_content" for the NEXT field, and select "Remarks" for the data type ";
PS: The biggest difference between a remark and a common text type is that the comment allows more field values to be inserted, which is especially important when inserting long articles.

Still enter the field "cn_author", select "text" for the data type, and switch to "allow empty strings" in "general" and select "yes ".
PS: This surface allows the value of the cn_author field to be null. This is important when some information is not filled in the form but must be correctly inserted into the database.

Finally, enter the field "cn_time", select "date/time" for the data type, and continue to switch to "default" in "general" and enter the "now ()" function.

Save "table 1" as "cnarticle"

3. Double-click the cnartile table and enter "test" in "cn_title", "cn_content", "this is a test", and "cn_author" in "cnbruce ", the time has been automatically added. Done! Close the table and shut down the database.

2. Establish a database connection

OK. The database has been created and a row of information has been filled in. Now, you need to display the row information through ASP.

To display the information, you must first establish a connection between ASP and the database file. How to create a connection? Look down.

1. conn. asp: Mainly used to connect to and open a database file. It is recommended that this file exists independently and is located at the same physical level as the folder where the database is stored.

<% Db_path = "database/cnbruce. mdb" Set conn = Server. CreateObject ("ADODB. Connection ") Connstr = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" & Server. MapPath (db_path) Conn. Open connstr %>

Db_path = "database/cnbruce. mdb"Needless to say, it is to assign the relative path of the database to a variable to facilitate subsequent calls.

Set conn = Server. CreateObject ("ADODB. Connection ")Like other components, An ADO connection is established and accepted by a conn object.

Connstr = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" & Server. MapPath (db_path)Obviously, it is the connection string, including the method for opening the database driver OLEDB, and the connection to the database (that is, the path of the database ).

We need to remind you that, whether FSO's operations on files and folders or ADO's operations on the database, obtaining the operated files is an absolute physical address. Generally, use Server. the MapPath method is relatively good.

Conn. Open connstrFinally, the object conn opens the connection to the database through the connection string connstr.

3. Display database content

A database is established and a connection to the database is established. The following figure shows the content of the database through ASP.

2. showit. asp

<! -- # Include file = "conn. asp" --> <% Set rs = Server. CreateObject ("ADODB. Recordset ") SQL = "Select * from cnarticle" Rs. Open SQL, conn, 1, 1 %> <% If rs. EOF and rs. BOF then Response. write ("no article yet ") Else Do Until rs. EOF Response. write ("the title of the article is:" & rs ("cn_title ")) Response. write ("<br> author:" & rs ("cn_author ")) Response. write ("<br> when the article was added:" & rs ("cn_time ")) Response. write ("<br> content:" & rs ("cn_content ")) Response. write ("Rs. MoveNext Loop End if %> <% Rs. close Set rs = Nothing Conn. close Set conn = Nothing %>

Simple debugging of this page is not surprising. I believe that the information in the database will be displayed. (PS: I wrote two rows in my database)

The following explains the meaning of "no:
1, <! -- # Include file = "conn. asp" -->There is no dispute, mainly because of the efficacy of calling conn. asp, which has been understood when interpreting the conn. asp file.

2, Set rs = Server. CreateObject ("ADODB. Recordset ")In addition to Connection connections, the ADO component also has a Recordset bound to the record set (I believe that those who have used DW for ASP are now somewhat back to their original stores) of course, rs can be visually imagined as a row in a database table.

3, SQL = "Select * from cnarticle"Standard structured SQL query language. Simple: I have established a database connection and bound a record set. What information do I need? That is to say, we need to filter some record sets. However, we can extract all records without any conditions.

4, rs. Open SQL, conn, 1, 1Truly open the door to the record set in the database. Specific parameters can be obtained from the following URL. Http://www.cnbruce.com/blog/showlog.asp? Cat_id = 26 & log_id = 283

5. if rs. EOF and rs. BOF thenThis statement involves the logical operations and of rs. EOF and rs. BOF and the two.Rs. EOFThe last row in the database table,Rs. BOFThe first row in the database table. The entire statement can be understood as: if the last row in the current database is the first row in the database table, you can be sure that the current database table does not have any data.

6,
Do Until rs. EOF
...
Rs. MoveNext
Loop

It is mainly a do loop statement, where the end condition of the LOOP is: Until rs. EOF, that is, the last row worth the database table. The specific information is displayed within the permitted conditions.

Only one row in the database table can be displayed in each loop. If you want to continue reading downstream dataRs. MoveNextThis is true for the feature.

7. rs ("cn_title") and so onThe information value of a specific field in the record set is displayed. It is very simple.

8. Do not forget to release the resource space.Disable record set connection and database connection.

4. Special Conditions

1. Have you noticed that the display of database table information is generally sorted by time, which is often mentioned in ascending chronological order. Note: In ascending chronological order, not every database table must have a time/Date field, as long as there is an automatically numbered field. This field is not repeated and increases sequentially. Therefore, in ascending order of time, it is actually sorted by the increase of the number value in the automatic number.

Of course, the key to the problem is to sort by time in descending order, that is, always display from the latest content. Which procedures are required?
It is easy to slightly modify the connection string.

Changed from SQL = "Select * from cnarticle"
SQL = "Select * from cnarticleOrder by cn_id desc"
The order by cn_id field is used, and the desc field is used in descending order.

2. Sometimes, you only need to extract the first or latest information. How can this problem be solved? Also, the connection string is modified.
Changed from SQL = "Select * from cnarticle order by cn_id desc"
SQL = "SelectTop 3* From cnarticle order by cn_id desc"
Top 3 indicates that the latest three pieces of information are extracted.

That's All. Now you can do it without any effort in the essence of an article system, news system, or message book.

What you leave behind is: the document format may not display properly, for example, the carriage return and space are not displayed, then you have to learn to process the values of the accepted database tables (as mentioned in several ASP exercises). The article cannot always open the database input information, then you will continue to learn how to use ASP to insert data to the database and table. Then there are too many articles. Is it very troublesome for a single page? So you need to learn paging technology ......

There are too many things to be done. I will detail them later :)