Storing myLinks Information in the Database
The links table is pretty simple, so let's take a look at it first. We're going to need the ability to do three things: insert, update, and delete records.
We're going to start by creating a form that will list all of the current records, along with a check box to mark them for deletion. We'll also have a link to a form where we can edit the record and a link that will give us to a blank form we can use to create a new one.
Copy admintemplate.asp to edit_links.asp. Add the code in Listing 8.6.
Code Listing 8.6. edit_links.asp: Deciding which records to edit or delete
On line 10 we create a link to the new record form. We'll put it at the top and the bottom of the page, so it's less likely that our administrator will have to scroll for miles if there are a lot of records in our table. Obviously if there are too many records, we're going to have to manage this in a different way, but the basic idea is the same.
On line 12 we're creating a form that will note which records we want to delete. Displaying the table and the check boxes on lines 23 through 46 is very similar to what we did in display_table.asp. We could hard-code in the names of the fields, but if we change the table definition later, that'll come back to haunt us. The main difference between this and display_table.asp is that we've added a check box for every field on lines 26 and 27, and we've separated out the first column (number 0) on lines 30 through 35 so we can make it a link to an edit page (see Figure 8.2).
Let's look at that first column for a minute. It's link_id, the primary key for this table. That means that if we want to identify a specific record in the table, all we need is that value. We don't have to even know the actual link_name and link_desc. As long as we have the link_id, we can pinpoint which record it is. That's why it's important in your applications to make sure that every table has a primary key.
Note
Even though we haven't been designating them as we've been building them, every table in our project has a primary key. Also, a primary key doesn't have to be just one column. For instance, in our order_items table, the primary key is actually a combination of the order_id and the prod_id.
Now we need a form action to take those check boxes and delete our records. Remember back in Chapter 4, "Database Access Using ASP," when we were comparing the links the user had chosen with the links that were in the table? We're doing the same thing here, only this time we're deleting the record when we find a match. When we're done deleting, we'll want to take the user back to the original form. Copy admintemplate.asp to edit_links_action.asp and add the code in Listing 8.7.
Code Listing 8.7. edit_links_action.asp: Deleting specific records
0: <%@ LANGUAGE="VBSCRIPT" %>
1: <% Response.buffer = true %>
2: <!--#include file="adovbs.inc"-->
3: <!--#include file="pagetop.txt"-->
4: <!--#include file="isadmin.inc"-->
5: <%
6: if isAdmin then
7:
8: set outpostDB = Server.CreateObject("ADODB.Connection")
9: outpostDB.Open "outpost"
10:
11: set deleteSet = Server.CreateObject("ADODB.RecordSet")
12: deleteSet.Open "select * from links order by link_id", _
13: outpostDB, adOpenDynamic, adLockPessimistic, adCmdText
14: p_thisDelete = 1
15: while not deleteSet.EOF
16: if cint(deleteSet("link_id")) = _
17: cint(Request.form("p_delete").item(p_thisDelete)) then
18: deleteSet.Delete
19: deleteSetUpdate
20: if p_thisDelete < Request.form("p_delete").Count then
21: p_thisDelete = p_thisDelete + 1
22: end if
23: end if
24: deleteSet.MoveNext
25: wend
26: deleteSet.Close
27: set deleteSet = Nothing
28:
29: outpostDB.Close
30: set outpostDB = Nothing
31:
32: Response.Clear
33: Response.Redirect "http://localhost/edit_links.asp"
34:else
35:
36: Response.Write "You do not have access to this page."
37:
38:end if
39:%>
40:</BODY>
41:</HTML>
|
In the past, when we were redirecting the user, we took great pains to make sure that we didn't send any text to the browser before we did it; we even went to the point of re-arranging the page. This time, instead of worrying about what we've already sent, we're going to tell the browser to hold all headers and text until the page is completely finished. To do this, on line 1 we tell the Response object to buffer it's output. Essentially, we're telling the Response object not to send anything at all until we tell it to, or until the page is completely finished. Then later, on line 32, just before we do the redirect, we tell it to throw away whatever else it was going to send and do the redirect instead.
Note
This behavior is the default in IIS 5.0, but in previous versions it was not, requiring the developer to set it.
Lines 11 through 27 are similar to what we did in Chapter 4, when we were managing the user's chosen links. We're creating a list of all links and then comparing them, one by one, against those that we've marked for deletion. When we find a record that has been checked off, we delete it and move on.
We could also have looped through the links for deletion and let the RecordSet find each one. Again, there's more than one way to solve this problem.
Now let's look at creating a new record. Copy admintemplate.asp to edit_links_rec.asp and add the code in Listing 8.8.
Code Listing 8.8. edit_links_rec.asp: Adding a record
What we have here is just a form that lets us add a link name, description, and URL. The only tricky part is getting a unique link_id. To do that, we're selecting the maximum existing link_id and adding 1 to it on lines 10 through 19.
Adding the record itself is simple. Copy admintemplate.asp to edit_links_rec_action.asp and add the code in Listing 8.9.
Code Listing 8.9. edit_links_rec_action.asp: Creating the new record
On lines 7 through 10 we get the information from the form and convert it to the proper types because we're going to be putting it into the database. Then on lines 15 through 25 we create a RecordSet for the links table, add a new record, populate it, and close everything up. Nothing new there.
However, you may have noticed that when we created the edit_links.asp page, we were also sending the user to edit_links_rec.asp to change a record, as opposed to just adding a new one.
What we need is for the edit_links_rec.asp form to come up blank if we are creating a new record, but to be prepopulated if we're editing an existing one. Make the changes to edit_links_rec.asp shown in Listing 8.10.
Code Listing 8.10. edit_links_rec.asp: Editing an existing record
What we're doing on lines 20 through 34 is checking to see whether we're supposed to be editing an existing record, and if we are, we're extracting the information from the RecordSet we've already created and populating the form with it. If there is no existing record, the fields will come up blank, just like they did before.
Because we're going to be searching the RecordSet on lines 21 through 23, we've changed it from the default Forward-only cursor you get with an Execute statement to a Static cursor so we can move up the list as well as down.
On line 21, we're checking to see whether there was an existing record submitted. Because we're coming from a link instead of a form, we're using Request. querystring instead of Request.form. Just in case there's no value coming in, we'll call it p_link_id_in so we don't inadvertantly set p_link_id to an empty string.
If we did find a value for p_link_id_in, we're using it to find the appropriate record in the set. Then, whether we find it or not, we're populating variables that we're going to use later. Also, if we did find a value for p_link_id_in, we're using it to replace p_link_id. We'll also set a flag to tell us that we're dealing with an existing record. That way, when we get to the form action, we know whether to update a record or create a new one.
Finally we'll use the new (or blank) values to populate the text boxes.
Now we have a form that will handle both new and existing records. All that's left is to modify the form action to handle the existing records. Go back to edit_links_rec_action.asp and make the changes in Listing 8.11.
Code Listing 8.11. edit_links_rec_action.asp: Saving changes to an existing record
0: <%@ LANGUAGE="VBSCRIPT" %>
1: <% Response.buffer = true%>
2: <!--#include file="adovbs.inc"-->
3: <!--#include file="pagetop.txt"-->
4: <!--#include file="isadmin.inc"-->
5: <%
6: if isAdmin then
7:
8: p_link_id = cint(Request.form("p_link_id"))
9: p_link_name = cstr(Request.form("p_name"))
10: p_link_desc = cstr(Request.form("p_desc"))
11: p_link_url = cstr(Request.form("p_url"))
12: p_existing = cstr(Request.form("p_existing"))
13:
14: set outpostDB = Server.CreateObject("ADODB.Connection")
15: outpostDB.Open "outpost"
16:
17: set insertSet = Server.CreateObject("ADODB.RecordSet")
18: insertSet.Open "links", outpostdb, adOpenDynamic, _
19: adLockOptimistic, adCmdTable
20:
21: if p_existing = "" then
22: insertSet.AddNew
23: else
24: insertSet.Find "link_id =" & p_existing
25: end if
26: insertSet("link_id") = p_link_id
27: insertSet("link_name") = p_link_name
28: insertSet("link_desc") = p_link_desc
29: insertSet("link_url") = p_link_url
30: insertSetUpdate
31: insertSet.Close
32: set insertSet = Nothing
33:
34: outpostDB.Close
35: set outpostDB = Nothing
36:
37: Response.Clear
38: Response.Redirect "http://localhost/edit_links.asp"
39:else
40:
41: Response.Write "You do not have access to this page."
42:
43:end if
44:%>
45:</BODY>
46:</HTML>
|
All we had to do differently with this page was to check and see whether there was an existing record, and if there was, find it instead of creating a new one. That's it! It certainly beats deciding whether to create an insert statement or an update statement and having to maintain them both!