WEB AND SOFTWARE APPLICATION DEVELOPER CALIFORNIA STATE UNIVERSITY, LONG BEACH US-CA-Long Beach Post A Job | Post A Resume

This article is the second part in a 4 part series. The series will be a complete tutorial of creating your own search engine and web portal called - Hooray! I will be discussing the following techniques:

1. Sorting and Searching Database Tables
2. Adding, Deleting, and Updating Records in a Database
3. Creating a Customized homepage (using a database to store preferences)
4. Putting It All Together (combines all previous articles as well as others to create Hooray!)

Another feature of Hooray! will be the ability for visitors to add their site to the search engine. Hoping that your search engine will receive many submissions, we also want a way for administration to edit the sites added. Not only to delete unwanted sites from the search engine, but also to change contact or URL information if a registered user requests it.

For these examples, we will be using the same database we used in Part 1 (companyx.mdb). Don't worry, in Part 4 we will be building Hooray! with the final database and putting everything together in a nice, neat package.

Lets start be creating 3 pages; veiw.asp, edit.asp, and add.asp. The view.asp page is for the administrators, and lists every entry in the database. It is here that the administrator can select which action to take with each entry (edit or delete). The edit.asp page is a quick form that displays the contents of the selected record and allows the administrator to change any or all of the information. The add.asp page will contain a blank form that allows the visitors (and administrators) to add sites to the database. It is assumed that for both the veiw.asp and edit.asp pages you will want some kind of password protection, I will cover that topic in Part 4.

OK, now for veiw.asp. We will be using the exact connection strings used in Part 1 for out Access Database connection, with one exception, we want to change it from a "read only" connection to a "Pessimistic" connection. We do this by simply changing one number in the code below:

RecordSet.Open cmdDC, , 0, 1

gets changed to:

RecordSet.Open cmdDC, , 0, 2

In this example, I will be showing you two different ways to capture the action to be used. The first one uses text links labeled "Edit" and "Delete". The second example uses command buttons. Both examples pass the record ID # through a QueryString. I do it this way to save on Session Variables.

Text Links:

<table>
<tr>
<td>ID</td>
<td>Name</td>
<td>Title</td>
<td>Department</td>
<td>Action</td>
</tr>

<%
'-- Go to the first record

If Not RecordSet.BOF Then
RecordSet.MoveFirst

'-- Loop through records until we are at the end

Do Until RecordSet.EOF

'-- Display the fields

Response.Write("<tr>" & Chr(13))
Response.Write(" <td>" & RecordSet.Fields("ID") & "</td>" & Chr(13))
Response.Write(" <td>" & RecordSet.Fields("Name") & "</td>" & Chr(13))
Response.Write(" <td>" & RecordSet.Fields("Title") & "</td>" & Chr(13))
Response.Write(" <td>" & RecordSet.Fields("Department") & "</td>" & Chr(13))

'-- display the edit and delete links

Response.Write(" <td><a href='edit.asp?ID=" & RecordSet.Fields("ID") & "'>Edit</a> / <a href='view.asp?Action=Delete&ID=" & RecordSet.Fields("ID") & "'>Delete</a></td>" & Chr(13))
Response.Write("</tr>" & Chr(13))

RecordSet.MoveNext
Loop
End If
%>
</table>

You will notice that the action links are as follows:

<a href='edit.asp?ID=" & RecordSet.Fields("ID") & "'>Edit</a>
<a href='view.asp?Action=Delete&ID=" & RecordSet.Fields("ID") & "'>Delete</a>

Because it gets sent to the same page we are on, lets start with the delete link. We need to first see if the Action has been sent as "Delete", and then get the record ID from the QueryString:

If Request.QueryString("Action") = "Delete" Then
If Request.QueryString("ID") <> "" Then

RecordToDelete = CInt(Request.QueryString("ID"))

'--Move to first record

If Not RecordSet.BOF Then
RecordSet.MoveFirst

'--Find the record we want to delete

Do Until RecordSet.Fields("ID") = RecordToDelete

RecordSet.MoveNext
Loop

RecordSet.Delete
RecordSet.Requery
End If
End If
End If

You will notice that I use a Do...Loop to "scroll" through each record until I find the one that matched my ID. Once found, we simply delete it.

Now onto Command buttons. very similar idea, but instead of checking if the Action in the QueryString is sent as "Delete", we simply check to see if the Delete button has been clicked. Lets start with displaying the table:

<form action="view.asp" method="post">

<table>
<tr>

<td>ID</td>
<td>Name</td>
<td>Title</td>
<td>Department</td>
<td>Select</td>

</tr>

<% '-- Go to the first record

If Not RecordSet.BOF Then
RecordSet.MoveFirst

'-- Loop through records until we are at the end

Do Until RecordSet.EOF

'-- Display the fields

Response.Write("<tr>" & Chr(13))
Response.Write(" <td>" & RecordSet.Fields("ID") & "</td>" & Chr(13))
Response.Write(" <td>" & RecordSet.Fields("Name") & "</td>" & Chr(13))
Response.Write(" <td>" & RecordSet.Fields("Title") & "</td>" & Chr(13))
Response.Write(" <td>" & RecordSet.Fields("Department") & "</td>" & Chr(13))
Response.Write(" <td><input type='radio' name='rdoSelect' value='" & RecordSet.Fields("ID") & "'></td>" & Chr(13))
Response.Write("</tr>" & Chr(13))

