Project Setup

In this chapter, you'll learn a number of new techniques. Along the way, you will:

• Add a hyperlink column to an existing DataGrid control.

• Edit data on the ProductDetail.aspx page.

• Add Edit, Save, and Cancel buttons.

• Add a template column.

• Add a new row using the DataGrid control.

• Delete data using the DataGrid control.

In this chapter, you'll add all these features to the ProductsEdit.aspx page. Figure 17.1 shows the finished page.

In order to work through the steps in this chapter, you'll need to add a few prebuilt pages to your application. (We've built them for you because there's a significant amount of code. Don't worry, however, because we'll walk through all the code that's different from the code you've already seen.)

Follow these steps to add the chapter examples to your project:

You will also need to add a hyperlink to the main page in order to call the new page you will build in this chapter:

In the code that you'll use later in this chapter, and in other chapters, you need to add apostrophes around a text value and pass that value back to SQL Server. In order to allow for embedded apostrophes within the text passed back to SQL Server, you must “double up” the apostrophes inside the text; otherwise, Visual Basic .NET will trigger a runtime error.

To make this simple and reusable, add the QuoteString method to the DataHandler class:

Public Shared Function QuoteString( _
 ByVal Value As String) As String

  Return String.Format("'{0}'", Value.Replace("'", "''"))
End Function

This method accepts a string as a parameter and returns that same string, with internal apostrophes doubled, surrounded by apostrophes. For example, if you called QuoteString and passed it the value Eat at O'Neill's, it would return 'Eat at O''Neill''s'. Those extra apostrophes are required by the VB .NET parser.

You're likely going to want to execute raw SQL statements more often than you might imagine. It makes sense to create a generic procedure to which you can pass a SQL statement that doesn't return any rows and then have the procedure do all the work for you. Therefore, we've created the ExecuteSQL function, which takes a SQL statement as its parameter and returns the number of rows it modified.

Add the procedure shown in Listing 17.1 to your DataHandler class.

Listing 17.1. The ExecuteSQL Procedure Allows You to Execute SQL Statements Without Having to Do All the Setup Work Each Time

Public Shared Function ExecuteSQL( _
 ByVal SQLStatement As String, _
 ByVal ConnectionString As String) As Integer
  Dim cmd As OleDbCommand
  Dim intRows As Integer

  Try
    cmd = New OleDbCommand()
    With cmd
      ' Create a Connection object
      .Connection = _
       New OleDbConnection(ConnectionString)

      ' Fill in Command Text, set type
      .CommandText = SQLStatement
      .CommandType = CommandType.Text

      ' Open the Connection
      .Connection.Open()

      ' Execute SQL
      intRows = .ExecuteNonQuery()
    End With
  Catch
    Throw

  Finally
    ' Close the Connection
    With cmd.Connection
      If .State = ConnectionState.Open Then
        .Close()
      End If
    End With
  End Try

  Return intRows
End Function

Investigating the ProductsEdit Page

The page you've imported, ProductsEdit.aspx, includes a simple DataGrid control. This page has been set up to retrieve its data from the Products table in the Northwind sample database, using techniques you've seen in previous chapters. The page's Page_Load procedure calls the GridLoad procedure, shown here:

Private Sub GridLoad()
  Dim ds As DataSet
  Dim strSQL As String
  Dim strConn As String

  ' Get SQL and Connection strings.
  strSQL = "SELECT ProductID, ProductName, " & _
   "UnitPrice, UnitsInStock " & _
   "FROM Products " & _
   "ORDER BY ProductName "
  strConn = Session("ConnectString").ToString

  ' Get DataSet based on SQL
  ds = DataHandler.GetDataSet(strSQL, strConn)

  ' Put DataSet into session variable
  Session("DS") = ds

  ' Set DataSource and draw grid
  grdProducts.DataSource = ds
  grdProducts.DataBind()
End Sub

The DataGrid control, as it's currently set up, contains just three columns (ProductID, UnitPrice, and UnitsInStock). In the next section, you'll learn to add a hyperlink column that you can use to link your DataGrid control to another page—in this case, a page showing detail information about the selected product.