ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 5) - Direct Data Access - Updating Data

5.5. Updating Data

Now that you understand how to retrieve data, it isn't much more complicated to perform simple insert, update, and delete operations. Once again, you use the Command object, but this time you don't need a DataReader because no results will be retrieved. You also don't use a SQL Select command. Instead, you use one of three new SQL commands: Update, Insert, or Delete.

To execute an Update, Insert, or Delete statement, you need to create a Command object. You can then execute the command with the ExecuteNonQuery() method. This method returns the number of rows that were affected, which allows you to check your assumptions. For example, if you attempt to update or delete a record and are informed that no records were affected, you probably have an error in your Where clause that is preventing any records from being selected. (If, on the other hand, your SQL command has a syntax error or attempts to retrieve information from a nonexistent table, an exception will occur.)

5.5.1. Displaying Values in Text Boxes

Before you can update and insert records, you need to make a change to the previous example. Instead of displaying the field values in a single, fixed label, you need to show each detail in a separate text box. Figure 12 shows the revamped page. It includes two new buttons that allow you to update the record (Update) or delete it (Delete), and two more that allow you to begin creating a new record (Create New) and then insert it (Insert New).

Figure 12. A more advanced author manager

The record selection code is identical from an ADO.NET perspective, but it now uses the individual text boxes:

Protected Sub lstAuthor_SelectedIndexChanged(ByVal sender As Object, _
  ByVal e As EventArgs) Handles lstAuthor.SelectedIndexChanged

    ' Create a Select statement that searches for a record
    ' matching the specific author ID from the Value property.
    Dim selectSQL As String
    selectSQL = "SELECT * FROM Authors "
    selectSQL &= "WHERE au_id='" & lstAuthor.SelectedItem.Value & "'"

    ' Define the ADO.NET objects.
    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(selectSQL, con)
    Dim reader As SqlDataReader

' Try to open database and read information.
    Try
        con.Open()
        reader = cmd.ExecuteReader()
        reader.Read()

        ' Fill the controls.
        txtID.Text = reader("au_id").ToString()
        txtFirstName.Text = reader("au_fname").ToString()
        txtLastName.Text = reader("au_lname").ToString()
        txtPhone.Text = reader("phone").ToString()
        txtAddress.Text = reader("address").ToString()
        txtCity.Text = reader("city").ToString()
        txtState.Text = reader("state").ToString()
        txtZip.Text = reader("zip").ToString()
        chkContract.Checked = CType(reader("contract"), Boolean)
        reader.Close()
        lblResults.Text = ""

    Catch err As Exception
        lblResults.Text = "Error getting author. "
        lblResults.Text &= err.Message
    Finally
        con.Close()
    End Try
End Sub

					  

If you play with the example at length, you'll notice that it lacks a few niceties that would be needed in a professional website. For example, when creating a new record, the name of the last selected user is still visible, and the Update and Delete buttons are still active, which can lead to confusion or errors. A more sophisticated user interface could prevent these problems by disabling inapplicable controls (perhaps by grouping them in a Panel control) or by using separate pages. In this case, however, the page is useful as a quick way to test some basic data access code.

5.5.2. Adding a Record

To start adding a new record, click Create New to clear all the text boxes. Technically this step isn't required, but it simplifies the user's life:

Protected Sub cmdNew_Click(ByVal sender As Object, _
  ByVal e As EventArgs) Handles cmdNew.Click

    txtID.Text = ""
    txtFirstName.Text = ""
    txtLastName.Text = ""
    txtPhone.Text = ""
    txtAddress.Text = ""
    txtCity.Text = ""
    txtState.Text = ""
    txtZip.Text = ""
    chkContract.Checked = False

lblResults.Text = "Click Insert New to add the completed record."
End Sub

The Insert New button triggers the ADO.NET code that inserts the finished record using a dynamically generated Insert statement:

