ASP Programming for the Absolute Beginner
.pdf
96
ASP Programming for the Absolute Beginner
|
|
|
Y |
FIGURE 5.12 |
|
L |
|
|
|
|
|
Simple output but |
|
M |
|
somewhat |
|
|
|
A |
|
||
complicated |
|
||
|
F |
||
processing! |
|
||
|
E |
|
|
T |
|
|
|
The code listing for the S |
L CT C.asp page is shown here: |
||
Listing 5.4 Select_C.asp
<HTML>
<HEAD>
<TITLE>Third SELECT Example</TITLE> <BODY>
<B>In this example, the only record that is returned is the first record that has an ID greater than 20 but less than 30, and where the artist is not equal to The Grateful Dead and where the title is not equal to CSN.</B>
<HR>
<%
Set Catalog=Server.CreateObject("ADODB.Recordset")
Catalog.open "SELECT * FROM Catalog WHERE ID > 20 AND ID < 30 AND ARTIST <> 'The Grateful Dead' AND TITLE <> 'CSN'", "DSN=Music"
Response.Write "Title: " & Catalog("Title") & "<p>"
Response.Write "Artist: " & Catalog("Artist")
Catalog.close
Set Catalog=Nothing %>
</BODY>
</HTML>
Again, the only difference between this code listing and the preceding two is the SQL statement. Note how, by adding qualifiers to the SQL statement, you can instruct the code to return a very specific record.
Team-Fly®
As you move through the rest of the book, you will see many examples of the WHERE clause being used to return a specific record. For now, you might want to experiment with the three examples listed here, modifying the SQL statements to return different values, depending on the level of specificity your SQL statement calls for.
The INSERT Statement
Although the SELECT statement is the core statement of SQL, it can take you only so far. What happens when, for example, you want to insert information into a database as it is provided by visitors to your Web site or as a result of the processing of your code? That is where the INSERT statement comes in handy.
Here is an example:
1.Load the INSERT_A.asp file from the CD-ROM, and save it to your ABG_ASP folder.
2.Open the page in a Web browser. It should look like Figure 5.13.
Examine the following code listing to see how things are working:
Listing 5.5 Insert_A.asp
<HTML>
<HEAD>
<TITLE>First INSERT Example</TITLE> <BODY>
<B>If you are viewing this code, then the following information has been inserted into the Customer table of the Music database.</B>
FIGURE 5.13
A simple example of using the SQL INSERT statement to add information to a database via your ASP Web pages.
97
C h a p te r
5
D a t ab a s A e c c e s s
w i t h
A D O
98
ASP Programming for the Absolute Beginner
<HR>
First name: William<p>
Last name: Burroughs<p>
E-mail: blee@mugwump.com<p> <hr>
<%
Set Catalog=Server.CreateObject("ADODB.Recordset")
Catalog.open "INSERT INTO CUSTOMERS (LastName, FirstName, Email) VALUES ('william', 'Burroughs', 'blee@mugwump.com')", "DSN=Music"
%>
</BODY>
</HTML>
In this example, the INSERT statement is used to insert information into three specific columns (FirstName, LastName, and Email) of the Customers table of the database. This table is illustrated in Figure 5.14.
The INSERT statement isn’t difficult to master, although you have to be careful with the syntax. Note the use of the parentheses around the table field names, as well as the values inserted into these field names. Other than this, the INSERT statement is easy to use. Throughout the other chapters in this book, you will see more examples of how this powerful statement can be used, especially in the next chapter, where information gathered directly from a Web form is inserted into a database.
Ensuring Proper Database Permissions
Unlike the SELECT statement, which simply reads data, the INSERT statement (and the UPDATE and DELETE statements, discussed later in this chapter) requires that proper permissions be set on the database.
This is especially important if you are using Windows 2000 and the Internet Information Server (IIS) because the security is much more robust, thus requiring more attention on your part. If you don’t set the security levels correctly, your code can generate errors.
Now you will set these security levels on the Music database and ensure that the permissions are set properly on your ABG_ASP folder.
1.Navigate to where you have saved the Music.mdb file (this should be in your ABG_ASP folder). Select the file, and then right-click. From the pop-up menu, select Properties.
2.In the properties dialog box that opens, click the Security tab. Then, in the Name section, click Everyone (see Figure 5.15).
FIGURE 5.14
The Customers table
of the Music
database.
FIGURE 5.15
Setting specific
security levels for
your databases is
critical to proper
functioning
with ASP.
3.For this example (and all other databases you will be working with in this book), be sure that all the checkboxes—with the exception of Full Control—are checked in the Permission area of the dialog box (refer to Figure 5.15).
4.Now that you’ve set the database permissions, it’s time to ensure that the permissions are set properly on the ABG_ASP folder in which you are placing all your ASP pages. Navigate to that folder, select it, and right-click. From the pop-up menu, select the Properties option, and click the Security tab. The properties dialog box opens (see Figure 5.16).
5.Set the permissions for Everyone to include everything except the Full Control option. Then click Apply.
The UPDATE Statement
The UPDATE statement is similar to the INSERT statement in that both statements alter the information in your database. However, the key difference between the two is that with UPDATE, you can make modifications to a specific record (or records) that already exists.
99
C h a p te r
5
D a t ab a s A e c c e s s
w i t h
A D O
FIGURE 5.16
The permission settings for your ABG_ASP Web folder should be the same for everyone as they are for your databases.
100
ASP Programming for the Absolute Beginner
FIGURE 5.17
Similar to INSERT,
the UDPATE
statement allows
you to modify
existing database
records.
As always, an example is in order:
1.Load the UPDATE_A.asp file from the CD-ROM, and save it to your ABG_ASP folder.
2.Open the file in your Web browser. Your screen should look like Figure 5.17.
This is the code for UPDATE_A.asp:
Listing 5.6 Update_A.asp
<HTML>
<HEAD>
<TITLE>First UPDATE Example</TITLE> <BODY>
<B>If you are viewing this code, then the following information has been updated in the Catalog table of the Music database.</B>
<HR>
Record ID: 24<p>
Artist: The Doors<p>
Title: Morrison Hotel<p>
Price: Changed from $10.99 to $13.99 <hr>
<%
Set Catalog=Server.CreateObject("ADODB.Recordset")
Catalog.open "UPDATE Catalog SET Price='$13.99' WHERE ID=24", "DSN=Music" %>
</BODY>
</HTML>
The critical thing you need to remember when using the UPDATE statement is that you must be sure to specify which record (or records) is to be updated. Take another look at the SQL statement in the preceding listing, and compare it to the following SQL statement:
UPDATE Catalog SET Price='$13.99'
In this example, there is no WHERE clause. Can you guess what would happen if this statement was executed? If your guess is that all the records in the Catalog table would have their price set to $13.99, you are exactly right. Unless this type of global change is what you have in mind, be very careful when using the UPDATE statement so that you update only the records that need to have their information changed.
Another example of the UPDATE statement follows:
1.Load the UPDATE_B.asp file from the CD-ROM, and save it in your ABG_ASP folder.
2.Load the page in a Web browser. Your screen should look like Figure 5.18.
Here is the code:
Listing 5.7 Update_B.asp
<HTML>
<HEAD>
<TITLE>Second UPDATE Example</TITLE> <BODY>
<B>If you are viewing this code, then all records in the Catalog table with an ID greater than 30 but less than 40, and with a price equal to $9.99 have had their price updated to $15.99</b>
<HR>
<%
FIGURE 5.18
Another UPDATE
example, this time
with an even more
specific WHERE
clause.
101
C h a p te r
5
D a t ab a s A e c c e s s
w i t h
A D O
102
ASP Programming for the Absolute Beginner
Set Catalog=Server.CreateObject("ADODB.Recordset")
Catalog.open "UPDATE Catalog SET Price='$15.99' WHERE ID > 30 AND ID < 40
AND PRICE='$9.99'", "DSN=Music"
%>
</BODY>
</HTML>
Again, this code should look familiar. The only difference is the use of a more specific WHERE clause, which is updating the price field to $15.99 in only those records that have an ID in the range of 30–40 and a price of $9.99.
The DELETE Statement
So far, you’ve learned how to read, insert, and update information in a database. Through ADO and ASP, I hope that you are finding this experience very easy and that it is giving you ideas about how to create even more dynamic, exciting Web pages.
You’ve learned a lot about basic SQL, but there is still one more fundamental statement you need to learn. This is the DELETE statement.
Consider the following example:
1.Load the file DELETE_A.asp from the CD-ROM, and save it in your ABG_ASP folder.
2.Load the page in a browser. Your screen should look just like Figure 5.19.
FIGURE 5.19
Deleting records from a database. As with the UPDATE statement, you need to be careful about clearly specifying which records you want to delete.
Here is the code:
Listing 5.8 Delete_A.asp
<HTML>
<HEAD>
<TITLE>First DELETE example</TITLE> <BODY>
<B>If you are viewing this code, then all records greater than 78 but
less than 80 have been deleted from the Catalog table of the Music database. </b>
<HR>
<%
Set Catalog=Server.CreateObject("ADODB.Recordset")
Catalog.open "DELETE * FROM Catalog WHERE ID > 78 AND ID < 80", "DSN=Music" %>
</BODY>
</HTML>
After the code is run, if you look in the Catalog table of the Music database, you will see that record 79 has been deleted (see Figure 5.20).
The Counting Magician: Working with Recordsets
You now understand the essential components of database access via ADO and ASP, you know how to create a DSN connection so that your databases can be accessed via your ASP Web pages, and you’ve experimented with the core statement of SQL.
Whew! That’s a lot of material. Before I close out this chapter, take just one more look at the issue of recordsets and how you can manipulate them.
103
C h a p te r
5
D a t ab a s A e c c e s s
w i t h
A D O
FIGURE 5.20
By utilizing a typical WHERE clause with the DELETE statement, you have pinpoint control over which records you want to delete from a database.
104
ASP Programming for the Absolute Beginner
To illustrate this, let me refer you to the first listing in this chapter, repeated here:
Listing 5.9 Counting_Magic.asp
<HTML>
<HEAD>
<TITLE>ADO First Test</TITLE> <BODY>
<B>Here is a listing of all the records in the Catalog table of the Music.mdb database!</B>
<HR>
<%
Set Catalog=Server.CreateObject("ADODB.Recordset")
Catalog.open "SELECT * FROM Catalog", "DSN=Music"
Do WHILE NOT Catalog.EOF
Response.Write Catalog("Title") & "<p>"
Catalog.MoveNext
Loop
Catalog.Close
Set Catalog=Nothing %>
</BODY>
</HTML>
If you recall, when this code is executed, the result is a simple dump of all values contained in the Title field of the Catalog table for each record in the table (refer to Figure 5.7). In the chapters that follow, you will learn all kinds of nifty ways to format your returned recordsets (dynamically populating form drop-down menus, creating tables on-the-fly, and so on). For now, however, you will stick to something simple: the recordset Counting Magician.
In the preceding code (and as described earlier in the chapter), you know that a DO WHILE loop is executed so that all the records are returned from the SQL query. In the case of the SQL statement illustrated in this code listing, this accounts for all the records in the Catalog table because there is no WHERE clause to specify individual records. Although dumping all the results of this query as this loop executes to the screen might be useful, it would be even more interesting if you could have a running tally of the total number of records as they are displayed.
Enter the Counting Magician. With just a simple Response.Write statement, you can see how you can count the number of records returned from the SQL query.
TRICK |
You will be working in much more detail with DO WHILE loops and other essential |
|
|
|
program control in Chapter 7, “Essential Programming Logic.” |
Consider the following code, which includes just a minor change from the preceding listing:
Listing 5.10 Counting_Magic_B.asp
<HTML>
<HEAD>
<TITLE>The Counting Magician</TITLE> <BODY>
<B>Here is a listing of all the records in the Catalog table of the Music.mdb database!</B>
<HR>
<%
Set Catalog=Server.CreateObject("ADODB.Recordset")
Catalog.open "SELECT * FROM Catalog", "DSN=Music"
DIM counter
counter = 0
Do WHILE NOT Catalog.EOF counter=counter+1
Response.Write Catalog("Title") & "<p>"
Response.Write "This is record count number " & counter & "<p>"
Catalog.MoveNext
Loop
Catalog.Close
Set Catalog=Nothing %>
</BODY>
</HTML>
To see this code in action, do the following:
1.Open the file Counting_Magic_B.asp from the CD-ROM, and save it in your ABG_ASP folder.
2.Open the file in a Web browser. Your screen will look like Figure 5.21.
Again, in later chapters, you will be manipulating to a much greater degree the recordsets returned by various SQL queries. For now, just start thinking about how you might dynamically export to a Web page the information returned from a SQL query. As you will see later (especially in Chapter 9, “Formatting Processed Output,” and Chapter 10, “Advanced Output Processing”), you can do some amazing things via ADO and ASP. Some real fun is in store for you.
Summary
You have covered much ground in this chapter. Specifically, you’ve learned
•The essentials of ADO, including its developmental history and how it integrates with ASP.
105
C h a p te r
5
D a t ab a s A e c c e s s
w i t h
A D O