The following is a typical (and rather inefficient) Select statement for the pubs database. It works with the Authors table, which contains a list of authors:

SELECT * FROM Authors

Here's another example that retrieves a list of author names:

SELECT au_lname, au_fname FROM Authors WHERE State='CA' ORDER BY au_lname ASC

Here's one last example:

SELECT TOP 100 * FROM Sales ORDER BY ord_date DESC

This example uses the Top clause instead of a Where statement. The database rows will be sorted in descending order by order date, and the first 100 matching results will be retrieved. In this case, it's the 100 most recent orders. You could also use this type of statement to find the most expensive items you sell or the best-performing employees.

In many respects, the Where clause is the most important part of the Select statement. You can find records that match several conditions using the And keyword, and you can find records that match any one of a series of conditions using the Or keyword. You can also specify greater-than and less-than comparisons by using the greater-than (>) and less-than (<) operators.

The following is an example with a different table and a more sophisticated Where statement:

SELECT * FROM Sales WHERE ord_date < '2000/01/01' AND ord_date > '1987/01/01'

This example uses the international date format to compare date values. Although SQL Server supports many date formats, yyyy/mm/dd is recommended to prevent ambiguity.

If you were using Microsoft Access, you would need to use the U.S. date format, mm/dd/yyyy, and replace the apostrophes around the date with the number (#) symbol.

The Like operator allows you to perform partial string matching to filter records where a particular field starts with, ends with, or contains a certain set of characters. For example, if you want to see all store names that start with B, you could use the following statement:

SELECT * FROM Stores WHERE stor_name LIKE 'B%'

To see a list of all stores ending with S, you would put the percent sign before the S, like this:

SELECT * FROM Stores WHERE stor_name LIKE '%S'

The third way to use the Like operator is to return any records that contain a certain character or sequence of characters. For example, suppose you want to see all stores that have the word book somewhere in the name. In this case, you could use a SQL statement like this:

SELECT * FROM Stores WHERE stor_name LIKE '%book%'

By default, SQL is not case sensitive, so this syntax finds instances of BOOK, book, or any variation of mixed case.

Finally, you can indicate one of a set of characters, rather than just any character, by listing the allowed characters within square brackets. Here's an example:

SELECT * FROM Stores WHERE stor_name LIKE '[abcd]%'

This SQL statement will return stores with names starting with A, B, C, or D.

The SQL language also defines special aggregate functions. Aggregate functions work with a set of values but return only a single value. For example, you can use an aggregate function to count the number of records in a table or to calculate the average price of a product. Table 14-1 lists the most commonly used aggregate functions.

For example, here's a query that returns a single value—the number of records in the Authors table:

SELECT COUNT(*) FROM Authors

And here's how you could calculate the total quantity of all sales by adding together the qty field in each record:

SELECT SUM(qty) FROM Sales

The connection string is actually a series of distinct pieces of information separated by semicolons (;). Each separate piece of information is known as a connection string property.

The following list describes some of the most commonly used connection string properties, including the three properties used in the preceding example:

You can set some other, lesser-used options for a connection string. For more information, refer to the Visual Studio Help. Look under the appropriate Connection class (such as SqlConnection or OleDbConnection) because there are subtle differences in connection string properties for each type of Connection class.

The previous example uses integrated Windows authentication, which is the default security standard for new SQL Server installations. You can also use SQL Server authentication. In this case, you will explicitly place the user ID and password information in the connection string. However, SQL Server authentication is disabled by default in SQL Server 2000 and later versions, because it's not considered to be as secure.

Here's the lowdown on both types of authentication:

For Windows authentication to work, the currently logged-on Windows user must have the required authorization to access the SQL database. This isn't a problem while you test your websites, because Visual Studio launches your web applications using your user account. However, when you deploy your application to a web server running IIS, you might run into trouble. In this situation, all ASP.NET code is run by a more limited user account that might not have the rights to access the database. Although the exact user depends on your version of IIS (see the discussion in Chapter 26), the best approach is usually to grant access to the IIS_IUSRS group.

Every database server stores a master list of all the databases that you've installed on it. This list includes the name of each database and the location of the files that hold the data. When you create a database (for example, by running a script or using a management tool), the information about that database is added to the master list. When you connect to the database, you specify the database name using the Initial Catalog value in the connection string.

Interestingly, SQL Server Express has a feature that lets you bypass the master list and connect directly to any database file, even if it's not in the master list of databases. This feature is called user instances. Oddly enough, this feature isn't available in the full edition of SQL Server.

To use this feature, you need to set the User Instances value to True (in the connection string) and supply the file name of the database you want to connect to with the AttachDBFilename value. You don't supply an Initial Catalog value.

Here's an example connection string that uses this approach:

myConnection.ConnectionString = "Data Source=localhostSQLEXPRESS;" & _
  "User Instance=True;AttachDBFilename=|DataDirectory|Northwind.mdf;" & _
  "Integrated Security=True"

There's another trick here. The file name starts with |DataDirectory|. This automatically points to the App_Data folder inside your web application directory. This way, you don't need to supply a full file path, which might not remain valid when you move the web application to a web server. Instead, ADO.NET will always look in the App_Data directory for a file named Northwind.mdf.

User instances is a handy feature if you have a web server that hosts many different web applications that use databases and these databases are frequently being added and removed. However, because the database isn't in the master list, you won't see it in any administrative tools (although most administrative tools will still let you connect to it manually, by pointing out the right file location). But remember, this quirky but interesting feature is available in SQL Server Express only—you won't find it in the full version of SQL Server.

VISUAL STUDIO'S SUPPORT FOR USER INSTANCE DATABASES

Visual Studio provides two handy features that make it easier to work with databases in the App_Data folder.

First, Visual Studio gives you a nearly effortless way to create new databases. Simply choose Website

Visual Studio also simplifies your life with its automatic Server Explorer support. When you open a web application, Visual Studio automatically adds a data connection to the Server Explorer window for each database that it finds in the App_Data folder. To jump to a specific data connection in a hurry, just double-click the .mdf file for the database in the Solution Explorer.

Using the Server Explorer, you can create tables, edit data, and execute commands, all without leaving the comfort of Visual Studio. (For more information about executing commands with the Server Explorer, refer to the "SQL Basics" section earlier in this chapter.)

Typically, all the database code in your application will use the same connection string. For that reason, it usually makes the most sense to store a connection string in a class member variable or, even better, a configuration file.

You can also create a Connection object and supply the connection string in one step by using a dedicated constructor:

Dim myConnection As New SqlConnection(connectionString)
' myConnection.ConnectionString is now set to connectionString.

You don't need to hard-code a connection string. The <connectionStrings> section of the web.config file is a handy place to store your connection strings. Here's an example:

<configuration>
  <connectionStrings>
    <add name="Pubs" connectionString=
"Data Source=localhost;Initial Catalog=Pubs;Integrated Security=SSPI"/>
  </connectionStrings>
  ...
</configuration>

You can then retrieve your connection string by name. First, import the System.Web.Configuration namespace. Then, you can use code like this:

Dim connectionString As String = _
  WebConfigurationManager.ConnectionStrings("Pubs").ConnectionString

This approach helps to ensure all your web pages are using the same connection string. It also makes it easy for you to change the connection string for an application, without needing to edit the code in multiple pages. The examples in this chapter all store their connection strings in the web.config file in this way.

Once you've created your connection (as described in the previous section), you're ready to use it.

Before you can perform any database operations, you need to explicitly open your connection:

myConnection.Open()

To verify that you have successfully connected to the database, you can try displaying some basic connection information. The following example writes some basic information to a Label control named lblInfo (see Figure 14-9).

Figure 14.9. Testing your connection

Here's the code with basic error handling:

' Define the ADO.NET Connection object.
Dim connectionString As String = _
  WebConfigurationManager.ConnectionStrings("Pubs").ConnectionString
Dim myConnection As New SqlConnection(connectionString)

Try
    ' Try to open the connection.
    myConnection.Open()
    lblInfo.Text = "<b>Server Version:</b> " & myConnection.ServerVersion
    lblInfo.Text &= "<br /><b>Connection Is:</b> " & _
      myConnection.State.ToString()

Catch err As Exception
    ' Handle an error by displaying the information.
    lblInfo.Text = "Error reading the database."
    lblInfo.Text &= err.Message
Finally
    ' Either way, make sure the connection is properly closed.
    ' (Even if the connection wasn't opened successfully,
    '  calling Close() won't cause an error.)
    myConnection.Close()
    lblInfo.Text &= "<br /><b>Now Connection Is:</b> "
    lblInfo.Text &=  myConnection.State.ToString()