Protected Sub cmdInsert_Click(ByVal sender As Object, _
  ByVal e As EventArgs) Handles cmdInsert.Click

    ' Perform user-defined checks.
    ' Alternatively, you could use RequiredFieldValidator controls.
    If txtID.Text = "" Or txtFirstName.Text = "" Or txtLastName.Text = "" Then
        lblResults.Text = "Records require an ID, first name, and last name."
        Return
    End If

    ' Define ADO.NET objects.
    Dim insertSQL As String
    insertSQL = "INSERT INTO Authors ("
    insertSQL &= "au_id, au_fname, au_lname, "
    insertSQL &= "phone, address, city, state, zip, contract) "
    insertSQL &= "VALUES ('"
    insertSQL &= txtID.Text & "', '"
    insertSQL &= txtFirstName.Text & "', '"
    insertSQL &= txtLastName.Text & "', '"
    insertSQL &= txtPhone.Text & "', '"
    insertSQL &= txtAddress.Text & "', '"
    insertSQL &= txtCity.Text & "', '"
    insertSQL &= txtState.Text & "', '"
    insertSQL &= txtZip.Text & "', '"
    insertSQL &= Val(chkContract.Checked) & "')"

    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(insertSQL, con)

    ' Try to open the database and execute the update.
    Dim added As Integer = 0
    Try
        con.Open()
        added = cmd.ExecuteNonQuery()
        lblResults.Text = added.ToString() & " records inserted."
    Catch err As Exception
        lblResults.Text = "Error inserting record. "
        lblResults.Text &= err.Message
    Finally
        con.Close()
    End Try

    ' If the insert succeeded, refresh the author list.
    If added > 0 Then
        FillAuthorList()
    End If
End Sub

					  

If the insert fails, the problem will be reported to the user in a rather unfriendly way (see Figure 13). This is typically the result of not specifying valid values. If the insert operation is successful, the page is updated with the new author list.

Figure 13. A failed insertion

NOTE

In a more polished application, you would use validators and provide more useful error messages. You should never display the detailed database error information shown in Figure 13, because it could give valuable information to malicious users.

5.5.3. Creating More Robust Commands

The previous example performed its database work using a dynamically pasted-together SQL string. This off-the-cuff approach is great for quickly coding database logic, and it's easy to understand. However, it has two potentially serious drawbacks:

• Users may accidentally enter characters that will affect your SQL statement. For example, if a value contains an apostrophe ('), the pasted-together SQL string will no longer be valid.

• Users might deliberately enter characters that will affect your SQL statement. Examples include using the single apostrophe to close a value prematurely and then following the value with additional SQL code.

The second of these is known as SQL injection attack, and it facilitates an amazingly wide range of exploits. Crafty users can use SQL injection attacks to do anything from returning additional results (such as the orders placed by other customers) or even executing additional SQL statements (such as deleting every record in another table in the same database). In fact, SQL Server includes a special system stored procedure that allows users to execute arbitrary programs on the computer, so this vulnerability can be extremely serious.

You could address these problems by carefully validating the supplied input and checking for dangerous characters such as apostrophes. One approach is to sanitize your input by doubling all apostrophes in the user input (in other words, replace ' with "). Here's an example:

Dim authorID As String = txtID.Text.Replace("'", "''")

A much more robust and convenient approach is to use a parameterized command. A parameterized command is one that replaces hard-coded values with placeholders. The placeholders are then added separately and automatically encoded.

For example, this SQL statement:

SELECT * FROM Customers WHERE CustomerID = 'ALFKI'

would become this:

SELECT * FROM Customers WHERE CustomerID = @CustomerID

You then need to add a Parameter object for each parameter in the Command.Parameters collection.

The following example rewrites the insert code of the author manager example with a parameterized command:

Protected Sub cmdInsert_Click(ByVal sender As Object, _
  ByVal e As EventArgs) Handles cmdInsert.Click

    ' Perform user-defined checks.
    If txtID.Text = "" Or txtFirstName.Text = "" Or txtLastName.Text = "" Then
        lblResults.Text = "Records require an ID, first name, and last name."
        Return
    End If

    ' Define ADO.NET objects.
    Dim insertSQL As String
    insertSQL = "INSERT INTO Authors ("
    insertSQL &= "au_id, au_fname, au_lname, "

					  

insertSQL &= "phone, address, city, state, zip, contract) "
    insertSQL &= "VALUES ("
    insertSQL &= "@au_id, @au_fname, @au_lname, "
    insertSQL &= "@phone, @address, @city, @state, @zip, @contract)"

    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(insertSQL, con)

    ' Add the parameters.
    cmd.Parameters.AddWithValue("@au_id", txtID.Text)
    cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text)
    cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text)
    cmd.Parameters.AddWithValue("@phone", txtPhone.Text)
    cmd.Parameters.AddWithValue("@address", txtAddress.Text)
    cmd.Parameters.AddWithValue("@city", txtCity.Text)
    cmd.Parameters.AddWithValue("@state", txtState.Text)
    cmd.Parameters.AddWithValue("@zip", txtZip.Text)
    cmd.Parameters.AddWithValue("@contract", chkContract.Checked)

    ' Try to open the database and execute the update.
    Dim added As Integer = 0
    Try
        con.Open()
        added = cmd.ExecuteNonQuery()
        lblResults.Text = added.ToString() & " record inserted."
    Catch err As Exception
        lblResults.Text = "Error inserting record. "
        lblResults.Text &= err.Message
    Finally
        con.Close()
    End Try

    ' If the insert succeeded, refresh the author list.
    If added > 0 Then
        FillAuthorList()
    End If
