Creating Data Entry Pages

Although RepeaterTest.aspx provides a more convenient listing of the customers in the Customer table, it does nothing to allow editing of that data. I did, however, make the customer name and the Add New button in RepeaterTest.aspx hyperlinks. Remember the links to EditCustomer.aspx I mentioned in the previous section? Clicking on these links will take you to the page shown in Figure 9 9.

Cwtomer Mî|ntKii(ince fTM Cuiluiïït

|01 NKI

Figure 9-9 : The Customer Maintenance page, EditCustomer.aspx, which allows you to edit customer records selected from RepeaterTest.aspx The page shown in Figure 9-9 will allow you to edit or delete the current customer. It's not designed to enable you to navigate from customer record to customer record, as you might do in a traditional Microsoft Access or Visual Basic 6.0 application. However, using a limited amount of code, it does offer quite a bit of functionality. For example, let's say I changed the ContractEnds date to 1/32/2003, and removed the at sign (@) from the ContactEmail address. Figure 9-10 shows the result.

m- [ji y— pç-4-m [wt ih

- ■

■jrftk. - -t J i3 jj'iwih gwedta ^ ^^ J

****** ¡40 tax? ( tor K hqx unj r^tin.tdr:.* :rw m* e-1

cfv> krti "

CfiKt IM (Lut. F'H: |n<»j . |Cm*h HdJrahi |ll'i K'^i

FHlirM jUAj _

Customer M3lrt>e<nn(e

CfiKt IM (Lut. F'H: |n<»j . |Cm*h HdJrahi |ll'i K'^i

FHlirM jUAj _

Figure 9-10 : The Customer Maintenance page, showing invalid input for ContractEnds and ContactEmail

As you can see, the page displays asterisks next to the two fields that contain validation errors. The date is clearly invalid, and the e-mail address isn't valid, since there isn't an @. One thing that is important to note is that this validation took place on the client, and even if the client didn't have JavaScript working on their machine, the server-side part of the validation would have caught the errors. Also significant is that fact that each of the fields on the page is linked to at least one validation control. The State drop-down list is connected to a database table of states, located in the same GolfArticles database as the Customer table.

Creating the User Interface

Listing 9-6 shows EditCustomer.aspx, the file that created the pages shown in Figures 99 and 9-10.

Listing 9-6 EditCustomer.aspx, the file used to create the Customer Maintenance page shown in Figures 9-9 and 9-10

Creating the User Interface

Listing 9-6 shows EditCustomer.aspx, the file that created the pages shown in Figures 99 and 9-10.

Listing 9-6 EditCustomer.aspx, the file used to create the Customer Maintenance page shown in Figures 9-9 and 9-10

<%@ Page Debug="true" language="c#"

Codebehind="EditCustomer.aspx.cs" AutoEventWireup="false"

Inherits="Chapter09_Template.EditCustomer" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <HTML> <HEAD> <META http-equiv=Content-Type content="text/html; charset=windows-1252"> <meta content="Microsoft Visual Studio 7.0" name=GENERATOR> <meta content=C# name=CODE_LANGUAGE> <meta content="JavaScript (ECMAScript)"

name=vs_defaultClientScript> <meta content= name=vs_targetSchema> </HEAD> <body>

<form id=EditCustomer method=post runat="server"> <table width=640> <tr>

<td colspan=2 align=middle> <p><font face=Verdana,Arial color=#3300ff size=4> Customer Maintenance </font></p>

<td width="30%" align=right>

<font face="Verdana,Arial" size=2 color="#3300ff">

Company Name:

<asp:TextBox id=CompanyName runat="server" MaxLength="50" Width="250px" ></asp:TextBox> <asp:RequiredFieldValidator id=RequiredFieldValidator2 runat="server"

ControlToValidate="CompanyName" ErrorMessage="*"> </asp:RequiredFieldValidator> </td> </tr> <tr>

<td width="30%" align=right>

<font face="Verdana,Arial" size=2 color="#3300ff">

Contact Name (Last, First):

<asp:TextBox id="ContactLastName" runat="server" MaxLength="50" Width="200px" ></asp:TextBox> <asp:RequiredFieldValidator id="Requiredfieldvalidator7" runat="server"

ControlToValidate="ContactLastName" ErrorMessage="*"> </asp:RequiredFieldValidator>,&nbsp; <asp:TextBox id="ContactFirstName" runat="server" MaxLength="50" Width="200px" ></asp:TextBox> <asp:RequiredFieldValidator id="Requiredfieldvalidator8" runat="server"

ControlToValidate="ContactFirstName" ErrorMessage="*"> </asp:RequiredFieldValidator> </td> </tr> <tr>

<td width="30%" align=right>

<font face="Verdana,Arial" size=2 color="#3300ff">


<asp:TextBox id="Address" runat="server" MaxLength="5C" Width="25Cpx" ></asp:TextBox> <asp:RequlredFleldValldator id=RequiredFieldValidator3 runat="server"

ControlToValldate="Address" ErrorMessage="*"> </asp:RequiredFieldValidator> </td> </tr> <tr>

<td wldth="3C%" allgn=rlght>

<font face="Verdana,Arlal" slze=2 color="#33CCff">

City, State and Zip:

<asp:TextBox ld="Clty" runat="server" MaxLength="5C" Width="2CCpx" ></asp:TextBox>

<asp:DropDownList id=ddlState runat="server"> </asp:DropDownList> <asp:TextBox id="PostalCode" runat="server" MaxLength="10" Width="70px" ></asp:TextBox> <asp:RequiredFieldValidator id=RequiredFieldValidator4 runat="server" Display="Dynamic" ControlToValidate="City" ErrorMessage="*"> </asp:RequiredFieldValidator> <asp:RequiredFieldValidator id=RequiredFieldValidator5 runat="server" Display="Dynamic" ControlToValidate="PostalCode" ErrorMessage="*"> </asp:RequiredFieldValidator> <asp:RegularExpressionValidator id=RegularExpressionValidator1 runat="server"

ControlToValidate="PostalCode" ErrorMessage="*"


</asp:RegularExpressionValidator> </td> </tr> <tr>

<td width="3C%" align=right>

<font face="Verdana,Arlal" slze=2 color="#33CCff">

Contract Ends:

<asp:TextBox ld="ContractEnds" runat="server" MaxLength="1C" Width="7Cpx" ></asp:TextBox> <asp:RequlredFleldValldator id=RequiredFieldValidator1 runat="server" ErrorMessage="*" ControlToValldate="ContractEnds" Display="Dynamic"> </asp:RequiredFieldValidator> <asp:CompareValidator ID=CompareValidator1 Runat=server ErrorMessage="*" Type=Date Display=Dynamic


Operator="DataTypeCheck"> </asp:CompareValidator> </td> </tr> <tr>

<td width="30%" align=right>

<font face="Verdana,Arial" size=2 color="#3300ff">

Contact EMail:

<asp:TextBox id="ContactEmail" runat="server" MaxLength="50" Width="250px" ></asp:TextBox> <asp:RequiredFieldValidator id="Requiredfieldvalidator6" runat="server"

ControlToValidate="ContactEMail" ErrorMessage="*" Display="Dynamic"> </asp:RequiredFieldValidator> <asp:RegularExpressionValidator id=RegularExpressionValidator2 runat="server" Display="Dynamic" ControlToValidate="ContactEmail" ErrorMessage="*"


"\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"> </asp:RegularExpressionValidator> </td> </tr> <tr>

<td width="30%" align=right>

<font face="Verdana,Arial" size=2 color="#3300ff">

User Name:

<asp:TextBox id="UserName" runat="server" MaxLength="50" Width="250px" ></asp:TextBox> <asp:RequiredFieldValidator id="Requiredfieldvalidator9" runat="server"

ControlToValidate="UserName" ErrorMessage="*"> </asp:RequiredFieldValidator> </td> </tr> <tr>

<td width="30%" align=right>

<font face="Verdana,Arial" size=2 color="#3300ff">


<asp:TextBox id="Password" runat="server" MaxLength="5C" Width="25Cpx" ></asp:TextBox> <asp:RequlredFleldValldator ld="Requlredfieldvalldator1C" runat="server"

ControlToValldate="Password" ErrorMessage="*"> </asp:RequlredFleldValldator> </td> </tr> <tr>

<td colspan=2 align=middle> <asp:Button id=BtnSave runat="server" Text="Save"> </asp:Button>&nbsp; <asp:Button id=BtnCancel runat="server" Text="Cancel" CausesValidation="False" > </asp:Button>&nbsp; <asp:Button ld=btnDelete runat="server"

Text="Delete" Visible="False" CausesValidation="False"> </asp:Button> </td> </tr> </table>

<asp:Label id=Label1 runat="server" ForeColor="Red" Font-Names="Verdana,Arial"> </asp:Label></form>

The general structure of EditCustomers.aspx is an HTML table, with two columns. The left column contains the field names, and the right column contains the controls that allow the fields to be entered and edited.

Each of the fields, except the State drop-down list, is hooked up to a RequiredFieldValidatorcontrol. RequiredFieldValidator, as you recall, is one of the simpler validator controls. The only attributes set for most of the RequiredFieldValidator controls are ControlToValidate, each set to a different control, and ErrorMessage, in this case, an asterisk. Some of the RequiredFieldValidator controls—for example the control associated with the ContractEnds text box—also have the Display attribute set to Dynamic. Recall from Chapter 5 that validators not set to Display=Dynamic take up space even when they're not being fired. When you have multiple validators on a single control, having those validators set to Display=Dynamic indicates that validators that aren't signaled won't take up space, meaning that when you have two validators on a field, no matter which one is signaled, the error message will begin at the same location. Several of the controls have RegularExpressionValidator controls associated with them. For example, the PostalCode text box uses the following RegularExpressionValidator declaration:

<asp:RegularExpressionValidator id=RegularExpressionValidator1 runat="server"

ControlToValidate="PostalCode" ErrorMessage="*"

ValidationExpression="\d{5}(-\d{4})?"> </asp:RegularExpressionValidator>

The ValidationExpression attribute indicates that there must be five digits, optionally followed by a hyphen and four other digits. There's also a RegularExpressionValidator control associated with the ContactEmail field, and that ValidationExpression attribute is even more complex. Again, the MSDN documentation covers the regular expression syntax fairly completely.

The ValidationExpression attribute indicates that there must be five digits, optionally followed by a hyphen and four other digits. There's also a RegularExpressionValidator control associated with the ContactEmail field, and that ValidationExpression attribute is even more complex. Again, the MSDN documentation covers the regular expression syntax fairly completely.

Why Doesn't EditCustomers.aspx Use Data Binding?

Why Doesn't EditCustomers.aspx Use Data Binding?

In Listing 9-6, you'll see a number of text box controls, including the CompanyName text box control, shown here:

<asp:TextBox id=CompanyName runat="server"



What seems to be missing is any code to bind data to the controls. This isn't an accident, and ends up being an interesting design decision. When I first began this page, I did use data binding to set the text value for the control. This approach worked, but it left me with a couple of problems, some obvious, some less so. First, having the binding taking place declaratively places details of how the data is bound in the .aspx file. This isn't a showstopper, and in fact RepeaterTest.aspx does have the binding code intermixed within the user interface code. The DataGrid object shown in the GridTest.aspx example provides some middle ground, in that you actually declare the name of the field to be bound in each column, but you don't have the <%# %> tags that bind code in the .aspx file.

The more compelling problem is what happens when you try to bind to the DataReader object and find that there's no data. How might that happen? Several ways. First, the CustomerID property is passed in as a parameter in the URL. A user could bookmark the page, capturing the CustomerID value as well. If the user visits the page again and the CustomerID value is no longer valid, the error will occur. I might also get to this page without a valid CustomerID when I try to add a customer. The +New link at the bottom of the page in Figure 9-8 links to EditCustomer.aspx with a CustomerID of 0, indicating that I want to add a new record. Manually setting the text box controls within the code-behind file works well, and because data binding is read-only anyway, there already needs to be code to handle saving updates that deals with each text box in any event.

The final type of validator is the CompareValidatorcontrol. The ContractEnds field is a date. Although I can't be sure exactly when the date will be—in the past or in the future— I do know that it must be a valid date. The CompareValidator control is the answer to this problem, with a particular set of attributes specified, as follows:





Type=Date Display=Dynamic ControlToValidate="ContractEnds" Operator="DataTypeCheck"> </asp:CompareValidator>

The significant attributes here are Type=Date, which indicates that the field should contain a date; ControlToValidate="ContractEnds", which points to the control that should contain the date; and Operator="DataTypeCheck", which tells the .NET Framework that I'm just checking that the correct data type has been entered. Finally, several button controls appear toward the bottom of the page. One problem with validator controls is what to do when you just want to get out of the page. For example, when you click the Cancel button, you certainly don't want to force the user to enter valid information in each of the fields containing validators. The answer is to set the CausesValidation attribute of the Cancel button to False. This will disable client and server validation, allowing your server-side button click handler to do what it needs to do. In this example, the server-side button handler for the Cancel button will simply redirect the user back to the RepeaterTest.aspx page.

Processing Data Entry

The code-behind file for EditCustomer.aspx, EditCustomer.aspx.cs, is shown in Listing 97.

Listing 9-7 EditCustomer.aspx.cs, the code-behind file for the Customer Maintenance age using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace Chapter09_Template {

/// Summary description for EditCustomer.

public class EditCustomer : System.Web.UI.Page

protected DropDownList ddlState; protected TextBox CompanyName; protected TextBox Address; protected TextBox City; protected TextBox PostalCode; protected Label Labell; protected TextBox ContractEnds;

protected RequiredFieldValidator RequiredFieldValidatorl; protected CompareValidator CompareValidatorl; protected RequiredFieldValidator RequiredFieldValidator2; protected RequiredFieldValidator RequiredFieldValidator3; protected RequiredFieldValidator RequiredFieldValidator4; protected RequiredFieldValidator RequiredFieldValidator5; protected RegularExpressionValidator RegularExpressionValidatorl; protected TextBox ContactEmail;

protected RequiredFieldValidator Requiredfieldvalidator6;

protected RegularExpressionValidator RegularExpressionValidator2;

protected TextBox ContactLastName;

protected RequiredFieldValidator Requiredfieldvalidator7;

protected TextBox ContactFirstName;

protected TextBox UserName;

protected TextBox Password;

protected RequiredFieldValidator Requiredfieldvalidator8;

protected RequiredFieldValidator Requiredfieldvalidator9;

protected RequiredFieldValidator Requiredfieldvalidator10;

protected Button BtnSave;

protected Button BtnCancel;

protected Button btnDelete;

protected SqlDataReader dr;

public int CustomerlD {

get { return (int)ViewState["CustomerID"]; } set { ViewState["CustomerID"]=value; }

public EditCustomer() {

Page.lnit += new System.EventHandler(Page_Init);

private void doDataBind() {

System.Data.SqlClient.SqlConnection cn; System.Data.SqlClient.SqlConnection cnState; System.Data.SqlClient.SqlCommand cmd; System.Data.SqlClient.SqlCommand cmdState; cn=new System.Data.SqlClient.SqlConnection( "server=localhost;" +

"Integrated Security=SSPI;Initial Catalog=GolfArticles"); cnState=new System.Data.SqlClient.SqlConnection( "server=localhost;" +

"Integrated Security=SSPI;Initial Catalog=GolfArticles"); cmd=new System.Data.SqlClient.SqlCommand(

"spSelectCustomer",cn); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.Add("@CustomerID", Request.QueryString["CustomerID"]); cmdState=new System.Data.SqlClient.SqlCommand( "SELECT StateAbbreviation FROM " + "States ORDER BY StateAbbreviation", cnState);


CommandBehavior.CloseConnection); cnState.Open();

ddlState.DataTextField="StateAbbreviation"; ddlState.DataSource=cmdState.ExecuteReader( CommandBehavior.CloseConnection);



ddlState.Items.FindByText(dr.GetString(4))); CompanyName.Text=(string)dr["CompanyName"]; Address.Text=(string)dr["Address"]; City.Text=(string)dr["City"]; PostalCode.Text=(string)dr["PostalCode"]; ContractEnds.Text=

((DateTime)dr["ContractEnds"]).ToShortDateString(); ContactEmail.Text=(string)dr["ContactEmail"]; ContactFirstName.Text=(string)dr["ContactFirstName"]; ContactLastName.Text=(string)dr["ContactLastName"]; UserName.Text=(string)dr["UserName"]; Password.Text=(string)dr["Password"]; // Close data reader, and thus connection. dr.Close();



catch ( System.Exception eLoad) {

Label1.Text=eLoad.Message; btnDelete.Visible=false;

private void Page_Load(object sender, System.EventArgs e) {

// Put user code to initialize the page here if ( !(this.IsPostBack) ) {


(string)Request["CustomerID"]); doDataBind();



private void Page_Init(object sender, EventArgs e) {

// CODEGEN: This call is required by the // ASP.NET Web Form Designer.


#region Web Form Designer generated code /// <summary>

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary>

private void InitializeComponent() {

this.BtnSave.Click +=

new System.EventHandler(this.BtnSave_Click); this.BtnCancel.Click +=

new System.EventHandler(this.BtnCancel_Click); this.btnDelete.Click +=

new System.EventHandler(this.btnDelete_Click); this.Load +=

new System.EventHandler(this.Page_Load);

#endregion private void BtnCancel_Click(object sender, System.EventArgs e) {


private void BtnSave_Click(object sender, System.EventArgs e) {

System.Data.SqlClient.SqlConnection cn; System.Data.SqlClient.SqlCommand cmd; System.Data.SqlClient.SqlParameter prm;

cn=new System.Data.SqlClient.SqlConnection( "server=localhost;" +

"Integrated Security=SSPI;Initial Catalog=GolfArticles"); cmd=new System.Data.SqlClient.SqlCommand(

"spSaveCustomer",cn); cmd.CommandType=CommandType.StoredProcedure;

prm=new System.Data.SqlClient.SqlParameter(

"@ReturnValue",0); prm.Direction=ParameterDirection.ReturnValue; cmd.Parameters.Add(prm);





cmd.Parameters.Add("@State", ddlState.SelectedItem.Text); cmd.Parameters.Add("@PostalCode",PostalCode.Text); cmd.Parameters.Add("@ContractEnds", System.DateTime.Parse( ContractEnds.Text)); cmd.Parameters.Add("@ContactFirstName",

ContactFirstName.Text); cmd.Parameters.Add("@ContactLastName",

ContactLastName.Text); cmd.Parameters.Add("@ContactEMail",

ContactEmail.Text); cmd.Parameters.Add("@UserName",UserName.Text); cmd.Parameters.Add("@Password",Password.Text);

cmd.ExecuteNonQuery(); int prmNum;

prmNum=cmd.Parameters.IndexOf("@ReturnValue"); if ( Convert.ToInt64(

cmd.Parameters[prmNum].Value)!=0 )

Label1.Text="Customer " + cmd.Parameters["@ReturnValue"].Value.ToString()+ " Saved!"; CustomerID=Convert.ToInt32(

cmd.Parameters["@ReturnValue"].Value); // Put a friendlier name on button this.BtnCancel.Text="Close";

catch ( System.Exception eSave ) {


finally {

private void btnDelete_Click(object sender, System.EventArgs e) {

System.Data.SqlClient.SqlConnection cn; System.Data.SqlClient.SqlCommand cmd;

cn=new System.Data.SqlClient.SqlConnection( "server=localhost;" +

"Integrated Security=SSPI;Initial Catalog=GolfArticles"); cmd=new SqlCommand("spDeleteCustomer",cn); cmd.CommandType=CommandType.StoredProcedure;

cmd.Parameters.Add("@CustomerID",CustomerID); cn.Open();


// Put a friendlier name on button. this.BtnCancel.Text="Close"; // Display confirmation... Label1.Text="Customer " +

CustomerID + " Deleted!"; // No customer anymore... CustomerID=0; doDataBind(); btnDelete.Visible=true;

catch ( System.Exception eDelete ) {


finally {

Toward the top of Listing 9-7, I declare a property named CustomerID that is persisted as part of the ViewState. I could have used a hidden field on the form, as I would have done in ASP, but using ViewState here is more convenient. The next interesting part of EditCustomer.aspx.cs is the doDataBind method. First I create two connection objects and two command objects. I actually use results from both command objects at the same time, and so I need two separate connection objects. Note once again that I'm using SqlConnection objects because I'm connecting to Microsoft SQL Server.

Once the connections are set up, I create the command objects. For the main connection to the Customer table, I set up the command to call a stored procedure, spSelectCustomer, and for the connection to the State table, I set up the command to call a standard SQL select statement. After opening both connection objects and calling ExecuteReader on the main command object used for the Customer table (named cmd in the code), I do the following:



CommandBehavior.CloseConnection); The DataTextFieldproperty tells the State drop-down list, ddlState, that the field to use as the displayable text is the StateAbbreviation field. The States table has a StatelD field, a StateAbbreviation field, and a StateName field. The drop-down list component also has a DataValueField property, so I could have set these properties to different values. If I did, the rendered <OPTION> tags would have the DataValueField property as the Value attribute of the <OPTION> tag for each item, and the DataTextField property between the start <OPTION> tag and the end </OPTION> tag. Because screen real estate is limited in this example, I'm only displaying the state abbreviation, using it for both the text and the value.

The following code sets the selected index on the State drop-down list.



ddlState.Items.FindByText(dr.GetString(4))); If dr.Readreturns true, I call DataBindand then set the selected index on the ddlState drop-down list. The selected index should be set so that the value in the drop-down list is the value already in the Customer table for this customer. This looks a bit tortured, and certainly does seem to be more code than should be required, but it does work.

Tip None of the code following the call to dr.Read would work without that call. Unlike an ActiveX Data Objects (ADO) recordset, which points to the first record in the dataset upon being opened, the DataReader object in ADO.NET points just before the first record, and the Read method must be called to make the first record, if any, available. I've spent an hour once or twice trying to get data when the DataReader object hasn't been read. It didn't fail, it simply didn't have anything to display. After setting the selected index for the State drop-down list, the code sets the Text property of the CompanyName text box to the CompanyName field of dr, the DataReader object that contains the customer record I'm trying to display. I must cast the value to a string, since the returned value is an object.


Note The casting syntax used here wll look familiar to C and C++

programmers, but it might look a little strange to Visual Basic programmers. In Visual Basic .NET, the same task can be accomplished by calling the CType function—for example, CompanyName.Text = CType(dr("CompanyName"), String). After setting the CompanyName text box, the code continues with a number of similar lines of code, each setting one of the text boxes displayed on the EditCustomer.aspx page. The line to set the ContractEnds text box is a little different, since the underlying type is a DateTime object rather than a string, as follows:


((DateTime)dr["ContractEnds"]).ToShortDateString(); In this case, I'm casting the returned object to a DateTime object, and then calling the ToShortDateString method on the resulting DateTime object.

If I can't read the record I'm looking for (dr.Read returns false), I still call DataBind—in this case, to ensure that the State drop-down list is populated. If an exception occurs, I set Labell to the Message property of the resulting exception, and make the Delete button invisible. There's no sense in deleting what might not be there. The Save button is still active because, in theory, the user can still enter all the required information and attempt the save.

Because all the code involved with filling in the text boxes is located in doDataBind, the Page_Load method is fairly straightforward, as shown here:


(string)Request["CustomerID"]); doDataBind();



If this is not a postback, meaning this is the first time the user is visiting the page, I set the CustomerlD property from the value in the Request object, and call doDataBind to actually fill in the drop-down list and the text boxes. If the CustomerlD property is nonzero, I make the Delete button visible; otherwise, I make it invisible. That's all there is to Page_Load.

The BtnCancel_Click event handler is a single line, redirecting the user to the RepeaterTest.aspx page—the page that in the normal course of events is the one that got me to EditCustomer.aspx. The event handlers for the Save and Delete buttons are more complicated. We'll examine the details of the more complicated event handler, BtnSave_Click, here. You can then explore btnDelete_Click on your own. Once I establish the connection, I set up the command object to execute a stored procedure, spSaveCustomer. I next add a return value parameter by creating a SqlParameter object and setting the required properties. Additional parameters are appended to the Parameters collection, with the most interesting parameter addition being for ContractEnds, a date field, as follows:


System.DateTime.Parse(ContractEnds.Text)); Because I know that ContractEnds is a DateTime object, I parse the date using the System.DateTime.Parse method. In most cases, I'd put this specific code in an exception handler, since an exception could be thrown. I'm reasonably comfortable that an exception won't be thrown here, since I wouldn't get here unless the validator accepted the date entered. After all the parameters are set, I open the connection and call ExecuteNonQuery on the command object. After the command is executed, I check the return value, which is set to the CustomerlD, either the one passed in, in the case of a saved record, or the new CustomerlD, if this is a new record.

Note As with ADO, any command that executes a query that returns records—for example, a record returning stored procedure called using the ExecuteReader method of the SqlCommand object—will not allow you to retrieve return codes or output parameters until the object getting the records—for example, the DataReader object—is closed.

I let the stored procedure tell me whether it's a new record, as indicated by the code for spSaveCustomer shown in Listing 9-8. Listing 9-8 The spSaveCustomer stored procedure used to save a row in the Customer table

CREATE PROCEDURE spSaveCustomer @CustomerID int, @CompanyName nvarchar(50), @Address nvarchar(50), @City nvarchar(50), @State nvarchar(10), @PostalCode nvarchar(20), @ContractEnds datetime, @ContactFirstName nvarchar(50), @ContactLastName nvarchar(50), @ContactEMail nvarchar(128), @UserName nvarchar(50), @Password nvarchar(50) AS


SELECT @Ret=CustomerID FROM Customer WHERE [email protected]


INSERT INTO Customer( CompanyName , Address , City , State , PostalCode , ContractEnds , ContactFirstName , ContactLastName ,

ContactEMail , UserName , [Password] ) VALUES( @CompanyName , @Address , @City , @State , @PostalCode , @ContractEnds , @ContactFirstName , @ContactLastName , @ContactEMail , @UserName , @Password ) — Be careful about triggers and @@Identity SET @[email protected]@Identity END ELSE BEGIN

UPDATE Customer SET [email protected] , [email protected] , [email protected] , [email protected] , [email protected] , [email protected] , [email protected] , [email protected] , [email protected] ,

[email protected] , [Password][email protected] , — Set modified date conveniently DateModified=GetDate() WHERE

[email protected]

IF @@Error=0 BEGIN

Return(@Ret) END ELSE BEGIN Return(0)

The stored procedure shown in Listing 9-8 is reasonably straightforward. Rather than having two separate stored procedures, one for an insert and one for an update, spSaveCustomer decides at runtime whether an INSERT or an UPDATE is appropriate. If this is a new customer, I insert the new record and return the @@IDENTITY value, which will be the last value inserted.

Tip If you're using SQL Server 2000, you can use IDENT_CURRENT or

SCOPE_IDENTITY, which might be better under some circumstances. The @@IDENTITY value can be misleading if the table in which you've just inserted a record has a trigger that inserts a record in another table with an identity column. If the save succeeds, I change the Label1 Text property to reflect that, and change the caption of the Cancel button to Close, reflecting that the change has already taken place and that exiting the page now won't discard any changes.

Within the InitializeComponent method, Visual Studio .NET writes the code to add event handlers for the buttons. When you're in the designer, double-clicking the button will add the code to add a Click handler and display the newly created method in the code editor. A couple of minor enhancements would be appropriate for this application if it were being developed in a production environment. A ValidationSummary control would allow you to more clearly state what the problem is with each of the fields. You could also use clientside code to display a confirming message box when the user clicks the Delete button. Of course, additional error handling and even error and event logging would also be helpful in a production system.

Was this article helpful?

0 0

Post a comment