End Try

Once you use the Open() method, you have a live connection to your database. One of the most fundamental principles of data access code is that you should reduce the amount of time you hold a connection open as much as possible. Imagine that as soon as you open the connection, you have a live, ticking time bomb. You need to get in, retrieve your data, and throw the connection away as quickly as possible in order to ensure your site runs efficiently.

Closing a connection is just as easy, as shown here:

myConnection.Close()

Another approach is to use the Using statement. The Using statement declares that you are using a disposable object for a short period of time. As soon as you finish using that object and the Using block ends, the common language runtime will release it immediately by calling the Dispose() method. Here's the basic structure of the Using block:

Using object
    ...
End Using

It just so happens that calling the Dispose() method of a connection object is equivalent

to calling Close() and then discarding the connection object from memory. That means you can shorten your database code with the help of a Using block. The best part is that you don't need to write a Finally block—the Using statement releases the object you're using even if you exit the block as the result of an unhandled exception.

Here's how you could rewrite the earlier example with a Using block:

' Define the ADO.NET Connection object.
Dim connectionString As String = _
  WebConfigurationManager.ConnectionStrings("Pubs").ConnectionString
Dim myConnection As New SqlConnection(connectionString)

Try
    Using myConnection
        ' Try to open the connection.
        myConnection.Open()
        lblInfo.Text = "<b>Server Version:</b> " & myConnection.ServerVersion
        lblInfo.Text &= "<br /><b>Connection Is:</b> " & _
          myConnection.State.ToString()
    End Using
