When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Friday, August 27, 1999

Hooray! - Adding, Deleting, and Updating Records in a Database, Part 2
By Christopher Miller


  • Read Hooray!, Part 1

  • 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:

    - continued -

    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!


  • Read Hooray!, Part 1

  • Attachments:

  • The Code for this Article in ZIP Format
  • A live example of this Article!


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article