Beginning ASP.NET 2

Writing Data

VWD also adds a set of tags to the data source control to create the UPDATE parameters. ASP.NET 2.0 automatically fills these with the values that currently exist in the table. When the user makes a change to the data-bound control, ASP.NET 2.0 intelligently substitutes in the new value. The exception is for unique fields such as identifiers, in which case ASP.NET 2.0 keeps both the old and new value in the DataKeyNames dictionary as discussed in the introduction to this chapter.

Parameters (of any type) are not controls. They are tags within data source controls.

<ControlParameters> can hold values in the same way as <UpdateParameters>. Whereas

<UpdateParameters> hold the values that the data source control read from the existing record, <ControlParameters> hold values that the user enters or selects from controls other than the data-bound control that is changing the data. For example, you may be changing the date in a GridView, but you want the date to come from a selection in a Calendar control. You encountered <ControlParameters> in Chapter 7 when you created pages with a master-child relationship, and you will see examples in context of writing data later in this chapter.

An optional property can be added to a data source control: ConflictDetection=”CompareAllValues”. Conflict detection implements the DataKeyNames solution to avoid two users updating the same record at the same time.

In a data-bound control that implements updates you see two changes as well that are needed to move from a read-only control to a control that can update records (highlighted in the following code listing). First is the addition of a property for DataKeyNames. Second is in the list of fields, where you see the following new “field” added. Called a CommandField because it offers the user a button that will execute a command, it is only a field in the sense that ASP.NET 2.0 will create a field-like rendering for the button on the data-bound control:

<asp:DetailsView ID=”DetailsView1” runat=”server” AutoGenerateRows=”False” DataKeyNames=”FixtureID”

DataSourceID=”SqlDataSource1” Height=”50px” Width=”125px”> <Fields>

<asp:BoundField DataField=”FixtureID” HeaderText=”FixtureID” InsertVisible=”False” ReadOnly=”True” SortExpression=”FixtureID” />

<asp:BoundField DataField=”FixtureDate” HeaderText=”FixtureDate” SortExpression=”FixtureDate” /> <asp:CommandField

ShowEditButton=”True” ShowInsertButton=”True” />

</Fields>

</asp:DetailsView>

For example, the preceding code would contribute toward a DetailsView as follows in Figure 8-3 with the Edit and New buttons at the bottom of the data-bound DetailsView control.

<asp:CommandField ShowEditButton=”True”

269

Chapter 8

Figure 8-3

This small tag enables all of the functionality in the data-bound control to update records. This tag will render itself as a button labeled EDIT on a DetailsView or a column of EDIT buttons in a GridView. It will also automatically hide itself and substitute buttons for Update and Cancel when the control is in edit mode.

A common problem is to attempt to update a record with invalid data. For example, in the box for Goals Against a user may enter a bit of text or a date. When the data controls send this to the database an error will arise because the database is configured to limit values in a GoalsAgainst field to be integers. The problem can be solved in several ways, two of which are aspects of ASP.NET 2.0. First, you try to eliminate user-typed input whenever possible. For example, for the user to enter values for GoalsAgainst a football team, you could offer a list box with options from 1 to 10 (maybe 1 to 20 for Wrox United). The second technique is to use the ASP.NET 2.0 validation controls, which are covered in Chapter 15. As a rule of thumb, anything that a user types should be run through a validation control before it is sent to the database. This policy greatly reduces the susceptibility to a SQL injection attack.

The preceding directions explain the steps to turn on editing at the time you create a control. You can still turn on this feature using VWD after the control is on a page. Open the Common Tasks panel for the data source control and click Configure Data Source. You walk through the wizard again, but with choices filled in as per the existing state. It is easy to click the Advanced button and add the extra commands. Then go back and open the Common Tasks panel for the data-bound control and enable the Editing feature.

In the following Try It Out you practice the technique to enable updating data in a GridView and a DetailsView control by changing data for fixtures (games). You will be building on the Fixtures.aspx page you created in Chapter 7 that had a GridView with a child control of a DetailsView.

1.In VWD open the chapter Web site at C:\BegASPNET2\Chapters\Begin\Chapter08, and open the Fixtures.aspx page in Design. (Use the true Fixtures.aspx. Do not use the Fixtures-One or Fixtures-Stacked that you used as test pages in Chapter 7.)

2.Select the GridView1 control and open its Common Tasks panel. Enable Editing and, if not already enabled, enable Selection.

3.Select the SqlDataSource2 control (used by the DetailsView), open its Common Tasks panel, and click Configure Data Source. Make sure you select WroxUnitedConnectionString for the connection. On the Configure Select Statement screen, select the option to specify the columns from a table or view, select the Fixtures table, and select the individual columns (not the *).

270

Writing Data

4.Click the WHERE button to open the Add WHERE Clause window. For Column select FixtureID and for Source select Control. In the Parameter properties select GridView1 for the Control ID and click the Add button to add the parameter to the WHERE clause at the bottom of the screen. Click OK to close the window.

5.Back on the main wizard window, click the Advanced button, and on the Advancedwindow, turn on both options. Click OK to close the window, then Next and Finish to close the configuration wizard. As always, accept the refresh of the data keys if offered.

6.Select the DetailsView data-bound control and open its Common Tasks panel. Turn on Editing and close the panel. With these simple changes you have enabled editing of fixtures in both the

GridView and DetailsView.

7.You will make one final change. VWD is overzealous in creating the WHERE clause for the UpdateCommand. By default VWD added a check of every field prior to updating. You can be confident that if you match the FixtureID you have the right record. So modify the UpdateCommand of SqlDataSource2 (be sure you pick number two) in Source View to delete the shaded section in the following code – you may have to scroll the Source window to see the UpdateCommand. Your line breaks may be different; we have modified the listing for clarity. Be sure that the command still ends with the “> characters:

UpdateCommand=”UPDATE [Fixtures] SET

[FixtureDate] = @FixtureDate, [FixtureType] = @FixtureType, [GoalsFor] = @GoalsFor, [GoalsAgainst] = @GoalsAgainst, [Notes] = @Notes,

[Opponents] = @Opponents WHERE

[FixtureID] = @original_FixtureID

AND [FixtureDate] = @original_FixtureDate AND [FixtureType] = @original_FixtureType AND [GoalsFor] = @original_GoalsFor

AND [GoalsAgainst] = @original_GoalsAgainst

AND [Notes] = @original_Notes

AND [Opponents] = @original_Opponents

8.Run the page in your browser. In the GridView control, try clicking Edit and changing the name of an opponent as in Figure 8-4.

Figure 8-4

Now do the same in the DetailsView as in Figure 8-5.

271

Chapter 8

Figure 8-5

How It Works

Once again, ASP.NET 2.0 has made all of this easy by including the functionality of updating within the data controls. To enable updates in the data source control you checked off the option to include INSERT, UPDATE, and DELETE commands. This is more than you need for now (although you will use the INSERT and DELETE later in this chapter). VWD built two changes to the data source control that apply to updates. First is the addition of an UpdateCommand as follows.

<asp:SqlDataSource ID=”SqlDataSource1” ...

UpdateCommand=”UPDATE [Fixtures] SET [FixtureDate] = @FixtureDate, [FixtureType] = @FixtureType, [GoalsFor] = @GoalsFor, [GoalsAgainst] = @GoalsAgainst, [Notes] = @Notes, [Opponents] = @Opponents WHERE [FixtureID] = @original_FixtureID”>

VWD then added a set of parameters that will hold the values you will use for updates, as shown in the following code. Note that the FixtureID is saved in two parameters (the first and last). The first is the new value, if the user enters one. The second is named original_FixtureID and will hold the original value so it can be used in the WHERE clause to match which record to change:

<UpdateParameters>

<asp:Parameter Name=”FixtureDate” Type=”DateTime” /> <asp:Parameter Name=”FixtureType” Type=”String” /> <asp:Parameter Name=”GoalsFor” Type=”Int16” /> <asp:Parameter Name=”GoalsAgainst” Type=”Int16” /> <asp:Parameter Name=”Notes” Type=”String” /> <asp:Parameter Name=”Opponents” Type=”String” /> <asp:Parameter Name=”original_FixtureID” Type=”Int32” />

</UpdateParameters>

In the GridView control you added the single property of displaying the Edit button in a column:

<asp:GridView ID=”GridView1” ...

<Columns>

<asp:CommandField ShowSelectButton=”True” ShowEditButton=”True” />

The procedure was almost the same for the DetailsView. You modified the SqlDataSource2 to enable updates by adding an UpdateCommand and a set of update parameters. However, VWD is more conservative with DetailsView and provides original_ parameters for all fields and compares all fields in the WHERE clause. You excised those extra comparisons and just relied on matching the FixtureID.

272

Writing Data

Adding New Records

Adding new records, known in SQL parlance as INSERT or INSERT INTO, is almost as easy as changing an existing record. The main difference is that the GridView does not support adding a new record, so we consider that case in a minute.

As discussed previously, four changes must be made to the data controls. First, in the data source control you have that Advanced button in the Create New Data Source wizard. A click there and a check on the Generate INSERT, UPDATE, and DELETE gave you all three of the SQL statements. That created two additions to the data source control. The first is the InsertCommand itself. The second is the set of INSERT parameters. (Actually, commands and parameters were also created for UPDATE and DELETE, but it is INSERT that is of interest to us in this section.)

In the data-bound control you enable inserting with a checkbox on the data-bound control’s Common Tasks panel. Two changes result; the first is in the <asp:commandfield> where you now have the

ShowInsertButton. This will co-exist with the ShowEditButton and ShowDeleteButton if they have been selected. The ShowInsertButton enables the functionality to add new records. Like the other CommandField buttons, ShowInsertButton automatically hides itself and displays other buttons (Insert and Cancel) when the control switches to INSERT mode.

GridView is a special case because it does not directly support the addition of new records. The most common path is to display existing records in the GridView and create a button on the GridView that goes to a DetailsView (which can be on the same page or a second page). The DetailsView can then open in INSERT mode, ready to accept the values for the new record. The option to open in INSERT mode is set by the DefaultMode property of the data-bound control.

As in editing, constraints on data are acceptable. For example, if you were adding a new match report, the fixture must match an existing fixture. You can’t comment on a match that did not happen. As with editing, you can decrease these conflicts by giving users a list of possible options instead of typing. If they must type, then run the values through a validation control.

In this Try It Out you add the INSERT capability to the Fixtures.aspx page. You cannot insert new records in a GridView, so this exercise only affects the DetailsView control.

1.Open VWD and your Fixtures.aspx page in Source View and scroll down to the SqlDataSource2 control (the data source for the DetailsView control). Observe that in the last exercise when VWD added the capability to update a record, it also added similar tags for insert and delete operations. Therefore, in the data source control the insert behavior is already enabled.

2.Select the DetailsView control, open its Common Tasks panel, and enable inserting, as shown in Figure 8-6.

3.In the Properties window, find DefaultMode (see Figure 8-7) and change its value to Insert.

4.Save and view in the browser (see Figure 8-8). Try adding a new fixture and refresh the page.

5.As a final step, try entering some nonsense data, such as a date of 99/99/2006. Note how the error is not, at this point, handled very gracefully. Chapter 15 looks at handling errors.

273

Chapter 8

Figure 8-6

Figure 8-7

Figure 8-8

How It Works

Many of the tags for this process were created in the previous exercise when you configured the data sources and VWD offered the Advanced button with options to create INSERT, UPDATE, and DELETE commands. It may have seemed like an overwhelming number of tags at first, but you can now see how the pattern of a command plus a set of parameters enables a data writing function.

274

Writing Data

Specifically, you are using the InsertCommand of the SqlDataSource2 that supports the DetailsView. That command relies on the set of InsertParameters, as shown in the following code. Note that there is no need for a WHERE clause because you do not need to identify an existing record upon which to carry out the action:

<asp:SqlDataSource ID=”SqlDataSource2” ...

InsertCommand=”INSERT INTO [Fixtures] ([FixtureDate], [FixtureType], [GoalsFor], [GoalsAgainst], [Notes], [Opponents]) VALUES (@FixtureDate, @FixtureType, @GoalsFor, @GoalsAgainst, @Notes, @Opponents)”

...

<InsertParameters>

<asp:Parameter Name=”FixtureDate” Type=”DateTime” /> <asp:Parameter Name=”FixtureType” Type=”String” /> <asp:Parameter Name=”GoalsFor” Type=”Int16” /> <asp:Parameter Name=”GoalsAgainst” Type=”Int16” /> <asp:Parameter Name=”Notes” Type=”String” /> <asp:Parameter Name=”Opponents” Type=”String” />

</InsertParameters>

...

</asp:SqlDataSource>

ASP.NET 2.0 handles INSERTs in a way that is very similar to the UPDATEs you did in the last section. When you select the option in the Advanced dialog, VWD builds a SQL INSERT INTO statement with a set of parameters that can hold the data entered by the user. When you enable INSERT on the data-bound control VWD adds the CommandField (button) for adding a new record. All the rest of the code is taken care of by Microsoft, hidden from your view in the data controls. The only trap to remember is that inserting new records is available in DetailsView and FormView, but not GridView.

Deleting Records

By now you have a good feel for how writing data works and can anticipate that the procedure for deleting a record is similar. In the data source control you must include the DELETE command, which can

be automatically generated in the Advanced button of the control’s Configure New Data Source dialog box. That button will also create a set of DeleteParameters. But there are differences. For example, the DeleteParameters will be used to make an exact match for deletion in the WHERE clause, but they will not be used for changing information in the record.

As discussed before, there are some hazards when writing data and these are particularly true for deleting. The first is the human error of deleting the wrong record. More sophisticated systems respond to a request to delete by actually moving the record to a Deleted table. That gives a chance for recovery, at least until the Deleted table is purged. Discussions of this strategy are common in database texts and are not difficult to implement in ASP.NET 2.0. A second problem is that the user attempts to delete a record that the database has blocked from deletion. That block is most often the result of another record being dependent on the record that was to be deleted. For example, a table of orders would have a field for the ID of a customer that placed an order. If you delete that customer you are left with a value in the Orders table that points to nowhere. In this case, the database will return an error.

