Insert, Update, or Delete from a Web Page

The last sample in this chapter demonstrates an approach to performing data manipulation from a Web page by using ADO.NET objects and a DataGrid control. The sample enables a user to insert, update, and delete rows from the VBDotNetShippers table in the Northwind database. This table, initially used in the section Browsing and Manipulating Data with a Form in Chapter 9, is a copy of the Shippers table that lets your application manipulate a table without modifying the original Shippers table in the Northwind database.

Design and Operational Page Views

One especially easy way to grasp the substance of this application is to see it in action. The application enables the three basic data manipulation tasks while listing the most recent data from the VBDotNetShippers table.

Inserting a Row

Figure 11-8 shows the insert function in operation. In the top window, you can see the CompanyName and Phone column values for a new row in the VBDotNetShippers table. The new column values appear in the second and third text boxes on the left side of the Web page. In addition, the cursor rests on the Insert button. After a user clicks the Insert button, the application updates the page, as shown in the bottom window in Figure 11-8. The values in the text boxes move to the DataGrid control, which is bound to the VBDotNetShippers table in the Northwind database.

click to expand
Figure 11-8: The impact of clicking the Insert button

The sample application does not move the column values from the text boxes directly to the DataGrid control. Instead, the click event procedure for the Insert button first adds a new row to the VBDotNetShippers table in the Northwind database with the column values showing in the text boxes. The database creates a new autonumber field value (which supplies the ShipperID value) for the new row. Then, the application refreshes the local VBDotNetShippers DataTable object to reflect the new version of the VBDotNetShippers table in the Northwind database. Finally, the application rebinds the DataGrid control to the updated local DataTable object. In the process of refreshing the local DataTable object, the application clears the TextBox controls in the form on the Web page.

Updating a Row

Updating a row is a two-step process. First, you have to select a row by entering its ShipperID column value in the first text box and clicking Select. This populates the remaining two text boxes with the corresponding CompanyName and Phone column values. The top window in Figure 11-9 shows this result ”actually, the CompanyName column value appears after updating. Using the column values in the text boxes, you can modify the column values for the selected row. For example, the second text box shows the removal of , Inc. from the CompanyName column value for the row with a ShipperID value of 4. Clicking the Update button in the top window of Figure 11-9 transforms the Web page to the one in the figure s bottom window. Notice that the row for ShipperID 4 appears without an Inc. within the CompanyName column value.

click to expand
Figure 11-9: The impact of clicking the Update button

As with the Insert button, the impact of clicking the Update button is the result of a multistep process. First, clicking the Update button revises the VBDotNetShippers table in the Northwind database. Second, the application refreshes the local DataTable object based on the new version of the table in the database. Finally, the sample rebinds the DataGrid control to the updated DataTable object and clears the text boxes.

Deleting a Row

Removing a row from the DataGrid control is straightforward. The user merely enters the ShipperID column value for the row to be removed and then clicks the Delete button. These actions update the background table in the database, the local DataTable object, and ultimately the appearance of the DataGrid control. Figure 11-10 shows the deletion of a row in process. The top window shows the entry of the ShipperID for the row with a ShipperID column value of 4. After a click to the Delete button, the Web page transforms as shown in the bottom window.

click to expand
Figure 11-10: The impact of clicking the Delete button

Design View of the Web Page

Figure 11-11 presents the Design view for the Web page shown in various operational views in Figures 11-8, 11-9, and 11-10. The main reason for presenting the Design view is to identify the elements on the Web page so that you can follow the code behind the Web page. As you can see, the page features three TextBox controls with two Button controls above them and three more Button controls below them. A DataGrid control appears to the right of the text boxes.

click to expand
Figure 11-11: A Design view of a Web page for inserts , updates, and deletes

You refer to a control on a Web page programmatically by its ID property value. The DataGrid control has the ID property value of DataGrid1 . The three TextBox controls on the page (from top to bottom) have ID property settings of TextBox1 , TextBox2 , and TextBox3 . The two Button controls above the text boxes have ID property values of Button1 and Button5 , from left to right. The three Button controls below the text boxes have ID settings of Button 2, Button 3, and Button4 , from left to right.

The Code Behind the Web Page

The code behind this Web page is intentionally easy to understand. The code integrates a collection of basic ADO.NET and Visual Basic .NET design techniques to perform a set of a common database tasks via a Web connection. By enabling these tasks over a Web connection, the code vastly expands the potential base of users. This is because Web applications do not require the .NET Framework on client workstations, and client computers need not be on a physical LAN. You should, of course, authenticate users before granting them access to a system that lets users manipulate a database. The Logging In to a Protected Page Sample section in Chapter 10 presents a basic sample demonstrating how to control access to a Web session via a login process In addition, Chapter 13 focuses on security issues for Access databases in .NET applications.

