Working with databases in ASP

One of the most important features of ASP is that it makes it very easy for you to connect to a database. is usually connected to an access or a SQL database. Because access is the easiest to start with, you might already have access on your machine, so in the example below, we'll use Access as an example. Once you have learned the core technical approach to ASP and Access database connectivity, when you start using SQL Server, you will find that the key technologies required 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 the 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 establishing a System DSN in your database in Control Panel. You can create several DSNs on your local computer, each of which corresponds to a different database that you use. After the DSN is set up, you can test your page on your local server. If your site is serviced by  ,

ISP, and this ISP supports ASP, it is likely that it will provide a GUI interface. To create a DSN for your database.  

in Windows 95/98/nt, open Control Panel (Start menu, Settings, Control Panel), and double-click ODBC Entry.  
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 gave your database, so it's the same thing 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 is now displayed in the System DSN and can be used on your local server.  

Connecting to a database
Let's create a dsn-less connection and see how the database is connected. When you create a DSN, you have stored 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, 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:

<%
strconnect = "Driver={microsoft Access Driver (*.mdb)}; Dbq=c:\\\\\\\\db\\\\\\\\products.mdb "
Set objconn = Server.CreateObject ("ADODB. Connection ")
Objconn.openstrconnect
%>

The second row defines the drive and physical path to 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 the host service to find out the actual access paths that are difficult to track.

If we have established a System DSN and 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 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 rows and columns in the table are accessible. You need to open this recordeset, just as you 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 its contents on the screen. Or, we can test the contents of a particular field, or just write the contents of 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, which may contain many things, such as price or product description. But this can provide you with the most basic concept of database tables.

Fill in the Recordset content
Using a Recordset is a very easy thing to do. If you want to cycle through the database and print all of the information to the screen, you can do it as follows:

While not objrec.eof
\\\\\\\ ' says to does 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 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 adding your table tag using Response.Write, you need to keep the following in mind:

The contents of your HTML tags and quotes. [Shanghai Treatment Impotence Hospital]

If your tag or content uses quotation marks, be careful to use double quotes:
<font size= "+2" ";.
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 this field is a platform delimiter. Again, let's assume that the data stored in this field can only be data such as Windows NT, Windows 98, Windows, Windows, Mac, Unix, or Linux.

Below, 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 will give us more control over the database. Let's start by printing 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>
Add a record
Once you start using the recordset and ASP, you'll want to be able to add data to the database over the network. Adding content is important, such as when you need your Web browser to leave their views and opinions, or when you want to manage updates.

The following code opens a recordset that is about a database table with books and their author names. You may have seen this before, but this time, the last three instructions define a different type of pointer: 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 Adovbs.inc to copy files, the third line should be: Objrec.open "books", Bookdb, 3,3,2).

The recordset is now ready to receive the data, and you just need to tell it what to add. In this case, let's say 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:

<%
Objrec.addnew
Objrec ("Title") = Strbooktitle
Objrec ("Author") = Strbookauthor
Objrec.update
%>

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

Recordset Type
In the Objrec.open example shown, you will find the word 0,1,2 at the end. These numbers represent different pointer types. The type you are using 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. 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 backup of adovbs.inc on your server, you can use these words without using numbers. Adovbs.inc includes a list of these three constants and other constants.

Working with databases in ASP