In this Try It Out you enable the capability to remove a match from the list of fixtures.

275

Chapter 8

1.Continue using VWD with Fixtures.aspx in Design View. Both of the data source controls already have the DeleteCommand and a set of DELETE parameters.

2.Select the GridView control, open its Common Tasks panel, and Enable Deleting (see Figure 8-9), which adds the following shaded line to the GridView control. That code also adds the Delete command fields in Figure 8-9.

<asp:GridView ID=”GridView1” runat=”server”

...

<Columns>

<asp:CommandField

ShowEditButton=”True”

ShowSelectButton=”True” ShowDeleteButton=”True” />

...

</Columns>

</asp:GridView>

Figure 8-9

3.Select the DetailsView control, open its Common Tasks panel, and Enable Deleting. Also go to the DetailsView’s Properties window and change the DefaultMode back to ReadOnly.

4.As before, when VWD creates a DetailsView with deleting it is overly cautious in checking fields to match up the correct record to delete. The original DELETE statement is shown in the following code. (Be sure you are looking at SqlDataSource2, the one for the DetailsView.) Delete the shaded portion. Double-check that the DeleteCommand still ends with a double quote:

DeleteCommand=”DELETE FROM [Fixtures] WHERE [FixtureID] = @original_FixtureID AND [FixtureDate] = @original_FixtureDate

AND [FixtureType] = @original_FixtureType AND [GoalsFor] = @original_GoalsFor

AND [GoalsAgainst] = @original_GoalsAgainst AND [Notes] = @original_Notes

AND [Opponents] = @original_Opponents

“

276

Writing Data

5.Save the page and test it in your browser. It should look like Figure 8-10.

6.Note that if you try to actually delete a fixture you will get an error message. Many other aspects of the site rely on the list of fixtures, so deleting on will leave those data hanging without a fixture. This situation is called a reference constraint as mentioned in the error.

Figure 8-10

How It Works

The pattern is the same as for other means of changing data. The data source control must have a DeleteCommand. When you checked the option in the Advanced step of the Configure Data Source wizard, VWD created the DeleteCommand for you. VWD also created a set of DeleteParameters that will be used by the WHERE clause to identify the record to delete. As with UPDATE, VWD tries to match every field in the DeleteParameters list for DetailsView. This may be necessary in some cases, but for us a simple match of the FixtureID is sufficient.

You then enabled deleting on the data-bound control, which instructed VWD to add a CommandField of the type Delete to the data-bound control. The term “field” here is a little odd because there is no connection to a field in the data table. But it is an addition to the data-bound control that is rendered similarly to a field.

One thing you may have noticed while running through the exercises in this chapter is that you have two ways of showing and editing the fixtures: a GridView and a DetailsView. When you edited data in one, the changes weren’t reflected in the other. To get around this you need to add code to the data

source controls, and although it’s only a single line of code you need, it has to be entered in several places. This is because there are several ways the data can change; you can insert new fixtures, edit existing fixtures, or delete fixtures. Each of these causes the SqlDataSource control to raise an event, and it is within that event that you have to rebind the data for the other control.

277

Chapter 8

The code for this is shown below:

Protected Sub SqlDataSource1_Deleted(ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) _ Handles SqlDataSource1.Deleted

DetailsView1.DataBind() End Sub

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) _ Handles SqlDataSource1.Inserted

DetailsView1.DataBind() End Sub

Protected Sub SqlDataSource1_Updated(ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) _ Handles SqlDataSource1.Updated

DetailsView1.DataBind() End Sub

Protected Sub SqlDataSource2_Deleted(ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) _ Handles SqlDataSource2.Deleted

GridView1.DataBind() End Sub

Protected Sub SqlDataSource2_Inserted(ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) _ Handles SqlDataSource2.Inserted

GridView1.DataBind() End Sub

Protected Sub SqlDataSource2_Updated(ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) _ Handles SqlDataSource2.Updated

GridView1.DataBind() End Sub

You can see that there are three events for each control. The Deleted event is raised after a record is deleted, the Inserted event is raised after a record is added, and the Updated event is raised when a record is updated. Even through you are using a GridView control to display one set of data, and a DetailsView control for the other, the events look the same because it is the SqlDataSource control that raises the event. For the GridView, which is bound to SqlDataSource1, the DetailsView needs to be refreshed, so the DataBind() method is called on the DetailsView, which instructs it to re-fetch the data. A similar procedure is done for the events of SqlDataSource2, which is used by the DetailsView, but this time the DataBind() method is called on the GridView1 control. It’s a simple procedure — when data in one control changes, you refresh the data in the other control.

Uploading Pictures

ASP.NET 2.0 offers an easy way to upload pictures (or other files) from the browser to the server. Although not strictly a database issue, the topic is covered here. The toolbar offers the FileUpload tool, which can

278