RecordSet.MoveNext
Loop
End If
%>

</table>

<input type="submit" name="btnEdit" value="Edit">  
<input type="submit" name="btnDelete" value="Delete">

</form>

I use a radio input type to grab the record ID. Now lets delete the record:

If Request.Form("btnDelete") = "Delete" Then

RecordToDelete = CInt(Request.Form("rdoSelect"))

If Not RecordSet.BOF Then
RecordSet.MoveFirst

Do Until RecordSet.Fields("ID") = RecordToDelete

RecordSet.MoveNext
Loop
RecordSet.Delete
RecordSet.Requery
End If
End If

Very similar to the link method, but we grab the ID from the radio button rather than the QueryString. You will want to add a quick confirmation to the delete action so that there is no accidents (this you will have to figure out on your own).

Moving on to the edit section, we also grab the record ID from a QueryString in both the link and button examples. For the link example, we simply send the user to the edit.asp page and add on a QueryString called "ID". With the command button, we do the same thing, we just have to add an additional step:

If Request.Form("btnEdit") = "Edit" Then
Response.Redirect("edit.asp?ID=" & Request.Form("rdoSelect"))
End If

The SQL on this page is a little different from the veiw.asp page. We only want to display the selected record, rather than all of them. I suggest first checking if the QueryString contains a value, and then "plugging" it into your SQL:

If Request.QueryString("ID") <> "" Then
RecordToEdit = Request.QueryString("ID")

SQL = "SELECT tblEmployees.ID, tblEmployees.* FROM tblEmployees WHERE (((tblEmployees.ID)=" & Request.QueryString("ID") & "));"

End If

If the QueryString does not contain a value, you can either display the first record, or send the user back to the veiw.asp page. Now that we have the record we want to edit, lets display our table:

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>Name:</td>
<td><input type="text" name="txtName" size="40" value="<%= RecordSet.Fields("Name") %>"></td>
</tr>
<tr>
<td>Title:</td>
<td><input type="text" name="txtTitle" size="40" value="<%= RecordSet.Fields("Title") %>"></td>
</tr>
<tr>
<td>Department:</td>
<td><input type="text" name="txtDepartment" size="40" value="<%= RecordSet.Fields("Department") %>"></td>
</tr>
</table>

To allow the user to update the information, simply place a form on this page with a button called "btnUpdate", when the user click the button, execute the following code:

If Request.Form("btnUpdate") = "Update" Then

RecordSet.Fields("Name") = Request.Form("txtName")
RecordSet.Fields("Title") = Request.Form("txtTitle")
RecordSet.Fields("Department") = Request.Form("txtDepartment")

RecordSet.Update
Updated = "True"

End If

I use the "Updated" variable to display a friendly message alerting the user that the record has indeed been updated:

<% If Updated = "True" Then %>

<b><%= RecordSet.Fields("Name") %></b> has been updated.<P>

<% End If %>

That covers out editing (or updating) and deleting records, so lets move onto adding records. Adding records to the table is VERY similar to editing them, with one key difference:

RecordSet.AddNew

We also do not need to grab a record ID frokm a QueryString because we are not editing a specific record. Here is the code for my entire add.asp page:

<%
Session("DatabasePath") = "C:\WINNT\Profiles\cmiller\Desktop\hooray\companyx.mdb"

'-- Declare your variables

Dim DataConnection, cmdDC, RecordSet
Dim RecordToEdit, Updated

'-- Create object and open database

Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Session("DatabasePath") & ";"

Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection

'-- default SQL

SQL = "SELECT * FROM tblEmployees"

cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")

'-- Cursor Type, Lock Type

'-- ForwardOnly 0 - ReadOnly 1
'-- KeySet 1 - Pessimistic 2
'-- Dynamic 2 - Optimistic 3
'-- Static 3 - BatchOptimistic 4

RecordSet.Open cmdDC, , 0, 2

If Request.Form("btnAdd") = "Add" Then

RecordSet.AddNew

RecordSet.Fields("Name") = Request.Form("txtName")
RecordSet.Fields("Title") = Request.Form("txtTitle")
RecordSet.Fields("Department") = Request.Form("txtDepartment")

RecordSet.Update
Added = "True"

End If
%>

<form action="add.asp" method="post">

<% If Added = "True" Then %>

<b><%= Request.Form("txtName") %></b> has been Added.<P>

<% End If %>

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>Name:</td>
<td><input type="text" name="txtName" size="40" value=""></td>
</tr>
<tr>
<td>Title:</td>
<td><input type="text" name="txtTitle" size="40" value=""></td>
</tr>
<tr>
<td>Department:</td>
<td><input type="text" name="txtDepartment" size="40" value=""></td>
</tr>
</table>

<p>

<input type="submit" name="btnAdd" value="Add">

</form>

<%
RecordSet.Close
Set RecordSet = Nothing

Set cmdDC = Nothing
DataConnection.Close
Set DataConnection = Nothing
%>

You may want to add some kind of error checking for the fields. For example, you will want to check for blank fields, valid email addresses, valid zip codes, etc. This can be accomplished with a series of If...Then statements. It may make for an interesting article a later date (hint, hint).

That is all there really is to it! Be sure to keep your eyes open for Part 3, Creating a Customized homepage!

Enjoy!

Attachments:

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info

Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers