| 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. Figure 17.1. The finished page allows you to edit data in place, within the DataGrid control.
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: -
Select the Project, Add Existing Item menu item. -
At the bottom of the Add Existing Item dialog box, change the Files of Type drop-down list to "All Files (*.*)." -
Browse to the Jumpstart\DataGridEditing folder and add ProductDetail.* and ProductsEdit.* 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: -
Open Main.aspx in the page designer. -
Add a new HyperLink control just below the Sales By Category hyperlink. -
Set the properties for this new HyperLink control as shown in Table 17.1. Table 17.1. Set the New Hyperlink's Properties Using These Values | Property | Value | | ID | hypProductsEdit | | Text | Edit Products | | NavigateURL | ProductsEdit.aspx | 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: -
Open the DataHandler class in the Visual Studio .NET code editor. -
Add the following procedure: 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. | 