End Sub

					  

Now that the values have been moved out of the SQL command and to the Parameters collection, there's no way that a misplaced apostrophe or scrap of SQL can cause a problem.

NOTE

For basic security, always use parameterized commands. Many of the most infamous attacks on e-commerce websites weren't fueled by hard-core hacker knowledge but were made using simple SQL injection by modifying values in web pages or query strings.

5.5.4. Updating a Record

When the user clicks the Update button, the information in the text boxes is applied to the database as follows:

Protected Sub cmdUpdate_Click(ByVal sender As Object, _
  ByVal e As EventArgs) Handles cmdUpdate.Click

    ' Define ADO.NET objects.
    Dim updateSQL As String
    updateSQL = "UPDATE Authors SET "
    updateSQL &= "au_fname=@au_fname, au_lname=@au_lname, "
    updateSQL &= "phone=@phone, address=@address, city=@city, state=@state, "
    updateSQL &= "zip=@zip, contract=@contract "
    updateSQL &= "WHERE au_id=@au_id_original"

    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(updateSQL, con)

    ' Add the parameters.
    cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text)
    cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text)
    cmd.Parameters.AddWithValue("@phone", txtPhone.Text)
    cmd.Parameters.AddWithValue("@address", txtAddress.Text)
    cmd.Parameters.AddWithValue("@city", txtCity.Text)
    cmd.Parameters.AddWithValue("@state", txtState.Text)
    cmd.Parameters.AddWithValue("@zip", txtZip.Text)
    cmd.Parameters.AddWithValue("@contract", chkContract.Checked)
    cmd.Parameters.AddWithValue("@au_id_original", lstAuthor.SelectedItem.Value)

    ' Try to open database and execute the update.
    Dim updated As Integer = 0
    Try
        con.Open()
        updated = cmd.ExecuteNonQuery()
        lblResults.Text = updated.ToString() & " record updated."
    Catch err As Exception
        lblResults.Text = "Error updating author. "
        lblResults.Text &= err.Message
    Finally
        con.Close()
    End Try

    ' If the update succeeded, refresh the author list.
    If updated > 0 Then
        FillAuthorList()
    End If
End Sub

					  

5.5.5. Deleting a Record

When the user clicks the Delete button, the author information is removed from the database. The number of affected records is examined, and if the delete operation was successful, the FillAuthorList() function is called to refresh the page.

Protected Sub cmdDelete_Click(ByVal sender As Object, _
  ByVal e As EventArgs) Handles cmdDelete.Click

    ' Define ADO.NET objects.
    Dim deleteSQL As String
    deleteSQL = "DELETE FROM Authors "
    deleteSQL &= "WHERE au_id=@au_id"

    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(deleteSQL, con)
    cmd.Parameters.AddWithValue("@au_id ", lstAuthor.SelectedItem.Value)

    ' Try to open the database and delete the record.
    Dim deleted As Integer = 0
    Try
        con.Open()
        deleted = cmd.ExecuteNonQuery()
        lblResults.Text &= "Record deleted."
    Catch err As Exception
        lblResults.Text = "Error deleting author. "
        lblResults.Text &= err.Message
    Finally
        con.Close()
    End Try

    ' If the delete succeeded, refresh the author list.
    If deleted > 0 Then
        FillAuthorList()
    End If
End Sub

					  

Interestingly, delete operations rarely succeed with the records in the pubs database, because they have corresponding child records linked in another table of the pubs database. Specifically, each author can have one or more related book titles. Unless the author's records are removed from the TitleAuthor table first, the author cannot be deleted. Because of the careful error handling used in the previous example, this problem is faithfully reported in your application (see Figure 14) and doesn't cause any real problems.

Figure 14. A failed delete attempt

To get around this limitation, you can use the Create New and Insert New buttons to add a new record and then delete this record. Because this new record won't be linked to any other records, its deletion will be allowed.

NOTE

If you have a real-world application that needs to delete records and these records might have linked records in a child table, there are several possible solutions. One slightly dangerous option is to configure the database to use cascading deletes to automatically wipe out linked records. Another option is to do the deleting yourself, with additional ADO.NET code. But the best choice is usually not to delete the record at all (after all, you may need it for tracking and reporting later). Instead, use a bit column to keep track of records that shouldn't be displayed, like a Discontinued column in a Products table or a Removed column in the Authors table. You can then add a condition to your Select query so that it doesn't retrieve these records (as in SELECT * FROM Products WHERE Discontinued=0).