Project: Using a Web Interface to Edit Records in a Database
Chapter 3 covered a lot of the basics of connecting a Web site to a database: creating a connection to a database, collecting a set of data, cleaning up that data, then dropping it into a template that generates the HTML page that a visitor actually sees.
If you have a database where data entry is performed using a front end, such as the MS Access front end described in Chapter 3, that may be all you need. But there are several reasons that you might prefer to use a Web interface to manage the data. If a lot of different people will be editing information in the database, it'll be a heck of a lot easier to set up a script on a Web server than to set everyone up with a database front end (MS Access, for example), show them how to use it, and help them every time they run into to trouble getting connected to the database located on your Web server. You could save some money on licensing costs, as well. Perhaps most critically, you'll be able to edit the database from any computer with a Web browser that can establish a TCP/IP connection to the Web server where you host the database.
Hmm… Sounds like I'm writing a commercial for why the Internet matters. Maybe this should've gone on the back cover of the book instead of here in the middle. If this is old hat, my apologies for subjecting you to another tired rant. If not, file this away with all the other arguments you'll use next time you're trying to get your boss to give you a raise. :)
So let's take a quick tour of the Web interface generated by the scripts in this chapter.
First thing to consider is security: If you're going to let people edit a database, you want to make sure some spiteful recreant can't delete the information you've worked so hard to create. The scripts in this chapter use a very simple password mechanism, as in Figure 4-1.
Figure 4-1. To edit the database, a Web user has to know the password
If a user knows the correct password, the first thing she'll see is a list of categories similar to the list displayed by the scripts in the previous chapter. This time, however, she'll have the option of either clicking on a category name to view the records in that category, or of editing the information for the category itself (see Figure 4-2).
Figure 4-2. Users with access to this script can now edit category information
Using the category edit form in Figure 4-3, users can change the name or description of a category.
Figure 4-3. This form lets users edit the category names and descriptions
Just as with the category list (Figure 4-2), the list of records within a category lets you select a specific record for editing, as in Figure 4-4.
Figure 4-4. The "Edit" link lets a user edit a record
The edit record form creates an HTML as in Figure 4-5 from the field for every editable field in the database (note that the uid field, which is maintained by the database, is not editable).
Figure 4-5. The edit record form
So let's take a look at the new features that make these scripts tick.
New Features
Response.Redirect(): Redirecting Users to Another Page with the Response Object
In Chapter 1, I described how the Write() method of the Response object could be used to send a string of HTML code or content to the browser.
The Response object can also be used to send HTTP header information to a browser. HTTP headers are used to exchange a variety of information. For example, browsers use headers to tell Web servers what kind of files they're prepared to handle. Likewise, Web servers use headers to communicate about various things, including, for example, whether a file contains text, HTML, a binary image, etc. ….
An HTTP header can also be used to redirect a browser from one page to another page. In ASP, this is done through the Response object:
Response.Redirect("Page.asp")
This would redirect a browser from the current script to a page called Page.asp (the same code could just as easily be used to send visitors to an HTML page called Page.html).
Note
As the word "header" implies, HTTP headers need to be sent to the browser before the page can be sent. You'll note in this chapter that Response.Redirect() is always used before the <body> tag is opened.
ASP Session Object
HTML was designed as an open protocol that would allow computer users to share documents, regardless of the program used to create the document or the computer used to view the document. To make it easy to use, HTML was set up so that servers did not necessarily need to keep track of who was visiting a site or whether a visitor requesting a document had previously requested another document from the same server.
What made the protocol ideal for making it easy to publish documents has since made it difficult for certain situations where it's nice to know that a person who requested one page then requests another page. For example, shopping cart scripts need to know whether visitors have any items in their shopping carts. Another case where it's necessary to maintain information about a user (sometimes referred to as maintaining "state") is when it's necessary to authenticate a user, then to give that user access to more than one Web page or script.
Confronted with the need to keep track of information about site visitors, Netscape introduced a browser feature called "cookies," which was subsequently built into Internet Explorer and is now supported by most browsers. Cookies make it possible for a Web server to store small amounts of information on a browser, which can then be used to keep track of site visitors.
ASP includes a built-in facility for keeping track of unique visitors (using cookies): the Session object. Because the ASP Session object keeps track of unique visitors, once a user has been authenticated using a password check, it is very easy to use the Session object to verify that a user requesting access to a script does, in fact, have permission to use that script.
Session Password Mechanism
Using the Session object to exclude nonauthenticated users from using certain scripts requires two elements:
Code that sets a value in the Session object when a user matches a certain criterion (in this case, when a user knows a password).
Code that checks to make sure that the desired value is stored in the Session object before granting a user access to a sensitive script or HTML page.
Collecting a Password and Setting a Session Variable
The first step is to create a simple script that collects a password, checks to make sure it is correct, then assigns the value of the password to a Session object variable so that subsequent scripts will be able to verify that a user is, in fact, authenticated. The following script does the trick.
1. <%@ Language=JavaScript %> 2. <% 3. if (String(Request("password")) == "password") 4. { 5. Session("edit_db_pass") = String(Request("password")); 6. Response.Redirect("yet_another_page.asp"); 7. } 8. %> 9. <html><head></head><body> 10. <form action="<% = String(Request.ServerVariables("SCRIPT_NAME")) %>"> 11. <p>Password Please: 12. <input type=password name=password > 13. <input type=submit name="toss" value="Submit"> 14. </form> 15. </body></html>
Lines 9–14 consist of a very simple form that contains a text box and a Submit button, allowing the user to type in a password. Note that, on line 12, the type attribute of the input tag is set to password (as opposed to text), so that the password is not displayed when a user types it in.
When the password is submitted, it is reviewed by the if() statement on line 3. If the correct value is typed in, which, in this case, is the string "password", lines 5 and 6 are executed.
Line 5 creates a Session variable called edit_db_pass and assigns to it the value that was collected by the password box on the form.
Line 6 uses a Response.Redirect() statement (discussed above) to forward the browser to yet_another_page.asp.
Thus, if the correct password is entered, a user is forwarded to yet_another_page.asp, whereas, if the wrong password is typed in (or if no password is typed in, as when this script is invoked for the first time), the user is given the form and an opportunity to type in a valid password.
Checking for Valid Session Variable
Once a Session variable has been set, the next step is to check for it any time a script is generating a page to which you want to control access. In the case of the code above, this is done by checking to make sure that the value of the Session variable edit_db_pass is set to the correct value:
1. <%@ Language=JavaScript %> 2. <% 3. if (Session("edit_db_pass") != "password"){ 4. 5. Response.Redirect("password_form.asp"); 6. } 7. %> 8. //… balance of the script
On line 3, an if() statement is used to compare the value stored in Session("edit_db_pass") to the string "password". If the values are not the same, the browser is redirected to the page password_form.asp, which would contain a script similar to the script shown above, which would collect a valid password from the user.
A Quick Note on Security
Note that the password system described here offers very limited security, based on using a single password, which is passed unencrypted across the network and might be shared by many users.
Although adequate for many applications, this type of security is mainly useful where the point of the password is more to prevent someone unfamiliar with how the scripts work from accidentally deleting or modifying records than to prevent malicious users from accessing or modifying information stored in your database.
Using SQL to Update Records
Chapter 3 included a brief introduction to SQL and described how to use SQL to retrieve records from a database. This section briefly discusses how an existing record can be edited using an update statement.
This is applied to a specific record by constraining the update statement to affect only a record whose primary key is a specific value.
For example, an update statement can be used to change the description field of a specific record in the categories table.
| Category_uid | category_name | category_description |
|---|---|---|
| 3 | Web sites | Web sites that relate to our industry or are of general interest |
To do this, the statement is written so that it applies to only those records where the value of category_uid is 3. Because the category_uid field is the primary key of the table and can, therefore, contain only unique values, this effectively constrains the statement to acting on the single record shown above. To wit:
update categories set category_description = 'Spiffy sites!' where data_uid = 3
This statement modifies the value of a single field, category_description. As a result, the record is modified so that it now looks like this:
| Category_uid | category_name | category_description |
|---|---|---|
| 3 | Web sites | Spiffy sites! |
Using the Request.QueryString Object to Collect Form Data
In Chapters 2 and 3, the Request object was used to collect form data that was submitted by the user or included in a form or link in order to give a script instructions on what task to perform. In every case, the script had to identify specifically the name of the field for which a value was expected. For example, earlier in this chapter, a script retrieved a form variable called password by using the expression:
Request("password")
Well, actually, the script used an expression that included the String() function, because that helps to keep things simple:
String(Request("password"))
Either way, the point here is that it's pretty easy to get the Request object to collect a specific form variable. But what if you're too lazy to enumerate specifically every form variable that you're working with?
Fear not, for ASP gives us the Request.QueryString object, which contains all of the form information that was passed to the server using the HTTP Get method. [1] All that's required to collect all the information sent to a script via the Get method is to iterate through the Request.QueryString object:
[1] The HTTP Get method is when form information is passed to the server in the URL, which is the default method. The alternative, the Post method, passes information to the server via the HTTP headers that were discussed earlier in this chapter. Scripts can collect form information passed to the server by the Post method using the Request.Form object.
1. <%@ Language=JavaScript %> 2. <html><head></head><body> 3. <% 4. for ( field_number = 1; field_number <= Request.QueryString.count ; field_number++ ){ 5. 6. temp_field = String(Request.QueryString.Key(field_number)); 7. temp_content = String(Request.QueryString.item(temp_field)) 8. %> 9. <br><% = temp_field %>: <% = temp_content %> 10. <% 11. } 12. %></body></html>
Here's how it works:
Request.QueryString.count returns the number of form variables in the incoming form.
If we give Request.QueryString.Key() a number less than the number of incoming form variables, the Request object gives us the name of a field.
If we put that field name in Request.QueryString.item(), we get the value of that field.
Thus, line 4 opens a for() statement that loops from 1 to Request.QueryString.count.
Warning
Note that here the ASP Request goes off into left field, starting at 1 rather than at the more traditional 0. Duh.
On each iteration, the field_number is incremented by one, retrieving a different field name and value (lines 6 and 7) on each successive occasion. Finally, on line 9, the key/value pair is added to the Web page.
Iterating through form variables like this is useful in two cases. The first is while writing a script, when it's often useful to see what form variables are being passed for debugging purposes. The second is when dealing with a large number of fields that can be manipulated in a systematic fashion. One such scenario will be explored later in this chapter.
Using a Regular Expression to Examine the Contents of a String (String.search(//,""))
Chapter 2 touched briefly on regular expressions, showing how a regular expression could be used to perform pattern matching and substitutions within a string.
Regular expressions can also be used to check to see whether you can find a pattern within a string in order to make a decision.
For example, let's say that you have a variable called name, and that you want to check to see whether it contains the string "Bob" so that your script can react appropriately:
1. if (name.search(/Bob/) >= 0 ){ 2. Response.Write("Bob! n<p>So how the heck are you?") 3. } 4. else{ 5. Response.Write("Oh.<p>Hi.<p>How are you?") 6. }
When you apply the search() method to a string (regular expressions are methods of the generic JavaScript string object), this returns a "–1" if nothing is found, and the index value of where the pattern starts if it is found. Thus, if the "Bob" exists within the string name, the regular expression will return a value of 0 or more and trigger a familiar greeting. Non-Bobs will be relegated to the merely courteous "Hi".
To check for more than one value, you can separate patterns with the pipe ("|") symbol:
if (name.search(/Bob|Dolores/) >= 0 ){
//… do something
}
The JavaScript substring() Function
The substring( ) function lets you remove a small piece of a string. For example, it can be used to remove the last character from a string:
sentence = "Nothing will happen here." sentence = sentence.substring(0,(sentence.length-1));
which will remove the period from the string sentence, leaving you with the string "Nothing will happen here".
The two arguments passed to the substring() function are the index values of the first character and the last character that you want to cut out of the string that you start with.
Code To Edit Database
The code to edit the records in the database is a little more involved than the code in Chapter 3 used to display information in the database. Figure 4-6 illustrates the point.
Figure 4-6. How the scripts fit together
Utility Stuff: Passwords and Includes
Stuff that's like, you know, useful:
A script that collects a password and checks to see whether it's any good.
An include file that is shared by most of the scripts in this chapter that does different things.
Script 4-1 ch4_password_form.asp
1. <%@ Language=JavaScript %> 2. 3. <% 4. if (String(Request("password")) == "password"){ 5. 6. Session("edit_db_pass") = "OK"; 7. Response.Redirect("ch4_list_categories.asp"); 8. } 9. %> 10. 11. <html><head></head><body> 12. <form action="<% = String(Request.ServerVariables("SCRIPT_NAME")) %>"> 13. <p>Password Please: 14. <input type=password name=password > 15. <input type=submit name="toss" value="Submit"> 16. </form> 17. </body></html>
How the Script Works
The ch4_include.js file, which is used by most of the scripts in this chapter, checks to see whether a Session variable called edit_db_pass is set to a value of OK. If it's not, users are sent to this script, which asks them for a password. If they can enter a valid password, the edit_db_pass variable will be set, and they'll be redirected to ch4_list_categories.asp.
1. Set language to JavaScript.
4. Check the password, whether a form variable called password is set to an arbitrary string value, in this case, the string "password" (you might want to pick a different password).
If the password is correct, lines 5–8 are executed.
If no password is set or the password is incorrect, the rest of the script is run, which consists of a form that collects a password and submits it to this script.
6. Because a correct password was collected, this line sets the user's Session variable edit_db_pass to the value OK. This will give the user access to the other scripts in this chapter.
7. The user is redirected to the script ch4_list_catego ries.asp. Note that the Response.Redirect() command must be used before any html is sent to the browser, because it is implemented via an HTTP header.
Script 4-2 ch4_include.js
1. <% 2. if (Session("edit_db_pass") != "OK"){ 3. 4. Response.Redirect("ch4_password_form.asp"); 5.} 6. 7. var out = ""; 8. 9. var this_script_url = String(Request.ServerVariables("SCRIPT_NAME")); 10. 11. var current_category = String(Request("current_category")); // category uid 12. var current_subcategory = String(Request("current_subcategory")); //subcategory string 13. var current_record = String(Request("current_record")); // data uid 14. 15. // display scripts 16. var list_categories_script =" ch4_list_categories.asp"; 17.var list_records_script = "ch4_list_records.asp"; 18. 19. var edit_record_form_script = "ch4_edit_record_form.asp"; 20. var blank_record_form_script = "ch4_blank_record_form.asp"; 21. 22. var edit_category_form_script = "ch4_edit_category_form.asp"; 23. var blank_category_form_script = "ch4_blank_category_form.asp"; 24. 25. var edit_subcategories_form_script = "ch4_edit_subcategories_form.asp"; 26. 27. // db scripts 28. var update_record_script = "ch4_update_record.asp"; 29. var insert_record_script = "ch4_insert_record.asp"; 30. var delete_record_script = "ch4_delete_record.asp"; 31. 32. var update_category_script = "ch4_update_category.asp"; 33. var insert_category_script = "ch4_insert_category.asp"; 34. var delete_category_script = "ch4_delete_category.asp"; 35. 36. var insert_subcategory_script = "ch4_insert_subcategory.asp"; 37. var delete_subcategory_script = "ch4_delete_subcategory.asp"; 38. 39. ///////////////////////////////////////////////////// 40. // create connection to database 41. DSN = "DSN=ch3db"; 42. Conn = Server.CreateObject("ADODB.Connection"); 43. Conn.Open(DSN); 44. 45. 46. ///////////////////////////////////////////////////// 47. // create cat_object, the category object. Because category information is used 48. // on almost every page, this code is executed every time. 49. sql = "SELECT * FROM categories order by category_name"; 50. rs = Server.CreateObject("ADODB.RecordSet"); 51. rs.Open (sql, Conn); 52. if (rs.EOF){ // check to see if rs is empty 53. out += "no records found in categories table. something is probably wrong."; 54. } 55. else{ // rs not empty 56. cat_object = new Object (); 57. temp_counter = 0; 58. 59. while (! (rs.EOF)){ 60. cat_object[temp_counter] = new Object (); 61. cat_object[temp_counter].uid = parseInt(rs.fields.item ("category_uid")) 62. cat_object[temp_counter].name = String(rs.fields.item ("category_name")) 63. cat_object[temp_counter].desc = String(rs.fields.item ("category_description")) 64. 65. rs.move(1); 66. cat_object.qty = (temp_counter + 1); 67. temp_counter ++; 68. } // end while recordset 69. } // end else (rs not empty) 70. rs.Close (); 71. 72. ///////////////////////////////////////////////////// 73. // Create nav_header 74. nav_header = '<div align="center">'; 75. nav_header += '<a href="' + list_categories_script +'">List of Categories</a> ' 76. 77.if (current_category > 0){ 78. nav_header += ' | Current Category: <a href="' + list_records_script + '?current_category=' + current_category + '">' 79. for (i = 0; i < cat_object.qty; i++){ 80. if (cat_object[i].uid == current_category){ 81. nav_header += cat_object[i].name + '</a>' 82. } 83. } 84. } 85. 86. nav_header += '</div><hr>' 87. 88. ///////////////////////////////////////////////////// 89. // slash_date() 90. new function slash_date (temp_date){ 91. if (temp_date != "" && temp_date != "null" ){ 92. temp_date2 = new Date(temp_date); 93. temp_date3 = temp_date2.getMonth()+1 + "/" + temp_date2.getDate() + "/" + temp_date2.getFullYear(); 94. return (temp_date3); 95. } 96. else{ 97. return "--/--/--"; 98. } 99. } 100. 101. %>
How the Script Works
This include file is used by most of the scripts in this chapter to perform the following tasks:
Check to make sure that the user is authorized to use the current script.
Define shared variables.
Create the cat_object object, which stores information about the categories that the database knows about.
Creates a header that is used to navigate around the scripts in this chapter.
Define a function called slash_date() that seems to have something to do with formatting dates.
2–5. The first four lines deal with checking that the current user is authorized to use whatever script is invoking the ch4_include.js file.
2. An if() statement is used to check whether the Session variable edit_db_pass is not equal to OK. If this is true (its not being equal, that is), lines 3–4 are used to make sure that the user is authorized before anything else happens.
3,4. This block of code consists of a single line, which, if executed, redirects the user to the script ch4_password_form.asp, de scribed above, which will ask the user for a password.
7–37. These lines declare a bunch of variables that are useful. Some have fixed values, others are set on the fly by the script.
7. Creates a variable called out with a value of empty string (""). This allows scripts to concatenate stuff to out later without worrying about whether out exists.
9. this_script_url uses the ASP Request object to create a URL that points to the current script. Note that this will point to the script that uses this include file, not to the include file. Which is what we want.
11–13. These lines collect the form variables current_category, current_subcategory, and current_record and stuff them into local variables of the same name. Because these variables are not always set, they'll sometimes end up being "undefined".
16–37. These lines map script names to variables to make it easy to rename scripts later. This tends to be handy in projects like this one, where there are a lot of scripts floating around.
41–43. These lines create a database connection called Conn that allows the scripts in this chapter to communicate to the database.
41. The DSN variable contains a string that defines the name of the DSN that is used to connect to the database. See the previous chapter for an explanation of what a DSN is.
42. Next, an object called Conn is created using the Ser ver.CreateObject() method. As the argument im plies, the Conn object has something to do with being a connection and the acronym ADODB, which is a Microsoft database technology.
43. Using the DSN variable created in line 41, the open() method is applied to the Conn object to connect to the database that we want to play with, at which point we have a database connection that can be used to create recordsets or to execute SQL insert, update, or delete statements.
49–70. These lines create an object called cat_object that contains useful information about the categories of information that are stored in the database. For example, the default categories in the database are people, events, and Web sites.
cat_object is created by collecting all the fields that are stored in the categories table of the database (described in the previous chapter) into a recordset, then looping though the recordset and adding the information in the recordset to cat_object. Specifically:
49. This line creates an SQL statement that will grab all the records stored in the categories table and order them alphabetically according to the field category_name.
50. Creates a recordset object called rs.
51. Using the Conn object and the SQL statement created above, the recordset is populated using the open() method.
52–55. Before looping through the recordset, this line checks to make sure that rs.EOF is not true. This would be true only if the recordset were empty (meaning there are no categories in the database). If this is the case, an error message is added to the out variable in line 53.
In most cases, there will be categories in the database, and lines 55–69 will be executed as a result of the else statement on line 55.
56,57. At this point, cat_object is created, which is, of course, the main attraction at this point. Another variable called temp_counter will be created, which will be used to organize the category records that are stored in cat_object.
59. The while() loop that starts here will run until the recordset is empty and the script has collected all the category information from it. It encompasses lines 60–67.
60. The first thing the loop does is create a nested object inside of cat_object called cat_object[temp_counter]. For example, if there are three records in the recordset, three nested objects will be created: cat_object[0], cat_object[1], and cat_object[2].
61–63. Information is stored in the category_uid, category_name, and category_description fields of the re cordset are passed to the appropriate property of the nested object being created.
65. Having collected the information we needed, the move() method is applied to the Recordset object to go to the next object. Note that, without this line, the while loop would run until the Web server times it out.
66. The qty property of cat_object is set to one more than temp_counter, which is the number of categories found so far. Theoretically, it might be more efficient to do this outside of the while loop, so that it happens only once.
67. Finally, temp_counter is incremented so that the next nested object created on line 60 will have a new name.
70. Having finished with the recordset, the Close() method is used to get rid of it. At this point, cat_object is good to go and can be used any time it's necessary to get information about the categories stored in the database.
74–86. These lines create a variable called nav_header that contains a string of HTML that creates a very simple header that looks like Figure 4-7.
Figure 4-7. The header stored inside of the string nav_header
The header has two links. The first goes back to the list of all of the categories in the database—the home page of the scripts in this chapter, if you will. The second link is the name of a specific category and takes the user to a list of the records that are in that category.
Much of the code on these lines is HTML that I won't discuss here.
75. This line uses the variable list_categories_script, defined earlier in this file to build a link.
77. This line checks to make sure that the current_category is set to a value greater than zero before executing lines 78–84. This is necessary because the current_category variable is not always set, for example, when a user logs in for the first time, and the code on lines 78–84 will generate an error when this happens.
78. The variables list_records_script and current_category are used to create some HTML.
79 –83. These lines loop through cat_object to find a category whose uid matches the value of current_category so that we can find out what that category's name is (creating a link to a category called "Events" is fine. Creating a link to category "2" might alienate some users).
79. The for() loop on this line will iterate through cat_object based on the number of categories stored in cat_object.
80. The if() statement on this line checks to see whether the uid of the current category matches the value in current_category. If it does, line 81 adds the name of that category to the HTML string nav_header.
86. A bit more HTML, and nav_header is good to go.
90–99. The last bit of code in this file defines a function called slash_date(). The purpose of the function is to translate the rather indecipherable date information stored in the database into a more human-readable string. For example, Wed Jan 5 00:00:00 PST 2000 will be transformed to 1/5/2000. How it works:
90. This line tells the computer that a new function is being created called slash_date and that it takes a single argument, which will be stored in a local variable temp_date.
91. The first thing to do is to make sure that we're dealing with an actual date. This line checks to make sure that we're not dealing with an empty string or the value "null". Note that "null" is in quotes, because the script is checking for a "null" from the database, not the special JavaScript value null.
If temp_date doesn't look like it's likely to be a date, the else clause (lines 96–98) will return the string "--/--/--". Otherwise, the script assumes we've got a date, and lines 92–94 get executed.
92. This line creates a new object called temp_date2, which is created by creating a JavaScript Date() object by parsing the temp_date variable.
93. Next, a string called temp_date3 is used to collect the month, day, and year information stored in temp_date2. See Chapter 1 for more on working with the JavaScript Date() object.
The slashes that are added to the string here were the inspiration for the name of this function.
94. Our work complete, the function returns the variable temp_date3.
99. End of the function.
101. End of the include file.
Code to Edit Categories
The scripts illustrated in Figure 4-8 are used to review and edit category information as detailed below.
Figure 4-8. Scripts involved in editing categories
ch4_list_categories.asp: Displays a list of categories in the database and lets user go to ch4_list_records.asp, ch4_edit_category_form.asp, or ch4_blank_category_form.asp.
ch4_edit_category_form.asp: Lets user change category information or delete a category.
ch4_update_category.asp: Updates the database with edits created, using ch4_edit_category_form.asp.
ch4_delete_category.asp: Deletes a category.
ch4_blank_category_form: Creates a blank form that users can use to create a new category.
ch4_insert_category.asp: Inserts a new category using information typed into ch4_blank_category_form.asp.
Script 4-3 ch4_list_categories.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <html><head><title>List Categories</title></head> 5. <body> 6. 7. <center><font size="+2">Edit Database </font> 8. <br>Select a category to see records in that category. 9. <br>Select "Edit" to change name or description or to delete category. 10. <br>Use "Create New Category" button to create a new category.<hr> 11. 12. <% 13. for (i = 0; i < cat_object.qty; i++){ 14. %> 15. <a href="<% = list_records_script %>?current_category=<% = cat_object[i].uid %>"><b><% = cat_object[i].name %></b></a> 16. <a href="<% = edit_category_form_script %>?current_category=<% = cat_object[i].uid%>">Edit</a> 17. <br><% = cat_object[i].desc %><br> 18. <% 19. } 20. %> 21. 22. <hr> 23. <form action="<% = blank_category_form_script %>"> 24. <input type="submit" name="toss" value="Create New Category"> 25. </form> 26. </center> 27. 28. <% 29. Conn.Close (); 30. %> 31. 32. </body></html>
How the Script Works
If this script looks familiar, it's because it's almost identical to ch3_view_categories.asp that was described in the last chapter. There are only three significant differences: first, some copy that tells users how to use this page. Tedious as it is to take the time to write documentation, it beats having to stop what you're doing to explain to someone how to use the page for the twelfth time. Second, next to every category name, there's a link called "Edit" that takes users to ch4_edit_category_form.asp. Finally, there's a form at the bottom of the page that creates a "Create New Category" button.
Because this is mostly a repeat of the Chapter 3 script, I'll address only those portions of the script that are new:
16. This is where the "Edit" link is created. This is done using the edit_category_form_script variable that was created in the include file and setting the current_category variable so that the script will know which category is being edited.
23 –25. The small form here is used to display a button that says "Create New Category" on it. Note that there is no particular reason to use a button here rather than a link. For example, a link might have looked like:
<a href="<% = blank_category_form_script %>">Create New Category</a>
Either way works fine: It's largely a matter of interface design, which, as you've probably figured out by now, I'm not paying a whole lot of attention to in the scripts. I'm not saying that interface design isn't important. It is. In a lot of ways, it's more important than writing code, because people will be more willing to use a terrible script with a great interface than good code that looks like junk. It's just that this is a book about writing code, and to make it look really good would mean cluttering up the scripts in this book with a bunch of HTML code that would make things a lot less readable.
Once again, I'm using the name "toss" for a button whose value I don't care about. This way, six months down the road, it'll be easy for me to know at a glance that the button is there just for looks.
Script 4-4 ch4_edit_category_form.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <html><head><title>Edit Category Form</title></head><body> 5. 6. <% = nav_header %> 7. 8. <% 9. // create recordset "rs" 10. sql = "SELECT * FROM categories where category_uid = " + current_category ; 11. rs = Server.CreateObject("ADODB.RecordSet"); 12. rs.Open (sql, Conn); 13. 14. // check to see if rs is empty 15. if (rs.EOF){ 16. out += "no records found. This shouldn't happen unless multiple people are using the database simultaneously and somebody just deleted the category you were about to edit."; 17. } 18. 19. // rs not empty, we have a record to edit, so create form: 20. else{ %> 21. 22. <form action="<% = update_category_script%>"> 23. <table> 24. 25. <% 26. for ( field_number = 0; field_number < rs.fields.count ; field_number++ ){ 27. 28. temp_field = String(rs.fields(field_number).Name); 29. temp_content = String(rs.fields.item(temp_field)); 30. 31. //data cleanup: nulls 32. if (temp_content == "null") { temp_content = ""; } 33. 34. // we can get rid of "category_" on labels, but not in the form fields 35. temp_field_label = temp_field.replace (/category_/,""); 36. 37. // uid field should not be editable: 38. if (temp_field.search(/uid/) >= 0){ 39. out += ' n n<tr><td>' + temp_field_label + ': </td><td>' + temp_content + ' (this field is used by database to create a unique primary key for each category and is not editable)</td></tr>'; 40. } 41. 42. // standard text box for name and description fields 43. else { 44. out += ' n n<tr><td>' + temp_field_label + ': </td><td><input type=text name="' + temp_field + '" value="' + temp_content + '" size=60></td></tr>'; 45. } 46. } // end of looping through incoming form fields 47. 48. } // end else (rs not empty) 49. rs.Close (); 50. %> 51. <% = out %> 52. </table> 53. 54. <input type="hidden" name ="current_category" value="<% = current_category %>"> 55. <input type="submit" name ="toss" value="Save Changes"> 56. </form> 57. 58. <hr> 59. <p>Warning: if you delete this category, any records that are in this category will not be deleted, but will no longer be accessible except by opening the database with MS Access. 60. <center> 61. <form action="<% = delete_category_script%>"> 62. <input type="hidden" name ="current_category" value="<% = current_category %>"> 63. <input type="submit" name ="toss" value="Delete This Category"> 64. </form></center> 65. 66. <% 67. Conn.Close (); 68. %> 69. </body></html>
How the Script Works
This script lets users edit the name and description fields of category records, as well as lets users delete a category entirely. Note that when a category is deleted, the records within that category will no longer be accessible using the scripts in this chapter, so you want to be a little careful about that (if you think the people using this script might do this by accident, you can always pull lines 59–65 out of the script.)
The URL that invokes this script must tell the script what the unique id of the category being edited is (this is done using a form variable called current_category, which is used by most of the scripts in this chapter).
This script includes a nifty trick that I'm partial to: rather than hard-coding a form that is populated by a recordset, the form that is generated by this script is generated dynamically, based on the fields that are found in the recordset. As a result, it is entirely possible that you could add a field to the table categories (this is the database table where the category information is stored) and be able to edit information in that field without having to modify this script.
Note that this means that the field labels on the form are derived from the field names, so you'll need to give any new fields a reasonably human-readable name for this system to work.
The fine print:
1,2. Set script language to be JavaScript and import include file that is discussed earlier in this chapter.
6. Add the nav_header variable to the page being generated. This contains a link back to ch4_list_categories.asp, as well as a link to the list of records for the current category (ch4_list_records.asp).
10. Creates an SQL string (the variable sql) that selects all records from the table categories where the field category_uid is equal to the form variable current_category. Because category_uid is the primary key for categories, this will return only a single record. Thus, it will be un necessary to loop through the recordset that we retrieve, as we would if we were getting more than one record.
11,12. First, the Recordset object rs is created. On the next line, the Open() method is used to pass the SQL string sql to the Conn object and place the resultant recordset into the rs object.
15–17. Checking to see whether the recordset is empty (if rs is empty, rs.EOF would be true) won't be all that useful most of the time. But it could come in handy if multiple users are sharing the database or if you decide to extend these scripts and unexpected things start happening.
20. If, as expected, the rs object is not empty, this else{ } statement executes lines 20–49.
22. Because the script is collecting information from the user, a form is required. The form generated by this script will be sent to the script defined as the update_category_script in include.js.
26–47. Starting on line 26, a for() loop is used to loop through every field in the first (and only) record that is stored in the rs object. Field names and contents are retrieved using the rs.fields object.
In the case of this script, three fields will be retrieved from the categories table: category_uid, category_name, and category_description. The field names are retrieved from rs.fields using the counter field_number, which will increment from 0 to 2, so that, in line 28, temp_field is set, in turn, to String(rs.fields(0).Name), String(rs.fields(1).Name), and String(rs.fields(2).Name)
On line 29, temp_field is then used to set the contents of the field into the variable temp_content, which is thus set first to String(rs.fields.item("category_uid")) (when field_number is 0), then String(rs.fields.item("category_name")) (when field_number is 1), and finally to String(rs.fields.item("category_description")) (when field_number is 2).
32. If a field is empty, the value of that field will be set to null. For some reason, the word "null" seems to confuse some users, and replacing it with an empty string seems to keep them happy, and it's easy enough to do.
35. All the fields in the database are prefixed: Fields in the categories table all start with category_, fields in the data table start with data_, etc. … Again, this is something that could confuse users, who would then want to talk about it, so it's better just to get rid of the prefix. This is done using a simple regular expression, the replace() method, which in JavaScript can be applied to any string. In this case, the regular expression /category_/ is replaced with an empty string.
The purpose of the prefix is to keep field names as unambiguous as possible. You'll notice that there are not a lot of weird variables like rsFldLstWrd in my code. Sure, I spend a little more time typing than some of you freaks who like to abbreviate everything. On the other hand, when I go back to my code six months later and try to figure out what I was doing, maybe I save a little time. And maybe it makes this book a little easier to read, too. At any rate, that's how I'll do it until I change my mind or arguments about naming conventions stop reminding me of conversations about which operating system is best.
Finally, having retrieved the fields we need and cleaned them up a bit, it's time to send information to the browser. How this happens depends on what field we're talking about:
38–40. Because the uid is a field whose value is set by MS Access (called an "Autonumber" field by Access; other SQL databases offer a similar feature), it should not be editable. A search() regular expression is used to check for /uid/, and if the current field is the primary key field, it will be displayed but not be editable. In some cases, it may make sense to comment out line 39 so that this information is not displayed in order to streamline the interface.
43–45. All other fields are displayed in the same way: The temp_field_label variable is used to label a text box, whose name attribute is set to the name of the current field (temp_field) and whose value attribute is set to the value of the current field (temp_content).
Lines 46 and 48 close some curly brackets that were opened earlier.
49. Closes the Recordset object.
51. The out variable that was used to store most of the good stuff that's being sent to the browser is dumped here.
54. The current_category variable is passed to the next script using a hidden form variable. current_category is the same thing as category_uid and will be required by the next script, which commits the edits made on this form to the database, so that it can make changes to the correct record.
55. As is the case with all of the scripts in this chapter, the copy on the Submit button, in this case, "Save Changes", can be changed without affecting how the scripts work. Because people always want to change what buttons say, you want to avoid assigning any more functionality to buttons, aside from the fact that when someone clicks on a Submit button, the current form is submitted to a script.
61–64. The small form here is used to delete a category. The key stuff here is that the action attribute of the <form> tag is set to delete_category_script (defined in the include file) and that the primary key of the category to be deleted (current_category) is passed as a hidden variable. The same information could be passed as a link:
<a href="<% = delete_category_script %>?current_catego-ry=<% = current_category %>">Delete This Category</a>
67. Database connection is closed.
Script 4-5 ch4_update_category.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <% 5. temp_update = ""; 6. for ( field_number = 1; field_number <= Request.QueryString.count ; field_number++ ){ 7. 8. temp_field = String(Request.QueryString.Key(field_number)); 9. temp_content = String(Request.QueryString.item(temp_field)) 10. 11. if (temp_field.search (/uid|current_category|toss/) < 0 ){ 12. // check for null values 13. if (temp_content == "null" || temp_content == "") 14. { temp_update += " "+ temp_field + " = null,";} 15. 16. else { 17. 18. temp_content = temp_content.replace(/'/g,"'"); 19. temp_update += " "+ temp_field + " = '" + temp_content +"',"; 20. } 21. } // end if() 22. } // end for() 23. 24. // remove last character, which is a trailing comma that we don't want 25. temp_update = temp_update.substring(0,temp_update.length-1); 26. 27. update_sql = "update categories set " + temp_update + " where category_uid = " + current_category; 28. 29. Conn.Execute(update_sql); 30. 31. Conn.Close (); 32. 33. Response.Redirect(list_categories_script) 34. 35. //Response.Write("<hr> n n n" + update_sql + "} n n n<hr>"); 36. //Response.Write(list_records_script) 37. %>
How the Script Works
ch4_update_category.asp performs a relatively simple task—it collects the edits that were carried out using the form generated in the previous script (ch4_edit_category_form.asp) and writes them to the database.
This means doing two things: First, collecting the form information being forwarded from the previous script. Second, creating an SQL statement that will save this information to the database.
Just as the previous script dynamically generated a form based on the fields that it found in the database, this script is smart enough to dynamically collect the form information it receives and generate an SQL statement without knowing what those fields are. Again, this means that form fields need to be named in a specific way (in this case, they need to be named the same as the fields into which their data is to be inserted. This is easy enough, because the form fields in the previous scripts were generated on the fly, based on the field names that the script found in the database). Again, this means that you can add new fields to the category table in the database and not have to worry about updating this script. And if that's not fun, I guess I just don't know what is.
One more thing before I dive into it. The SQL statement to edit a single record whose primary key is known is an update statement and has the form:
update table set field_1='value_1', field_2='field_2', … where primary_key_field=value
Thus, the riddle that this script needs to solve is figuring out a relatively easy way to collect incoming form fields and values, and plugging that information into an SQL statement that itself consists of a series of fields and values.
That means collecting incoming database field names and values into a comma-separated string (field_1='value_1', field_2='field_2', …) while the where clause will assign the changes to that record where the category_uid field is equal to the value current_category.
This is how it works:
5. The meat of the script starts by creating an empty string variable called temp_update.
6–22. These lines encompass a for() loop that executes once for every field in the Request.QueryString object (discussed in the New Features section of this chapter). The Request.QueryString object contains all of the form information passed to the current script using the Get method (form stuff in the URL).
Just as the rs.fields object was used to collect field names and values in the previous script, the Request.QueryString object tells us how many form variables were collected (the count property), what their names are (by passing an index value to the Key() method), and what their values are (by passing a field name to the item() method).
Thus, in line 6, the for() loop increments from 1 to Request.QueryString.count, looping through each of the incoming form variables in turn, so that the information that's going to get written to the database can be put into the temp_update string. [2]
[2] Note that the index value for the QueryString object starts at 1 instead of 0.
8. temp_field is set to a form variable (or key), using Request.QueryString.Key().
9. temp_content is set to the content of that form variable, using Request.QueryString.item().
11. Having collected form information, the first step is to eliminate form information that doesn't get written to the database. Line 11 uses a regular expression to search for expressions that match form variables that should not go in the database: /uid/, /current_category/, and /toss/. Using a regular expression makes it easy to add new variables that we want to exclude. For example, this line started out as:
if (temp_field != "toss"){
Then I realized that I also needed to check for the field current_category. Thus:
if (temp_field != "toss" && temp_field != "current_category"){
Later, when I was passing a form variable category_uid (which was superfluous, because I already had current_category, but it took me a while to realize that), the idea of adding yet another "logical and" (&&) got me thinking; thus, the regular expression.
At any rate, if the regular expression finds a match, it returns the index value of where it found the match, which will be zero or higher. Without a match, it returns a value of –1, so that the if() resolves to true (minus one is less than zero), and the code on lines 12–20 is executed.
13,14. Nulls and empty strings get special treatment: null is a special value in SQL. String fields in the database (in SQL terminology: char, varchar, text/memo, etc. … [3]) that are storing a zero-length string ("") are, in fact, storing a string. On the other hand, fields that are set to null are empty. For the sake of simplicity, it's nice to have consistent data in the database.
[3] char, varchar, text, and memo are SQL data types that can store strings.
Furthermore, null is better behaved than is an empty string, because integers, date fields, strings, etc. can be set to null, and, whereas a string field can be set to an empty string, an integer field cannot.
14. At any rate, if temp_content is empty, whatever field we're dealing with gets set to null. For example, if the category_description field is set to an empty string, the result is equivalent to:
temp_update += " category_description = null,"
Note the preceding space and the trailing comma, so that any number of fields can be concatenated into the temp_update string.
16. Empty or null fields are all handled the same way:
18. First, a regular expression is used to replace single quotes—which are special characters used to delimit strings in SQL statements—with the string "'", the HTML expression for single quotes. Note the use of the "g" operator to replace every occurrence of a single quote, rather than only the first one.
19. Next, the field name and value are concatenated into the temp_update string. For example, if the field category_name is set to "Events", line 19 would be equivalent to the statement:
temp_update += " category_name='Events',";
21. End of the if() on line 11.
22. End of for() loop. At this point, the script has reviewed all of the incoming form variables, used some to concatenate field names and contents into the temp_update variable, and discarded those fields that do not need to be updated in the database.
25. Because a trailing comma (",") is included every time information is added to the temp_update string, there will always be an extra comma that needs to be removed to avoid generating an error. This is done using the JavaScript substring() function (actually a method that can be applied to any string, which makes a string kind of like an object… but that's another story).
The substring() method takes two arguments: the index values of where the substring starts and the where it ends. We want everything from the very beginning ("0") to the next-to-last character ("temp_update.length-1": The length property of a string returns the number of characters in a string. By subtracting one, the offending comma is eliminated.).
27. At last, the SQL string update_sql is generated. The temp_update string provides a list of field names and values, while the current_category variable provides the value of the primary key field, so that the appropriate record in the database is updated.
29. The SQL statement assembled in line 27 is executed using the Execute( ) method of the Conn database connection object (created in the include file).
31. The Conn object is closed.
35,36. These two lines may be useful in the future for debugging purposes. For example, if you decide to make some changes to the form that submits information to this script, and all of a sudden you start getting an error, most likely it will have something to do with attempting to execute the SQL statement on line 29. Should this happen, you can comment out lines 29 and 33 and uncomment lines 35 and 36, which will show you the two pieces of information that you're most likely to need when things stop working: What the SQL statement you're trying to execute looks like and where the form is being redirected to afterward. For example, if you're getting some kind of error from the update_sql string, there's a good chance you'll be able to eyeball the problem when you see the statement. If you're new to SQL and all looks like gibberish, you may want to take the opportunity to learn some more about SQL, or it might be time to call in a favor with a friend who's more familiar with it than you are (my favorite programming trick any time I'm stumped). Newsgroups are also often good with this kind of thing.
Script 4-6 ch4_delete_category.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. <% 4. 5. delete_sql = "delete from categories where category_uid = " + current_category; 6. 7. Conn.Execute(delete_sql); 8. 9. Conn.Close (); 10. 11. Response.Redirect(list_categories_script) 12. 13. //Response.Write("<hr> n n n" + delete_sql + " n n n<hr>"); 14. //Response.Write(list_categories_script) 15. 16. %>
How the Script Works
For this script to work, the form variable current_category needs to exist. For example, a valid URL would be:
ch4_delete_category.asp?current_category=2
Deleting something is, of course, easier than creating it or editing it, and this script is correspondingly simple.
The critical issue with an SQL delete statement is to make sure that its scope is sufficiently restricted that you are unlikely to delete something that you would prefer to keep around. For example, you want to be real careful not to execute a statement like "delete from table_name", which would delete every single record in the table table_name.
On the other hand, by restricting the scope of the delete statement to those records whose primary key field matches a specific value, you'll never delete more than one record (remember that primary key fields, by definition, contain unique values). So when you're creating a delete statement, pay attention to the where clause.
How it works:
1,2. Standard housekeeping: Language is JavaScript, and we need the include file because it creates the database Connection object (Conn) for us.
5. This is where the SQL statement delete_sql is created (discussed above).
7. SQL statement is executed.
9. Conn object is closed.
11. Browser is redirected to list_categories_script, a variable that is set in the include file, discussed earlier in this chapter.
13,14. These two lines can be uncommented if this script starts giving you error messages. You'll want to comment out lines 7 and 11 at the same time.
Script 4-7 ch4_blank_category_form.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <html><head><title>Blank Category Form</title></head><body> 5. 6. 7. <% = nav_header %> 8. To create a new category, type in a name and description below, and then click on the "Create New Category" button.<hr> 9. 10. <% 11. sql = "SELECT * FROM categories where category_uid=0" ; 12. rs = Server.CreateObject("ADODB.RecordSet"); 13. rs.Open (sql, Conn, 2,3); 14. %> 15. 16. <form action="<% = insert_category_script %>"><table> 17. 18. <% 19. // loop through form fields and create a form based on fields we found 20. // in recordset. 21. for ( field_number = 0; field_number < rs.fields.count ; field_number++ ){ 22. 23. temp_field = String(rs.fields(field_number).Name); 24. 25. // we can get rid of "category_" on labels, but not in the form fields 26. temp_field_label = temp_field.replace(/category_/,""); 27. 28. // if not uid field, create text box: 29. if (temp_field.search(/uid/) < 0){ 30. %> 31. <tr><td><% = temp_field_label %>: </td><td><input type=text name="<% = temp_field %>"value="" size=60></td></tr> 32. <% 33. } 34. 35. } // end of looping through incoming form fields 36. rs.Close (); 37. 38. Conn.Close (); 39. %> 40. 41. </table> 42. <input type="submit" name="toss" value="Create New Category"> 43. </form> 44. 45. </body></html>
How the Script Works
This script dynamically generates a form that can be used to create a new category.
The best way to talk about this script is to start off by noting that it is unnecessary. It would be considerably simpler just to create an html page to do the same task. For example, you could use the html generated by this script (I removed a few spaces and line breaks):
1. <html><head><title>Blank Category Form</title></head><body> 2. 3. <div align="center"><a href="ch4_list_categories.asp">List of Categories</a> 4. </div><hr> 5. To create a new category, type in a name and description below, and then click on the "Create New Category" button.<hr> 6. 7. <form action="ch4_insert_category.asp"><table> 8. <tr><td>name: </td><td><input type=text name="category_name" value="" size=60></td></tr> 9. <tr><td>description: </td><td><input type=text name="category_description" value="" size=60></td></tr> 10. </table> 11. <input type="submit" name="toss" value="Create New Category"> 12. </form> 13. 14. </body></html>
So why use a script? Here are a few reasons:
1. I'm lazy.
2. Writing JavaScript code that maintains itself is more fun than changing HTML.
3. This book isn't about HTML programming.
Actually, I could probably have stuck to points 1 and 2: Once again, this script builds a form based on the fields that it discovers in the database. Which means that if I add a field to the categories table in the database, not only will that field show up in the form generated by this script, it'll also be automatically handled by ch4_edit_category_form.asp and ch4_update_category.asp.
Remember
Much of the purpose of putting scripts on top of a database is to avoid having to generate dozens, hundreds, or even thousands of html pages by hand. To limit the number of trees chewed up to make this book, this script deals with a relatively simple database—it generates a form with only two fields in it. However, it could be used to create a record with many more fields. Which is, in fact, what will happen later in this chapter in ch4_blank_record_form.asp.
Enough rambling. Let's look at the script:
1,2. Declare language and grab include file.
7. Drop in nav_header string (from include file, described earlier in this chapter). This creates a link back to ch4_list_categories.asp.
11. The SQL string does not need to be all that specific, because all the script really cares about is retrieving the names of the fields in the categories table. For example, the where clause is not required, and the following statement could also be used:
sql = "SELECT * FROM categories";However, setting a where clause restricts the size of the recordset that is returned by the query, which reduces the load placed on the database and, presumably, the amount of memory required to store the recordset. (I haven't tested this, so you never know. But it seems likely that an empty recordset would use less memory than a recordset with several records in it.)
12,13. Create recordset rs.
16. As with all the scripts in this chapter, the URL in the <form> tag is a variable that is set in the include file to make it as easy as possible to change file names.
21. This is the start of a for() loop that will run from line 22 to line 35. The loop will be executed once for every field in the categories table. Because there are currently three fields in the table (category_uid, category_name, category_description), the hope is that it will run three times and that field_number will increment from 0 to 2.
This is the same mechanism used to loop through the fields in ch4_edit_category_form.asp.
23. temp_field is set to the current field name, based on the index value field_number.
24. A regular expression is used to strip the prefix category_ from temp_field and to create the variable temp_field_label.
29. The script will create a text form element for every field that does not match the regular expression /uid/. (If the search() method finds the expression /uid/, it'll return a value of zero or higher, and lines 30–33 will not be executed.)
The same thing could also have been accomplished using a different test, such as:
if (temp_field != "category_uid")31. The heart of this script: the variables temp_field_label and temp_field are used to create a label and a text box where the user will be able to type in information.
36,38. rs and Conn are closed.
42. A Submit button needs to be included so that users can submit the form. The name attribute is set to toss and won't be used by the script that processes this form.
Script 4-8 ch4_insert_category.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <% 5. fields = ""; 6. values = ""; 7. 8. // loop through incoming form fields to build sql statements 9. for ( field_number = 1; field_number <= Request.QueryString.count ; field_number++ ){ 10. 11. temp_field = String(Request.QueryString.Key(field_number)); 12. temp_content = String(Request.QueryString.item(temp_field)) 13. 14. if (temp_field.search(/toss/) < 0 ){ 15. 16. fields += ' ' + temp_field + ','; 17. 18. // check for null values 19. if (temp_content == "null" || temp_content == ""){ 20. values += " null,"; 21. } 22. // this works for non-null char fields 23. else { 24. temp_content = temp_content.replace(/'/g,"'"); 25. values += " '" + temp_content +"',"; 26. } 27. } // end if we want to add to update string 28. } // end looping through incoming form fields 29. 30. // remove last character, which is a trailing comma that we don't want 31. values = values.substring(0,values.length-1); 32. fields = fields.substring(0,fields.length-1); 33. 34. insert_sql = "insert into categories (" + fields + ") values (" + values + ")"; 35. 36. Conn.Execute(insert_sql); 37. 38. Conn.Close (); 39. 40. Response.Redirect(list_categories_script) 41. 42. //Response.Write("<hr> n n n" + insert_sql + " n n n<hr>") 43. //Response.Write(list_records_script) 44. 45. %>
How the Script Works
Like a script discussed earlier (ch4_update_category.asp), this script collects data from a form (in this case, the form created by ch4_blank_category_form.asp, above), organizes it into an SQL statement, submits the SQL statement to a database, and redirects the user to the category list, where, if all went according to plan, the user will find the new category that was just created.
Once again, form information is collected using the Request.QueryString object by iterating through the object, cleaning up the data thus collected, and dropping it into the SQL.
The syntax for an insert statement is:
insert into table_name (field_1, field_2, …, field_n) values (value_1, value_2, …, value_n)
Thus, the task of the script is primarily to create two lists: a list of fields and a list of values. Both must be separated by commas.
Here's how it works:
1,2. Set language and grab include file.
5,6. Declare the eponymous variables that will be used to collect fields and values.
9. Start of the for loop that will iterate through the Request.QueryString object and collect incoming form information.
11,12. Assign name and value of current form variable to temp_field and temp_content.
14. The regular expression here is used to exclude any fields that should not be integrated into the SQL statement that is being created (in this case, any field that matches /toss/).
16. The current value of temp_field is simply concatenated into the fields variable.
19,20. As discussed earlier with ch4_update_category.asp, both string "null" and the empty string ("") are set to the SQL special value null in values.
23–26. Otherwise, after replacing single quotes with their HTML analog, temp_content is concatenated into values, with a space in front and a comma behind it.
27,28. Close blocks that were opened earlier.
31,32. At this point, the information that is going into the database has been put into the variables values and fields. Because both have an extra trailing comma, the substring() method is applied to both, removing the last character.
Code to Edit Records
The interactions between the scripts that are used to edit records are illustrated in Figure 4-9.
Figure 4-9. Scripts used to edit records
Just as the scripts discussed in the previous section allow users to review, create, edit, and create categories, the scripts in this section let Web users look at a list of records in a category, create new records, as well as edit or delete existing records. Briefly:
ch4_list_records.asp: Lists the records for a given category. Provides a link to edit a specific record (ch4_edit_record_form.asp) and a button to create a new record (ch4_blank_record_form.asp).
ch4_edit_record_form.asp: A form used to edit a specific record. Edits are forwarded to ch4_update_record.asp.
ch4_update_record.asp: Saves changes made to a record using ch4_edit_record_form.asp.
ch4_delete_record.asp: Deletes a single record when a user hits the Delete Record button on chr_edit_re cord_form.asp.
ch4_blank_record_form.asp: Creates a blank form that lets user create a new record.
ch4_insert_record.asp: Saves a record created using ch4_blank_record_form.asp.
Although this section has a good deal of code in it, much of it is similar or identical to code already described earlier in this chapter (code to edit information in the database) or the previous chapter (code to display information stored in the database). As a result, in discussing the code, I won't go into great detail to describe code that is more carefully described elsewhere.
Script 4-9 ch4_list_records.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <html><head><title>List Records</title></head><body> 5. 6. <% = nav_header %> 7. <center><font size="+2">List of records in <% = cat_object.current %> category </font><br>Select a record to edit it, or use button at bottom to create new record 8. </center> 9. <hr> 10. 11. <% 12. ///////////////////////////////////////////////////// 13. // Create dynamic subcategory navbar 14. 15. sql = "SELECT distinct data_subcategory FROM data where data_category = " + current_category + " order by data_subcategory"; 16. rs = Server.CreateObject("ADODB.RecordSet"); 17. rs.Open (sql, Conn, 2,3); 18. if (rs.EOF){ // check to see if rs is empty 19. out += "no subcategories in this category."; 20. } 21. else{ // rs not empty 22. Response.Write("<center>"); 23. while (! (rs.EOF)){ 24. %> 25. <a href="#<% = String(rs.fields.item ("data_subcategory"))%>"><% = String(rs.fields.item ("data_subcategory"))%></a> 26.       27. <% 28. rs.move(1); 29. } // end while recordset 30. } // end else (rs not empty) 31. 32. rs.Close (); 33. 34. %> 35. <form action="<% = edit_subcategories_form_script %>"> 36. <input type="submit" name="toss" value="Edit Subcategories"> 37. </form> 38. </center> 39. <% 40. ///////////////////////////////////////////////////// 41. // Create list of records: 42. 43. sql = "SELECT * FROM data where data_category = " + current_category + " order by data_subcategory, data_name"; 44. 45. // events should be ordered by date: 46. if (current_category == 2){ 47. sql = "SELECT * FROM data where data_category = " + current_category + " order by data_subcategory,data_main_date, data_name"; 48. } 49. 50. rs = Server.CreateObject("ADODB.RecordSet"); 51. rs.Open (sql, Conn, 2,3); 52. if (rs.EOF){ // check to see if rs is empty 53. %> 54. <p>There are no records in this category 55. <% 56. } 57. else{ // rs not empty 58. 59. temp_subcat= ""; //this string used to group records by subcategory 60. fields = new Object(); // this object used to store fields in records 61. 62. while (! (rs.EOF)){ 63. 64. // clean up data and put it into the fields object: 65. for ( field_number = 0; field_number < rs.fields.count ; field_number++ ){ 66. 67. temp_field = String(rs.fields(field_number).Name); 68. temp_content = String(rs.fields.item(temp_field)); 69. temp_content = temp_content.replace(/'/g,"'"); 70. if (temp_field.search(/date/) > 0 ) //check for date fields 71. { temp_content = slash_date(temp_content);} 72. else if (temp_content == "null") { temp_content = ""; } 73. fields[temp_field] = temp_content 74. } // end looping through fields in current record 75. 76. // subcategories: when we get to a new one, display it 77. if (temp_subcat != fields.data_subcategory){ 78. temp_subcat = fields. data_subcategory; 79. %> 80. <div align="center"><b><a name="<% = temp_subcat %>"><% = temp_subcat %></a></b></div> 81. <% 82. } 83. 84. ///////////////////////////////////////////// 85. // What happens next is entirely category dependent 86. ///////////////////////////////////////////// 87. 88. ///////////////////////////////////////////// 89. // PEOPLE (1) 90. if (current_category == 1){ 91. %> 92. <p><b><% = fields.data_name%></b> - <% = fields.data_description %> 93. <a href ="<% = edit_record_form_script %>?current_category=<% = current_category current_record=<% = fields.data_uid %>">Edit</a> 94.<table width=80%><tr> 95. <td width=50%> 96. Company: <% = fields.data_company %> 97. <br>Address1: <% = fields.data_address1 %> 98. <br>Address2: <% = fields.data_address2 %> 99. <br>City: <% = fields.data_city %> 100. <br>State: <% = fields.data_state %> 101. <br>Zip: <% = fields.data_zip %> 102. <br>Country: <% = fields.data_country %> 103. </td><td> 104. Email: <% = fields.data_email %> 105. <br>Work Phone: <% = fields.data_phone_work %> 106. <br>Fax: <% = fields.data_phone_fax %> 107. <br>Cell Phone: <% = fields.data_phone_cell %> 108. <br>Home Phone: <% = fields.data_phone_home %> 109. </table> 110. <% 111. } //end if cat is 1 112. 113. ///////////////////////////////////////////// 114. // EVENTS (2) 115. else if (current_category == 2){ 116. 117. 118. %> 119. <p><b><% = fields.data_main_date %>: <% = fields.data_name%></b> 120. <a href ="<% = edit_record_form_script %>?current_category=<% = current_category %>¤t_record=<% = fields.data_uid %>">Edit</a> 121.<br>Description: <% = fields.data_description %> 122.<br>Start Date: <% = fields.data_start_date %>, End Date: <% = fields.data_end_date %> 123. 124. <% 125. } // end if category is 2 126. 127. 128. ///////////////////////////////////////////// 129. // WEB SITES (3) 130. else if (current_category == 3){ 131. %> 132. <p> 133. <a href="<% = fields.data_url %>"> 134. <b><% = fields.data_name%></b></a> 135. <a href="<% = edit_record_form_script %>?current_category=<% = current_category %>¤t_record=<% = fields.data_uid %>">Edit</a> 136.<br><% = fields.data_description %> 137. <% 138. } //end if cat is 3 139. 140. 141. ///////////////////////////////////////////// 142. // CATCH-ALL TEMPLATE 143. else { 144. %> 145. <p> 146. <a href="<% = fields.data_url %>"> 147. <b><% = fields.data_name%></b></a> 148. <a href="<% = edit_record_form_script %>?current_category=<% = current_category %>¤t_record=<% = fields.data_uid %>">Edit</a> 149. <br><% = fields.data_description %> 150. <br>Note: no template has been created for this category 151. <% 152. } 153. 154. 155. rs.move(1); 156. } // end wh ile recordset 157. } // end else (rs not empty) 158. rs.Close (); 159. 160. 161. 162. // Form to set up "Create New Record" button: 163. %> 164. <hr><center> 165. <form action="<% = blank_record_form_script %>"> 166. <input type="hidden" name="current_category" value="<% = current_category %>"> 167. <input type="submit" name="toss" value="Create New Record"> 168. </form></center> 169. 170. <% 171. Conn.Close (); 172. %> 173. </body> 174. </html>
How the Script Works
ch4_list_records.asp creates a template-based list of all the records in a single category, creates "Edit" links to let users edit specific records, and displays buttons that let people go to ch4_edit_subcategories.asp (the Edit Subcategories button) and ch4_blank_re cord_form.asp (the Create New Record button).
Most of the code in this script is identical to ch3_view_records.asp, which was discussed in detail in the previous chapter. This script is different only in that it creates the "Edit" link and buttons mentioned above.
Thus, rather than pursue the grail of redundancy, the sections of the script that are different from ch3_view_re cords have been bolded, and only those sections are discussed. [4]
[4] If you're like me, you might be wondering, Why have two practically identical scripts in the book? Isn't this, after all, the beginning of a version control nightmare? Wouldn't it make more sense to have a single script?
The answer is a qualified yes. If you're going to be using the scripts in both Chapter 3 and Chapter 4, you'll probably want to maintain only a single script. It's not all that hard: Just add if statements to execute the bold code when you're editing. Probably the best thing to check for is the session variable used to protect the scripts in Chapter 4. For example:
if (Session("edit_db_pass") == "OK")
{
// bold code goes here.
}
However, if you're going to use only the scripts in Chapter 3, you may appreciate the fact that it's not unnecessarily complicated by code that is used to edit the database via the Web.
7. For some reason, few users seem willing to skim through the source code to figure out what a script is supposed to do. Fortunately, a bit of documentation here and there seems to do the trick.
35–37. This button takes users to ch4_edit_subcategories.asp. The use of a button here is an interface decision. A simple link could also have been used:
<a href="<% = edit_subcategories_form_script %>">Edit Subcategories</a>
The edit_subcategories_form_script variable is set in the include file described earlier in this chapter.
93, 120, 135, 148. The code on each of these lines is the same, repeated because each category has its own template (see discussion of ch3_view_records.asp if you're not clear on why there are templates). In each case, the code creates an "Edit" link to the edit_record_form_script (ch4_edit_record_form.asp). The link includes the form variable current_record, which contains the uid of the record to be edited.
162–168. The form on these lines generates a Create New Record button that will take the user to ch4_blank_record_form.asp. The current_category variable is passed to the blank record form script so that it will be able to set the category scroll box to the appropriate value.
Script 4-10 ch4_edit_record_form.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <html><head><title>Edit Record Form</title></head><body> 5. 6. <% = nav_header %> 7. 8. <% 9. // grab information about record we want to edit: 10. sql = "SELECT * FROM data where data_uid = " + current_record ; 11. rs = Server.CreateObject("ADODB.RecordSet"); 12. rs.Open (sql, Conn, 2,3); 13. if (rs.EOF){ // check to see if rs is empty 14. out += "no records found. This shouldn't happen unless multiple people are using the database simultaneously"; 15. } 16. else{ // rs not empty 17. %> 18. <!-- Table: Delete and save buttons --> 19. <table border=0> 20. <tr><td valign=top> 21. <!-- Delete button sends user to the delete script --> 22. <form action="<% = delete_record_script%>"> 23. <input type="hidden" name ="current_category" value="<% = current_category %>"> 24. <input type="hidden" name ="current_record" value="<% = current_record %>"> 25. <input type="submit" name ="toss" value="Delete This Record"> 26. </form> 27. </td><td valign=top> 28. <!-- Save button sends user to the update script --> 29. <!-- Note that this form spans most of this script --> 30. <form action="<% = update_record_script%>"> 31. <input type="submit" name ="toss" value="Save Changes"> 32. </td></tr></table> 33. 34. <!-- Table: holds all of the fields that get displayed --> 35. <table> 36. <% 37. for ( field_number = 0; field_number < rs.fields.count ; field_number++ ){ 38. 39. temp_field = String(rs.fields(field_number).Name); 40. temp_content = String(rs.fields.item(temp_field)); 41. 42. //data cleanup: nulls, dates, field names 43. if (temp_content == "null") { temp_content = ""; } 44. else if (temp_field.search(/date/) >= 0 ) // check for date fields 45. { temp_content = slash_date(temp_content);} 46. // we can get rid of "data_" on labels, but not in the form fields 47. temp_field_label = temp_field.replace(/data_/,""); 48. 49. // uid field should not be editable: 50. if (temp_field.search(/uid/) >= 0){ 51. out += ' n n<tr><td>' + temp_field_label + ': </td><td>' + temp_content + '</td></tr>'; 52. } 53. 54. // category needs a scroll box: 55. else if (temp_field.search(/data_category/) >= 0){ 56. out += ' n n<tr><td>'+temp_field_label+' </td><td>'; 57. out += ' n<select name="' + temp_field + '">'; 58. for (i=0; i < cat_object.qty ; i++){ 59. out += ' n<option name="data_category" value="' + cat_object[i].uid + '"'; 60. if (current_category == cat_object[i].uid) out += ' selected ' ; 61. out += '>' + cat_object[i].name; 62. } 63. out += ' n</select></td></tr> n n'; 64. } 65. 66. // subcategory also needs a scroll box: 67. else if (temp_field.search(/data_subcategory/) >= 0){ 68. sql = "SELECT * FROM subcategories where category = " + current_category; 69. rs2 = Server.CreateObject("ADODB.RecordSet"); 70. rs2.Open (sql, Conn, 2,3); 71. if (rs2.EOF){ // check to see if rs is empty 72. out += "<tr><td>error: no subcategories exist for this category!</td><td></td></tr>"; 73. } 74. else{ // rs not empty 75. out += ' n n<tr><td>' +temp_field_label+'</td><td>'; 76. out += ' n<select name="' + temp_field + '">'; 77. while (! (rs2.EOF)){ 78. out += ' n<option name="'+ temp_field +'" value="' + String(rs2.fields.item ("subcategory")) + '" '; 79. if (temp_content == String(rs2.fields.item ("subcategory"))) 80. { out += ' selected ' ;} 81. out += '>' + String(rs2.fields.item ("subcategory")); 82. rs2.move(1); 83. } // end while recordset 84. out += ' n</select></td></tr> n n'; 85. } // end else (rs not empty) 86. rs2.Close (); 87. } // end subcategory scrollbox 88. 89. // <textarea> for description field 90. else if (temp_field.search(/description/) >= 0){ 91. out += ' n n<tr><td>' + temp_field_label + ': </td><td><textarea name="' + temp_field + '" cols=50 rows=3> ' + temp_content + '</textarea></td></tr>'; 92. } // end description field 93. 94. // standard text box 95. else { 96. out += ' n n<tr><td>' + temp_field_label + ': </td><td><input type=text name="' + temp_field + '" value="' + temp_content + '" size=60></td></tr>'; 97. } 98. 99. } // end of looping through incoming form fields 100. 101. } // end else (rs not empty) 102. 103. rs.Close (); 104. %> 105. <% = out %> 106. </table> 107. 108. <!-- Table: Delete and save buttons --> 109. <p><table border=0> 110. <tr><td> 111. <!-- Save button sends user to the update script --> 112. <!-- also, close form that spans most of this script--> 113. <input type="hidden" name ="current_record" value="<% = current_record %>"> 114. <input type="hidden" name ="current_category" value="<% = current_category %>"> 115. <input type="submit" name ="toss" value="Save Changes"> 116. </form> 117. </td><td> 118. <!-- Delete button sends user to the delete script --> 119. <form action="<% = delete_record_script%>"> 120. <input type="hidden" name ="current_category" value="<% = current_category %>"> 121. <input type="hidden" name ="current_record" value="<% = current_record %>"> 122. <input type="submit" name ="toss" value="Delete This Record"> 123. </form> 124. </td></tr></table> 125. 126. <% 127. Conn.Close (); 128. %> 129. </body></html>
How the Script Works
When a user clicks on the "Edit" link next to a record on the page generated by ch4_list_records.asp, this invokes ch4_edit_record_form.asp and passes it the uid of the record that the user wants to edit. This generates a form that a user can use to edit the information for that record or to delete the record.
ch4_edit_record_form.asp does pretty much the same thing as ch4_edit_category_form.asp, discussed earlier in this chapter: it generates a form dynamically, based on the information that is retrieved from the database. The script is more complex, however, because where the category form involved only string data that could be edited using a text box, this script deals with a more complicated set of data involving strings, dates, and multiple-choice fields that require not only simple text boxes but also select boxes and a <textarea> field. If reading JavaScript ASP code is not yet second nature to you, you'll probably want to make sure that you completely get the code to edit category scripts before you move on to the code to edit record scripts. [5] You'll notice going through this script that all of the output is collected into a string variable called out. Because of the ratio of JavaScript code to HTML, this seems like a fine way to go. The alternative would be to be constantly opening and closing the ASP tags (<%…%>), which would probably make the script a little harder to read than it is this way.
[5] On this note, it might be worth noting that probably the single most important thing to learn in order to be able to code effectively is the ability to discern between things you understand well and things that don't completely make sense to you.
Over and over, I notice an easily preventable bad habit that cripples people's ability to work effectively: using code without really understanding it and not being aware of the fact.
We all have to do deal with things that we don't fully understand—that's part of life. Whether it's nested tables, objects, http headers, the relational algebra underlying SQL, or how TCP/IP really works, we can probably all think of things that we used to have trouble conceptualizing or still have trouble conceptualizing that we are, nevertheless, able to use. The individual who is conscious of this weakness will be able to work more effectively than the individual who doesn't take the time to understand the limits of his or her knowledge.
So if you don't completely "get" the scripts in this chapter, by all means, go ahead and use them. But just because you're able to get them to work, don't pretend to yourself that you really understand them until your grasp of HTML, ASP, JavaScript, SQL, ODBC, and how Web browsers and servers work is, in fact, refined to the point that you ontologically know what they are. In the meantime, feel free to let others think what they will. But don't fool yourself.
Which is, of course, what makes this stuff fun. Here goes:
1,2. Set language and grab include file.
6. Insert nav_header variable (set in include file).
10. Create SQL statement that will grab the record we want from the database: all the fields for any record where the data_uid field is equal to the value of current_record (current_record is a form variable that the include file grabs and uses to create the JavaScript variable of the same name).
11,12. Create a Recordset object called rs.
13–15. In the unlikely event that nothing is found, generate an error message. Given that the record has to exist to create the "Edit" link that got the user to this form, it seems unlikely that it will very often get deleted between when the "Edit" link is created and when a user clicks on it.
16. If the script has a record, lines 17–102 get executed.
18–32. The first thing that happens is that two buttons are created at the top of the page: a Delete button and a Save button.
22–26. The Delete this Record button that gets created here requires three pieces of information to work: the name of the script that will do the actual deleting (delete_re cord_script, which resolves to ch4_delete_record.asp), the uid of the current record (current_re cord, so that the delete script knows what to delete), and the uid of the current category (current_category, so that after deleting the record, the delete script can forward the user to the appropriate list of records).
30. The form that gets created on this line spans most of the page, including all of the form fields that let the user edit the contents of the current record.
37. The for() loop on this line is used to iterate through every field in the recordset rs in turn. rs.fields.count tells the for() loop how many fields there are and is used to determine the upper limit of the variable field_number, which is used to store the index value of each field in turn.
There are 22 fields, so field_number will increment from 0 to 21. On each iteration of the loop, the code on lines 38–100 is executed, and on each occasion, another row is added to the table that starts on line 35.
39. field_number is used to set temp_field to the name of a field using the rs.fields object.
40. temp_field is used to set a value for temp_content.
42–45. After getting the field name and its contents from the rs.fields object, the first task is to clean up the data. First, any null fields are converted to empty strings (""), which is more user-friendly. Next, in 44–45, the slash_date() function is applied to any non-null date fields to convert the date to a friendlier format. (Without it, a date like "1/6/2000" would come out as "Thu Jan 6 00:00:00 PST 2000").
47. Again, in the interest of being user-friendly, a regular expression is used to yank the prefix /data_/ from the field name and create a new variable temp_field_label.
49–97. Next, a series of if(){ …} else if(){ …} else if(){ …} else{ } blocks are used to execute different blocks of code, depending on the field.
In each case, the decision is made by applying a regular expression to the temp_field string. If a match is found, the corresponding block of code is executed. For example, to find the data_uid field, the regular expression searches for the /uid/ pattern.
49–52. data_uid field: This is the simplest case. Because the data_uid is set by the database, it should not be editable by the user. Displaying the field is a matter of creating a table row with two cells in it, and putting temp_field_label in the first field and temp_content in the second.
Alternatively, you might decide that there's no point in displaying this information at all, in which case, you might want to do something like:
if (temp_field.search(/uid/) >= 0){ // do nothing }
43–64. data_category field (scroll box): The category scroll box is a little tricky. Because the categories are fixed, users need to be constrained to choosing from a list. This can be done either with a scroll box or radio buttons. For this kind of thing, I think scroll boxes are more conventional.
Getting the categories is fairly easy, because category information is put into cat_object in the include file. In line 57, the <select> form field is started, with the name set to temp_field.
In lines 58–62, the script iterates through cat_object once for each category, using cat_obect[i].uid to set a value (an integer) for each option that will appear in the select box and cat_object[i].name to create a human-readable label (the category name) for each option. The for() loop used here is complicated by the fact that, on each iteration, the script checks to see whether the category being put into the <select> box matches the current category (stored in the current_category object). If a match is found (line 60), the selected option is set for the <option> tg being assembled, so that the correct category will be displayed by the select box.
66–87. data_subcategory field (scroll box): The subcategory scroll box needs to be populated with valid subcategories for the current category. The process is similar to how a scroll box was set up for the category field above, except that because this information has not yet been collected (there is no cat_object to turn to), a list of valid subcategories must be collected by querying the database.
In lines 68–70, a new recordset, rs2, is created that contains all of the subcategories that exist for the current category. Subcategories are stored in the subcategories table. Lines 71–73 generate an error message if no subcategories are found. If you find that you don't necessarily want to use subcategories, you might want to change the message in line 72.
77–83. Loop through rs2 and populate the <select> box that is opened on line 76 and closed on line 84. Again, there is a check on each iteration to make sure that the appropriate subcategory is selected.
89–92. data_description field (<textarea>): The description field is the last field that gets special treatment. Because it may contain more than one line of information, it is set up as a <textarea> form field. This is quick work, a matter of dropping the variables temp_field_label, temp_field, and temp_content into the appropriate HTML.
94–97. All the other fields: The remaining 18 fields can all be handled the same way—a text box (an <input> tag with the type attribute set to text) is generated with the same three variables dropped in the appropriate place: temp_field_label, the human-readable field label, temp_field, which is the full name of the field, and temp_content, the contents of the field that were retrieved from the database.
Note that neither this script nor any of the other scripts in this chapter make any effort to perform error checking. If you're going to be setting novice users loose on this, it's probably something that you'll want to add, using either client-side or server-side code. [6]
[6] For information on client-side error checking with JavaScript, see JavaScript for Web Professionals, also in this series.
99, 101. These lines close blocks of code that were started toward the beginning of the script.
103. Closes the rs object that will not be used again.
105. The out string, which is by now quite large, is sent to the browser at this point.
106. The table that contains the editable fields is closed.
Finally, another set of Save and Delete buttons is placed at the bottom of the page.
113–116. The Save button has to go first, because it is part of the form that has been running since line 30. Both current_record and current_category are set as hidden form variables. current_category is necessary because it contains the primary key of the current record, which the update script will need in order to save edits to the database. current_category is used so that the user can be returned to the list of records for the current category (otherwise, the user would have to be returned to the list of categories, which would be disorienting).
127. The Conn object that is opened in the include file is closed.
When a user has completed her edits, she clicks on the Save Changes button, which submits the form to ch4_update_record.asp, which is next.
Script 4-11 ch4_update_record.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <% 5. temp_update = ""; 6. 7. for ( field_number = 1; field_number <= Request.QueryString.count ; field_number++ ){ 8. 9. temp_field = String(Request.QueryString.Key(field_number)); 10. temp_content = String(Request.QueryString.item(temp_field)) 11. 12. // make sure field is one we want to use 13. if (temp_field.search (/current_record|current_category|toss/) < 0 ){ 14. 15. // nulls 16. if (temp_content == "null" || temp_content == ""){ 17. 18. temp_update += " "+ temp_field + " = null,"; 19. } 20. 21. //int fields 22. else if (temp_field.search (/data_category/) >= 0) 23. { temp_update += " "+ temp_field + " = " + temp_content +",";} 24. 25. /this works for non-null char fields 26. else { 27. 28. temp_content = temp_content.replace (/'/g,"'"); 29. temp_update += " "+ temp_field + " = '" + temp_content +"',"; 30. } 31. 32. } // end if we want to use field 33. } // end for loop 34. 35. // remove last character, which is a trailing comma that we don't want 36. temp_update = temp_update.substring(0,temp_update.length-1); 37. 38. update_sql = "update data set " + temp_update + " where data_uid = " + current_record; 39. 40. Conn.Execute(update_sql); 41. 42. Conn.Close (); 43. 44. Response.Redirect(list_records_script + "?current_category=" + current_category) 45. 46. //Response.Write("<hr> n n n" + update_sql + " n n n<hr>"); 47. //Response.Write(list_records_script + "?current_category=" + current_category) 48. %>
How the Script Works
This script works pretty much the same as ch4_update_category.asp (discussed earlier in this chapter). I wrote an overview of how that one worked before diving into the code. Here, I'll just dive into the code:
1,2. Tell Bill to use open protocols, grab bag of tricks.
5. Declare variable temp_update that will be used to store key/value pairs for the SQL update statement that is created by this script.
7. Rather than explicitly call out form variables, the script uses the for() loop that starts on this line to iterate through the Request.QueryString object and grab all the incoming form information. The block of code in the loop (lines 8–33) then looks at each incoming variable in turn and decides whether and how to put the incoming form information into the update statement used to update the database.
Note that field_number, the counter that is used to iterate through the Request.QueryString object, starts at one, rather then zero. I think I've already complained to you about that one. The count property of Request.QueryString is used to set the upper limit of field_number.
9,10. temp_field and temp_content are used to store, in turn, the values of each form variable and its contents as they are retrieved from Request.QueryString.
13. A regular expression is used to eliminate variables that we're not interested in. Any field that matches the regular expression (/current_record|current_category|toss/) will cause the search() method to return a value of 0 or higher, so that the if() statement resolves to false. Any field that fails to match is presumed to be a database field, and lines 14–32 are executed.
14–37. The if(){ …} else if(){ …} else{ } statement that runs from line 14 to line 32 ensures that, depending on the field in question, the field name and its contents are added to the temp_update string.
16–19. The first thing the script checks for is null values in temp_content, looking for the keyword null or an empty string (""). If this is the case, that field is set to null.
22–24. The next step checks for integer fields, which, unlike string fields, should not have single quotes around them. Because some of the string fields might contain integers, checking the contents is not an option. And because I didn't set up a naming convention to deal with integer fields, there is no generic way to identify integer fields. As a result, they need to be explicitly identified in the regular expression used on line 22, which checks for /data_category/. This works because data_category is the only nonstring field in the data table.
26–30. Fields that do not contain nulls or integer values can all be treated the same: It is simply a matter of first replacing any single quotes within the field with their HTML equivalent (line 28, with a regular expression), then of encapsulating the contents of the field with single quotes (line 29).
32. Close the if() started on line 13.
33. Closes the for() loop.
36. Because the script deals with an indeterminate number of fields, each time a field is added to temp_update, a trailing comma is added in anticipation of the next. The last comma is superfluous and is removed here, using the substring() method that can be applied to any JavaScript string.
38. Here, the SQL statement that lies at the core of this script is finally put together. The temp_update string at this point consists of a series of comma-separated key/value pairs:
field_1 = 'field_1', field_2 = field_2, … field_n = 'field_n'
Meanwhile, current_record contains the uid of the record that is to updated, so that for a record whose id is "i", update_sql will be:
update data set field_1 = 'field_1', field_2 = field_2, … field_n = 'field_n' where data_uid = i
The fields, of course, will be actual field names with actual values or the SQL special value null.
40. Finally, the SQL update statement is executed through the Conn object (created in the include file).
42. The Conn object is closed.
44. Response.Redirect is used to redirect the browser to ch4_list_records.asp.
46–47. I left these lines here in case you ever need to debug this script. To debug, comment out lines 40 and 44, and comment in lines 46 and 47. This will let you take a look at the SQL code being generated and the URL that the browser is being redirected to.
Script 4-12 ch4_delete_record.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <% 5. delete_sql = "delete from data where data_uid = " + current_record; 6. 7. Conn.Execute(delete_sql); 8. 9. Conn.Close (); 10. 11. Response.Redirect(list_records_script + "?current_category=" + current_category) 12. 13. //Response.Write("<hr> n n n" + delete_sql + " n n n<hr>"); 14. //Response.Write(list_records_script + "?current_category=" + current_category) 15. %>
How the Script Works
Give this script the primary key of a record in the data table, and it will delete a record for you.
5,7. The work is performed on lines 5 and 7. Using the form value current_record, line 5 creates an SQL statement that will delete any record whose data_uid field is set to that value. The SQL delete statement is then executed on line 7.
9,11. Line 9 closes the Conn object, and line 11 redirects users to ch4_list_records.asp.
13,14. Lines 13 and 14 are there in case you get creative and need to do some debugging. Note that, as long as you give this script a valid value for current_record, the delete statement should work, and as long as you give it a valid value for current_category, the redirect statement should do fine as well.
Script 4-13 ch4_blank_record_form.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <html><head><title>Blank Record Form</title></head><body> 5. 6. <% = nav_header %> 7. <% 8. sql = "SELECT * FROM data where data_uid=0" ; 9. rs = Server.CreateObject("ADODB.RecordSet"); 10. rs.Open (sql, Conn, 2,3); 11. 12. out += '<form action="' + insert_record_script + '">'; 13. out += '<input type="submit" name="toss" value="Create New Record">'; 14. out += '<table>'; 15. 16. // loop through form fields and create a form based on fields we found 17. // in recordset. 18. for ( field_number = 0; field_number < rs.fields.count ; field_number++ ){ 19. 20. temp_field = String(rs.fields(field_number).Name); 21. 22. // we can get rid of "data_" on labels, but not in the form fields 23. temp_field_label = temp_field.replace(/data_/,""); 24. 25. // uid field should not be editable: 26. if (temp_field.search(/uid/) > 0){ 27. // do nothing! 28. } 29. 30. // category needs a scroll box: 31. else if (temp_field.search(/data_category/) >= 0){ 32. out += ' n n<tr><td>'+temp_field_label+ '</td><td>'; 33. out += ' n<select name="' + temp_field + '">'; 34. for (i=0; i < cat_object.qty ; i++){ 35. out += ' n<option value="' + cat_object[i].uid + '"'; 36. if (current_category == cat_object[i].uid) { out += ' selected ' } ; 37. out += '>' + cat_object[i].name; 38. } 39. out += ' n</select><br></td></tr> n n'; 40. } 41. 42. // subcategory also needs a scroll box: 43. else if (temp_field.search(/data_subcategory/) >= 0){ 44. sql = "SELECT * FROM subcategories where category = " + current_category; 45. rs2 = Server.CreateObject("ADODB.RecordSet"); 46. rs2.Open (sql, Conn, 2,3); 47. 48. if (rs2.EOF){ out += "<tr><td>error: no subcategories exist for this category!</td><td></td></tr>"; } 49. else{ // we have subcategories 50. out += ' n n<tr><td>'+temp_field_label +'</td><td>'; 51. out += ' n<select name="' + temp_field+ '">'; 52. while (! (rs2.EOF)){ 53. out += ' n<option value="' + String(rs2.fields.item ("subcategory")) + '">' + String(rs2.fields.item ("subcategory")); 54. rs2.move(1); 55. } // end while rs2 56. out += ' n</select></td></tr> n n'; 57. } // end else (rs2 not empty) 58. rs2.Close (); 59. } // end subcategory scroll box 60. 61. // <textarea> for description field 62. else if (temp_field.search(/description/) >= 0){ 63. out += ' n n<tr><td>' + temp_field_label + ': </td><td><textarea name="' + temp_field + '" cols=50 rows=3> </textarea></td></tr>'; 64. } // end description field 65. 66. // standard text box 67. else { 68. out += ' n n<tr><td>' + temp_field_label + ': </td><td><input type=text name="' + temp_field + '" value="" size=60></td></tr>'; 69. } 70. 71. } // end of looping through incoming form fields 72. rs.Close (); 73. 74. out += '</table>'; 75. out += '<input type="hidden" name="current_category" value="'+ current_category +'">'; 76. out += '<input type="submit" name="toss" value="Create New Record">'; 77. out += '</form>'; 78. 79. %> 80. 81. <% = out %> 82. 83. <% 84. Conn.Close (); 85. %> 86. </body> 87. </html>
How the Script Works
If you compare this script to ch4_edit_record_form.asp, you'll see that it's almost the same, except that, because it creates a blank form, it doesn't put any values into any of the fields. Because ch4_edit_record_form.asp was discussed at length just a few pages ago I'll make this discussion very brief, and focus primarily on what's different about this script.
The basic idea of the script is simple: create a recordset that includes all the fields from the data table and build a form based on those fields so that a user can type in information that will then be stored in that table. By generating the form dynamically from the table, it makes it fairly easy to insert the information dynamically into the table (in SQL, records are created using insert statements).
1–7. Unless you've just opened the book to this page, you're probably familiar with this stuff. [7]
[7] If you did just open the book, take a look at similar scripts in Chapter 3.
8. The SQL statement created on this line is interesting, in that it is guaranteed to create an empty recordset. We can be sure of this because the data_uid field is provisioned by MS Access, which will use integers starting with the number 1. Getting an empty recordset is fine, as far as this script goes, because all we care about is the name of all the fields in the data table, which we can obtain even if the recordset is empty. Although performance is not likely to be a big issue with this database, which is organized only to deal with relatively small numbers of records, it's nevertheless nice to keep the script's overhead down, and one would hope that an empty recordset would incur less overhead than a bigger recordset (you never really know without testing, and I haven't).
9,10. Create recordset rs.
12–14. Start form, Create New Record button, and start table.
18. Beginning of for loop that loops through fields in the recordset, accessed through the rs.fields object.
20. Create temp_field variable that contains the name of a different field each time the script iterates through the for() loop.
23. Create human-readable label temp_field_label.
26–28. Script won't create form field for data_uid field, which is set by database.
31–40. These lines build a scroll box that contains a list of categories pulled out of cat_object.
43–59. These lines build a subcategory scroll box, using a second recordset, rs2.
62–64. The data_description field gets a <textarea> tag.
67–69. All the other fields get standard text boxes (<input type=text …> tags).
74–77. Close table, pass the current_category variable to the next script, build a Create New Record button, and close the <form> tag.
81. Send the out string to the browser.
83–87. Wrap things up.
Script 4-14 ch4_insert_record.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <% 5. fields = ""; 6. values = ""; 7. 8. // loop through incoming form fields to build sql statements 9. for ( field_number = 1; field_number <= Request.QueryString.count ; field_number++ ) 10. { 11. temp_field = String(Request.QueryString.Key(field_number)); 12. temp_content = String(Request.QueryString.item(temp_field)) 13. 14. if (temp_field.search(/toss|current_category/) < 0 ){ 15. 16. fields += ' ' + temp_field + ','; 17. 18. // check for null values 19. if (temp_content == "null" || temp_content == "") 20. { 21. values += " null,"; 22. } 23. 24. // check for int fields 25. else if (temp_field.search (/data_category/) >= 0) 26. { values += " " + temp_content +","; 27. current_category = temp_content; 28. } 29. 30. // this works for non-null char fields 31. else { 32. 33. temp_content = temp_content.replace (/'/g,"'"); 34. values += " '" + temp_content +"',"; 35. } 36. 37. } // end if 38. } // end looping through incoming form fields 39. 40. // remove last character, which is a trailing comma that we don't want 41. values = values.substring(0,values.length-1); 42. fields = fields.substring(0,fields.length-1); 43. 44. insert_sql = "insert into data (" + fields + ") values (" + values + ")"; 45. 46. Conn.Execute(insert_sql); 47. 48. Conn.Close (); 49. 50. Response.Redirect(list_records_script + "?current_category=" + current_category) 51. 52. //Response.Write(list_records_script + "?current_category=" + current_category) 53. //Response.Write(list_records_script + "?current_category=" + current_category) 54. %>
How the Script Works
Once again, a script whose inner workings are eerily reminiscent of earlier scripts. This script is closest to its baby brother, ch4_insert_category.asp, described earlier in the chapter, but also a lot like the update scripts, also discussed earlier.
Briefly, the script collects incoming form information, grabs the fields that should go in the database, creates an SQL insert statement, executes the statement, then forwards the user back to ch4_list_records.asp. Less briefly:
1,2. Declare language and grab include file discussed earlier in this chapter.
5,6. Create two variables, fields and values, that will be used to generate an SQL statement that contains the information typed in by the user in ch4_blank_record_form.asp.
9. Start looping through incoming fields in Request.Que ryString.
11,12. Populate the temporary variables temp_field and temp_content.
14. Use a regular expression to eliminate fields that we're not interested in.
16. Add temp_field to the fields string.
19–22. In the case of null fields and empty strings, the null value is appended to values.
25–28. For data_category, which is an int field, single quotes are not necessary when adding temp_content to values.
31–35. Any other fields will be noted, primarily for their need for single quotes on the outside and no single quotes on the inside. Thus, line 33 gets rid of any of the undesirable kind, and line 34 puts a single quote on either side of temp_content, and a comma on the end, and concatenates the whole thing into values.
37,38. Close a couple of blocks of code.
41,42. Get rid of extra commas that are hanging onto the end of values and fields.
44. At last, insert_sql, the point of the entire exercise, with fields and values in their proper place, so that the resulting SQL statement will be in the form:
insert into data field_1, field_2 values value_1, value_2
46. The SQL statement is delivered to the database.
48. The database connection is closed.
50. The user is redirected to ch4_list_records.asp or whatever URL is assigned to list_records_script in the include file.
52,53. A couple of lines that may come in handy if you ever need to debug this script.
Code to Edit Subcategories
As Figure 4-10 implies, when a user clicks on the Edit Subcategory button on ch4_list_records.asp, they are taken to ch4_edit_subcategories_form.asp, where they can create new subcategories (ch4_insert_subcategory.asp does the work) or delete existing ones (ch4_de lete_sub category.asp). There is no mechanism to edit existing subcategories, short of deleting an existing subcategory and creating a new one. If you've read this far, perhaps you'll be tempted to figure out how to do it yourself. :)
Figure 4-10. Three scripts used to edit subcategories
Script 4-15 ch4_edit_subcategory_form.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <html><head><title>Edit Subcategories</title></head><body> 5. 6. <% = nav_header %> 7. <center><font size="+2">Edit Subcategories </font><br></center><hr> 8. You can use this form to delete existing subcategories or to create new ones. 9. 10. <% 11. sql = "SELECT categories.category_uid, categories.category_name, subcategories.subcategory FROM categories INNER JOIN subcategories ON categories.category_uid = subcategories.category ORDER BY categories.category_name" 12. rs = Server.CreateObject("ADODB.RecordSet"); 13. rs.Open (sql, Conn); 14. if (rs.EOF){ // check to see if rs is empty 15. out += "no subcategories found. "; 16. } 17. 18. else{ // rs not empty 19. category_header = "" 20. while (! rs.EOF){ 21. temp_category = String(rs.fields.item("category_uid")) 22. temp_category_name = String(rs.fields.item("category_name")) 23. temp_subcategory = String(rs.fields.item("subcategory")) 24. 25. // categories: when we get to a new one, display it 26. if (category_header != temp_category_name){ 27. category_header = temp_category_name; 28. %> 29. <div align="center"><b><% = category_header %></b></div> 30. <% 31. } 32. %> 33. <br><b><% = temp_subcategory %></b> 34. <a href="<% = delete_subcategory_script %>?current_category=<% = temp_category %>¤t_subcategory=<% = temp_subcategory %>"> 35. <font size="-1" face="arial,helvetica">Delete</font></a> 36. <% 37. rs.move(1); 38. } // end while 39. } // end else 40. %> 41. 42. <hr> 43. To create a new subcategory, select a category and type in the name of the subcategory you want to create: 44. <form action="<% = insert_subcategory_script %>"> 45. <select name="current_category"> 46. <% for (i=0; i < cat_object.qty ; i++){ %> 47. <option value="<% = cat_object[i].uid %>"><% = cat_object[i].name %> 48. <%} %> 49. </select></td></tr> 50. Subcategory<input type=text name=current_subcategory value=""> 51. <input type=submit name=toss value="Create New Subcategory"> 52. </form> 53. 54. <% 55. Conn.Close (); 56. %> 57. </body></html>
How the Script Works
This script creates a Web page (Figure 4-11) that lists all of the subcategories for all of the categories and makes it easy to delete subcategories or to create new ones.
Figure 4-11. The edit subcategory page
Note that there is no way to edit subcategories. Subcategories are stored in each record as strings, rather than using an id, so editing a subcategory would mean not only changing the value of a subcategory name in the subcategories table but also changing the name of that subcategory every time it occurs in a record in the data table. This is perfectly doable, but it's not what this script does, because doing it this way is simpler, albeit less powerful.
The first half of the script lists all of the subcategories that already exist in the database and creates links that can be used to delete subcategories that are no longer useful.
The second half of the script creates a form that can be used to create new subcategories.
1. Set language to JavaScript.
2. Grab the include file described earlier in this chapter.
6. Drop navigation header created in the include file at the top of the page.
8. You might want to add more documentation here if you find that people are not figuring out how to use this page.
11. This SQL statement is a bit of a mouthful, but it makes sense once you look at it: To organize subcategories by category, the script needs to know which category a subcategory is in. Because category names are stored in the categories table (only category uids are stored in the subcategories table), it's necessary to do an inner join on the two tables to create a recordset that will look like this (only the first few records are shown):
Category_uid category_name subcategory 1 People Employees 1 People Board of Directors 1 People Sales Reps 2 Events Rock 2 Events Easy listening 3 Web sites Industry Sites 3 Web sites Competitors Kind of neat how everything in SQL looks like a table, isn't it?
12,13. Create a recordset called rs, based on the above sql statement.
14–16. If the recordset is empty, in which case rs.EOF will be true, generate a graceful error message. Otherwise, execute lines 18–39.
19. Create an empty string called category_header. This will be used to see whether any given record is in the same category as the previous record. If not, the script will decide that we're dealing with a new category and will display the name of the category.
20–38. This while() loop goes through the recordset one line at a time. Each record will be added to the HMTL output by the script. If appropriate, a new category heading will be created, as well.
21–23. Three variables are created to collect the three fields in the recordset shown in the table above.
26–31. These lines deal with deciding whether to create a category heading (see table above to see how subcategories are grouped by category).
26. Checks to see whether the category_header variable created in line 19 is not equal to temp_category_name, which contains the category associated with the current subcategory record. If they are the same, nothing happens. If they are different (which will be the case for the first record and any time the category changes—remember that the subcategories in the recordset are ordered by category), lines 27–30 are executed.
27. This line resets category_header to a new value—the value of the current category (temp_category_name).
28–30. These lines close the ASP tags and create the HTML for the category heading, which is simply the name of the new category, centered on the page. Once again, evidence that I am not a designer.
33–36. Regardless of whether a heading is created above, every subcategory in the recordset gets added to the page being created. This is where it happens. Once again, the ASP tags are closed, because there's a fair amount of HTML involved.
The only tricky part here is the "Delete" link that is created on lines 34 and 35. This includes a link to the delete_subcategory_script that includes the form variables current_category and current_subcategory. The latter will be used to figure out which subcategory to delete by the script, which is described later in this chapter.
37. Important line: Never forget to go to the next record when using a while() loop to iterate through a recordset. Otherwise, it takes a very long time.
Then there's some html…
44. This is the first line of the form that collects the information required to create a new subcategory record. As the action attribute suggests, the new subcategory will be created by the insert_subcategory_script, described next.
45–49. These lines create a select box by looping through the cat_object object created in the include file that knows all there is to know about categories. Note that the value of each item in the select list is set to the uid of a category while the human-readable name is displayed for public consumption.
50. Every subcategory must have a name, and a name every subcategory shall have. This is where it all starts.
51. Yet another Submit button called toss, so that it can easily be edited without affecting the functionality of anything. Customers are always weirdly picky about what buttons say. Maybe because it gives them the illusion of control. <Existential Moment>
55. Good practice to close the recordset, even if IIS will do it for us.
Script 4-16 ch4_insert_subcategory.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <% 5.insert_sql = "insert into subcategories (category,subcategory) values (" + current_category + ", '" + current_subcategory + "')"; 6. 7. Conn.Execute(insert_sql); 8. 9. Conn.Close (); 10. 11. Response.Redirect(edit_subcategories_form_script) 12. //Response.Write("<hr> n n n" + insert_sql + " n n n<hr>") 13. //Response.Write("redirect: "+ edit_subcategories_form_script) 14. %>
How the Script Works
Aren't short yet useful scripts the best? As the title indicates, this one creates a new subcategory, based on information collected in ch4_edit_subcategories_form.asp, discussed above.
1. Language set to JavaScript.
2. Grab the include file, which creates the database connection Conn as well as a couple of variables that we'll use.
5. This is the SQL statement that will create a new subcategory record by adding two fields, category and subcategory, to the subcategories database. Both fields are passed to the script as form variables and set as the local variables current_category and current_subcategory by the include file.
7. Execute the SQL statement created in line 5.
8. Close the database connection that was created in the include file.
11. That's it. This script produces no output. Instead, it redirects users to the edit_subcategories_form_script. On good days, the subcategory that they just tried to create is there, waiting to be used.
Script 4-17 ch4_delete_subcategory.asp
1. <%@ Language=JavaScript %> 2. <!-- #include file="ch4_include.js" --> 3. 4. <% 5. delete_sql = "delete from subcategories where category = " + current_category + " and subcategory = '" + current_subcategory+ "'"; 6. 7. Conn.Execute(delete_sql); 8. 9. Conn.Close (); 10. 11. Response.Redirect(edit_subcategories_form_script) 12. 13. //Response.Write("<hr> n n n" + delete_sql + " n n n<hr>") 14. //Response.Write("redirect: "+ edit_subcategories_form_script) 15. %>
How the Script Works
A mildly destructive script designed to delete a single record from the subcategories table when someone clicks on a delete link in ch4_edit_subcategories_form.asp.
1. Set JavaScript as the scripting language.
2. Grab the include file, which creates the database connection Conn and the variables current_category and current_subcategory.
5. This line creates the SQL statement that is designed to delete a single record from the subcategories table. Note that the where clause of the statement specifies a value for both the category field and the subcategory field. Because subcategories are category-specific, this is required to prevent deleting more than one subcategory if the same subcategory name is assigned to more than one category.
7. This is where the delete statement actually gets executed.
9. This is where the database connection is closed.
10. This is where the user gets redirected to ch4_edit_subcategories_form.asp.