How to use the database in ASP

This article teaches you how to use the database in asp:

One of the most important features of ASP is that it makes it easy for you to connect to a database. is usually connected to an access or an SQL database. Because access is the easiest to start with, and you may already have access on your machine, in the example below, we'll use access for example. Once you've learned the core technology for ASP and Access database connectivity, when you start using SQL Server, you'll find that the key technologies needed are essentially the same.

When you want to connect to the database, you need to open the database on the server. You can connect and open a database by using a data source name (DSN) or by using a dsn-less connection directly in your scripting language.

Create a data source name (DSN)

You can make your database available for use in ASP by creating a System DSN in the Control Panel for your database. You can create several DSNs on your local computer, each of which corresponds to a different database that you use. After you have created the DSN, you can test your page on your local server. If your site is made up of

ISP provides services, and the ISP supports ASP, 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-> set-> Control Panel) and double-click ODBC to enter.

Select System DSN and click Add.

Select Microsoft Access Driver and click End.

Fill in the data source name. This is the name you gave to your database, so it's 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

The new DSN now appears in the System DSN and can be used on your local server.

Connecting to a database

Let's build a dsn-less connection and see how it connects to the database. When you create a DSN, you have stored some information about the database, so you don't need to repeat them every time you need to use some information, such as database type, name, location, and optional, user and password.

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

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 location of the file (absolute path). Server.MapPath provides a simple working environment for anyone using the host service to find the actual access paths that are difficult to find.

If we have established a System DSN and are named products, the connection code should be:

Now that the database is open, what can you do about it? 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.

Recordset

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

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

Objrec.open "Downloadable", StrConnect, 0,1,2

This statement creates a recordset called the downloadable Table (OBJREC), which is defined in the strconnect of the products database. With the recordset open, we can iterate through the table and display all of its contents to the screen. Alternatively, we can test the contents of a particular field, or simply write our attention to the screen.

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

product ID	sku	name	file
1	pr12345	product A	install_a.exe
2	pr12346	product B	install_b.exe

So, we have the following fields: ProductID, SKU, Name, and file. Your watch is likely to have a lot of extra field content, possibly containing a lot of things, such as price or product description. But this schematic will give you the basic concept of database tables.

Fill in the Recordset content

Using a Recordset is a very easy thing to do. If you want to iterate through the database and print all of the information to the screen, you can do this by doing the following:

While not objrec.eof

' says to does this as long as we haven ' t reached the end of the '

Response.writeobjrec ("ProductID") & ","

Response.writeobjrec ("SKU") & ","

Response.writeobjrec ("Name") & ","

Response.writeobjrec ("File") & "

"

Objrec.movenext

Wend;

Even if you have not used the loop so much, you can still read the code to write the information into the comma-delimited string, and when a new row is created in the database table, recreate a new row to record the 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, you need to keep the following points in mind:

Your HTML tags and quotes in the content.

If your label or content uses quotes, note that you use double quotes:

.

Use & to connect variables and html/content information

Select a field in the recordset

Suppose our products database also contains a field called OS, assuming that the field is a platform delimiter. Again, let's assume that the data stored in this field can only be the following: Windows NT, Windows, Windows, Windows, Mac, Unix, or Linux.

Next, we can confirm which fields we need to print to the screen and which ones to ignore. Alternatively, we can choose which fields are in one format and the other fields in other formats, such as using different colors.

Using a simple if ..., the loop can give us more control over the database right. First, let's print a record of the Windows NT product:

　　

<%

While not objrec.eof

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

Response.Write "

"

Response.Write "

"

Response.Write "

"

Response.Write "

"

End If

Objrec.movenext

Wend

%>

　　

Windows NT Products
"& Objrec (" ProductID ") &"	"& Objrec (" SKU ") &"	"& Objrec (" Name ") &"	"& Objrec (" File ") &"

Add a record

Once you start using the recordset and ASP, you will want to be able to add data to the database over the network. Adding content is important, such as when you need your web surfers 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 last three specs are defined by different pointer types: adOpenStatic, adLockOptimistic, adCmdTable:

(If you do not use the Adovbs.inc copy file, the third line should be: Objrec.open "books", Bookdb, 3,3,2).

The recordset is now ready to receive data, and you just need to tell it what to add. In this case, suppose we take the variables out of 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:

Strbooktitle and Strbookauthor represent values that are typically accessed by the user. If you just want to test the Add feature, you can add a variable to title and author-just remember to use quotes. The first time you use it, you may open your database immediately to ensure that the update occurs.

Recordset type

In the Objrec.open example shown, you will find the words 0,1,2 at the end. These numbers represent different pointer types. The type you are using depends on what you are going to use it for. For example, if you don't need to modify or add any records, you can use a lock type. And 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 Adovbs.inc backup on your server, you can use these words without using numbers. Adovbs.inc includes a list of these three constants and other constants.