The code behind the Web page consists of module-level declarations for the ADO.NET objects ( cnn1 , dap1 , and das1 ), a Page_Load procedure, event procedures for each Button control, and a collection of supporting Sub and Function procedures called by the event procedures. The Page_Load procedure features two main code blocks. Within an If...Then statement that has Not IsPostBack as a condition, you can find the page setup code that needs to execute just once. This code assigns Text and TabIndex property settings for controls on the Web page. You can use the TabIndex property to specify the tab order of controls on a Web page (and a Windows Forms instance). Outside the If...Then statement is code for a Connection object and a call to the Refresh Sub procedure. This Sub procedure populates the DataGrid1 control with the most recent data on the VBDotNetShippers table in the Northwind database.

The Button1_Click procedure populates TextBox2 and TextBox3 with CompanyName and Phone column values for the row with a ShipperID column value matching the value in TextBox1 . This capability supports the editing of CompanyName and Phone column values. The application does not support edits to ShipperID column values because the Access database controls this column with an autonumber setting. The Button1_Click procedure works with the local DataTable object to which the DataGrid1 control binds. One modification you could make to this procedure would be to add a primary key for the ShipperID column. By making this change, you can use the Find method instead of a loop. However, when working with a DataTable object that has only a few rows (as in the current sample), you are not likely to gain much advantage by using a primary key.

The click event procedures for Button2 , Button3 , and Button4 have a parallel design, although they support substantially different functions. All the procedures execute a SQL statement to update the VBDotNetShippers table in the Northwind database. In addition, each procedure refreshes the Web page to reflect these changes. Two Sub procedures support these capabilities. The RunSQLString procedure executes the SQL statement represented by a String value. The code for the RunSQLString procedure invokes the ExecuteNonQuery method to execute the SQL statement, and it manages the opening and closing of the cnn1 Connection object around the use of the method. You should leave Connection objects open only when they are necessary for a task. The ClearAllAndRefresh procedure begins by clearing the dataset ( das1 ) to which the DataGrid1 control binds. Then, the procedure invokes the Refresh procedure to repopulate the DataGrid1 control based on the modification implemented by the SQL statement. The ClearAllAndRefresh procedure closes by clearing any values appearing in the TextBox controls. This prepares the Web page for any additional data manipulation.

The uniqueness of the event procedures for each of these three Button controls ( Button2 , Button3 , and Button4 ) resides primarily in the SQL statement specifying the type of change to make in the VBDotNetShippers table. All three procedures assign a different SQL string to the str1 variable. The Button2_Click procedure demonstrates the syntax for deleting a row based on the ShipperID value in TextBox1 . The Button3_Click procedure shows a syntax for changing the CompanyName and Phone column values of a row specified by the ShipperID value in the TextBox1 control. The Text property values for the TextBox2 and the TextBox3 controls specify the revised column values. The Button4_Click procedure illustrates the syntax for adding a new row to the VBDotNetShippers table with CompanyName and Phone column values equal to those in the TextBox2 and TextBox3 controls. Because the ShipperID column is an autonumber field, you should not designate a value for it.

Within any multiuser application, you need to account for the actions of other users. Other users can affect how or whether a SQL statement should execute. In the current application, you have no need to worry about other users for inserts because the Access database automatically assigns a unique ShipperID value for each new row. However, it is possible for another user to remove the row with a ShipperID value that a user then tries to change or delete. This is because the application relies on optimistic concurrency, which is common for .NET applications and good practice for Web applications that need to support many users.

Accounting for the actions of other users is often highly specific to the needs of a particular application. The sample application in this section checks for the existence of a row by calling the IsThere Function procedure. If the procedure returns a value of True , the Button2_Click and Button3_Click procedures proceed to implement either a delete or an update. This level of checking is sufficient for low to moderate levels of data manipulation. If you have a high volume of delete and update transactions that can potentially conflict with one another, you should apply another strategy to ensure that a row is not deleted after you check for its presence but before you operate on it. This is an issue for updates because you cannot modify a row that does not exist. If you try to delete a row that is missing, the application proceeds without an error and updates the DataGrid1 control to show the row as missing.

At least two strategies for handling concurrency issues with updates are available. First, you can wrap the IsThere function call and the RunSQLString call in a transaction. This will guarantee a lock on a row from the point at which you check whether it exists until you implement your update. However, this benefit can generate other concurrency issues, such as a row being unavailable to other users while the lock is in effect. A second approach for handling concurrency issues is to write updates with additional information about the identity of the user and the time of the change to a special table. Then, the application can update the main table from the special table during a time of lower transaction volume.