Catch err As Exception
    ' Handle an error by displaying the information.

lblInfo.Text = "Error reading the database."
    lblInfo.Text &= err.Message
End Try

lblInfo.Text &= "<br /><b>Now Connection Is:</b> "
lblInfo.Text &= myConnection.State.ToString()

There's one difference in the way this code is implemented as compared to the previous example. The error handling code wraps the Using block. As a result, if an error occurs the database connection is closed first, and then the exception handling code is triggered. In the first example, the error handling code responded first, and then the Finally finally block closed the connection afterward. Obviously, this rewrite is a bit better, as it's always good to close database connections as soon as possible.

To start, the connection string is defined as a private variable for the page class and retrieved from the connection string:

Private connectionString As String = _
  WebConfigurationManager.ConnectionStrings("Pubs").ConnectionString

The list box is filled when the Page.Load event occurs. Because the list box is set to persist its view state information, this information needs to be retrieved only once—the first time the page is displayed. It will be ignored on all postbacks.

Here's the code that fills the list from the database:

Protected Sub Page_Load(ByVal sender As Object, _
  ByVal e As EventArgs) Handles Me.Load

    If Not Page.IsPostBack Then
        FillAuthorList()
    End If
End Sub

Private Sub FillAuthorList()
    lstAuthor.Items.Clear()

    ' Define the Select statement.
    ' Three pieces of information are needed: the unique id
    ' and the first and last name.
    Dim selectSQL As String = "SELECT au_lname, au_fname, au_id FROM Authors"

' 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()

        ' For each item, add the author name to the displayed
        ' list box text, and store the unique ID in the Value property.
        Do While reader.Read()
            Dim newItem As New ListItem()
            newItem.Text = reader("au_lname") & ", " & reader("au_fname")
            newItem.Value = reader("au_id").ToString()
            lstAuthor.Items.Add(newItem)
        Loop
        reader.Close()

    Catch err As Exception
        lblResults.Text = "Error reading list of names."
        lblResults.Text &= err.Message
    Finally
        con.Close()
    End Try
End Sub

This example looks more sophisticated than the previous bite-sized snippets in this chapter, but it really doesn't introduce anything new. It uses the standard Connection, Command, and DataReader objects. The Connection is opened inside an error handling block so your page can handle any unexpected errors and provide information. A Finally block makes sure the connection is properly closed, even if an error occurs.

The actual code for reading the data uses a loop. With each pass, the Read() method is called to get another row of information. When the reader has read all the available information, this method will return False, the loop condition will evaluate to False, and the loop will end gracefully.

The unique ID (the value in the au_id field) is stored in the Value property of the list box for reference later. This is a crucial ingredient that is needed to allow the corresponding record to be queried again. If you tried to build a query using the author's name, you would need to worry about authors with the same name. You would also have the additional headache of invalid characters (such as the apostrophe in O'Leary) that would invalidate your SQL statement.

The record is retrieved as soon as the user changes the selection in the list box. To make this possible, the AutoPostBack property of the list box is set to True so that its change events are detected automatically.

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()

        ' Build a string with the record information,
        ' and display that in a label.
        Dim sb As New StringBuilder()
        sb.Append("<b>")
        sb.Append(reader("au_lname"))
        sb.Append(", ")
        sb.Append(reader("au_fname"))
        sb.Append("</b><br />")
        sb.Append("Phone: ")
        sb.Append(reader("phone"))
        sb.Append("<br />")
        sb.Append("Address: ")
        sb.Append(reader("address"))
        sb.Append("<br />")
        sb.Append("City: ")
        sb.Append(reader("city"))
        sb.Append("<br />")
        sb.Append("State: ")
        sb.Append(reader("state"))
        sb.Append("<br />")
        lblResults.Text = sb.ToString()

        reader.Close()

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

The process is similar to the procedure used to retrieve the last names. There are only a couple of differences:

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 14-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 14.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

To see the full code, refer to the online samples for this chapter. 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.

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 14-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 14.13. A failed insertion

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:

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.

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

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-14) and doesn't cause any real problems.

Figure 14.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.