The Button5_Click procedure is the least complicated of any of the button click event procedures. This procedure merely calls the ClearAllAndRefresh procedure. In a multiuser application, this process gives any user a chance to see changes made to the database by other users since the last time that a page was refreshed. If you plan to update or delete a row, it is a good idea to click the Refresh button ( Button5 ) before attempting to change the database.

 Dim cnn1 As New OleDb.OleDbConnection() Dim dap1 As New OleDb.OleDbDataAdapter() Dim das1 As New System.Data.DataSet() Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Initialize page If Not IsPostBack Then Assign Text property values for buttons Button1.Text = "Select" Button2.Text = "Delete" Button3.Text = "Update" Button4.Text = "Insert" Button5.Text = "Refresh" Assign TabIndex property for controls Button1.TabIndex = 2 TextBox1.TabIndex = 1 TextBox2.TabIndex = 3 TextBox3.TabIndex = 4 Button2.TabIndex = 5 Button3.TabIndex = 6 Button4.TabIndex = 7 Button5.TabIndex = 8 End If Re-assign connection string for current opening of the page Dim str1 As String = _ "Provider = Microsoft.Jet.OLEDB.4.0;" str1 &= _ "Data Source = c:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb" cnn1.ConnectionString = str1 Invoke Refresh to populate grid control Refresh() End Sub Sub Refresh() Instantiate connection string Dim cmd1 As New OleDb.OleDbCommand() Return all rows from the VBDotNetShippers table from the Northwind database With cmd1 .Connection = cnn1 .CommandText = _ "SELECT * FROM VBDotNetShippers" End With Fill VBDotNetShippers DataTable object in dap1 DataAdapter object Dim dap1 As New OleDb.OleDbDataAdapter(cmd1) dap1.Fill(das1, "VBDotNetShippers") Bind DataGrid1 control to VBDotNetShippers DataTable object DataGrid1.DataSource = (das1.Tables("VBDotNetShippers")) DataGrid1.DataBind() End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Populate TextBox2 and TextBox3 with CompanyName and Phone column values for designated ShipperID in TextBox1 Dim int1 As Integer For int1 = 0 To das1.Tables(0).Rows.Count - 1 If das1.Tables(0).Rows(int1)(0) = _ CInt(TextBox1.Text) Then TextBox2.Text = das1.Tables(0).Rows(int1)(1) TextBox3.Text = das1.Tables(0).Rows(int1)(2) End If Next End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Trap for missing row in database table If IsThere(TextBox1.Text) = False Then Response.Write("DELETE exit trap") Exit Sub End If Specify update SQL string Dim str1 As String = _ "DELETE FROM VBDotNetShippers " & _ "WHERE ShipperID=" & TextBox1.Text Run SQL String RunSQLString(str1) Clear controls and refresh from Northwind ClearAllAndRefresh() End Sub Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click Trap for missing row in database table If IsThere(TextBox1.Text) = False Then Response.Write("UPDATE exit trap") Exit Sub End If Specify update SQL string Dim str1 As String = _ "UPDATE VBDotNetShippers " & _ "SET CompanyName= ˜" & TextBox2.Text & " ˜, " & _ "Phone= ˜" & TextBox3.Text & _ " ˜ WHERE ShipperID=" & TextBox1.Text Run SQL String RunSQLString(str1) Clear controls and refresh from Northwind ClearAllAndRefresh() End Sub Private Sub Button4_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button4.Click Specify insert SQL string Dim str1 As String = _ "INSERT INTO VBDotNetShippers " & _ "(CompanyName, Phone) VALUES( ˜" & _ TextBox2.Text & " ˜, " & TextBox3.Text & _ " ˜)" Run SQL String RunSQLString(str1) Clear controls and refresh from Northwind ClearAllAndRefresh() End Sub Private Sub Button5_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button5.Click Clear controls and refresh from Northwind ClearAllAndRefresh() End Sub Function IsThere(ByVal str1) As Boolean Dim cmd1 As New OleDb.OleDbCommand() Dim int1 As Integer Run SQL statement to determine if ShipperID column value exists With cmd1 .Connection = cnn1 .CommandText = _ "SELECT * FROM VBDotNetShippers " & _ "WHERE ShipperID=" & str1 cnn1.Open() int1 = .ExecuteScalar cnn1.Close() End With Return True if int1 is greater than 0 If int1 > 0 Then Return True End Function Sub RunSQLString(ByVal str1 As String) Instantiate OleDbCommand object Dim cmd1 As New OleDb.OleDbCommand() Assign Connection and CommandText property values before invoking the command With cmd1 .Connection = cnn1 .CommandText = str1 cnn1.Open() .ExecuteNonQuery() cnn1.Close() End With End Sub Sub ClearAllAndRefresh() Clear local DataTable object and repopulate from Northwind database das1.Clear() Refresh() Clear text boxes TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" End Sub 
 


Programming Microsoft Visual Basic. NET for Microsoft Access Databases
Programming Microsoft Visual Basic .NET for Microsoft Access Databases (Pro Developer)
ISBN: 0735618194
EAN: 2147483647
Year: 2006
Pages: 111
Authors: Rick Dobson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net