Callout A in Listing 3 shows the T-SQL code for the ShowMyRow stored procedure. This code has the same FROM and WHERE clause expressions as the EditMyRow stored procedure that appears at callout B of Listing 3. However, the ShowMyRow stored procedure uses a SELECT statement to return a row, while the EditMyRow stored procedure uses an UPDATE statement to modify a row. The parameter list for EditMyRow assigns a default value of NULL to @inPh if the calling routine for the stored procedure omits this argument. The sample ASP.NET application takes advantage of this feature to assign a NULL to the Phone column value when the Phone text box on the Web form page is an empty text string (""), which isn't the same as a NULL.
Reviewing the ASP.NET Code
One strength of Web applications is the ease with which users can navigate between pages. ASP.NET applications can facilitate this navigation by using hyperlinks on .htm or .html pages, but they can also programmatically control navigation between pages through the LinkButton control. On an .aspx page, this control looks like a hyperlink, but it programs like a command button in VB.NET. When a user clicks a button, a Click event for the LinkButton control sends the page from the browser session on the user's workstation to the Web server. When the page arrives at the Web server, the Page_Load event fires. ASP.NET handles the Page_Load event before control-associated events that send a page to the Web server. Therefore, you can handle a Click event for a control by using either a Page_Load event procedure or the Click event procedure.
The Default.aspx page uses the LinkButton control, its Click event, and the Page_Load event to control navigation from this default Web page. Listing 4 contains the event procedures behind the Default.aspx page. You can use Visual Studio .NET 2003 to view this code in Default.aspx.vb. You can open Default.aspx.vb from Default.aspx in the Visual Studio IDE by choosing View, Code. Callout A in Listing 4 shows the Page_Load event procedure, which performs tasks associated with the Click event for the LinkButton control (hylLogin) initially pointing at the Login.aspx page. If the value of a session variable named LoggedIn is False, the procedure configures hylLogin to point at Login.aspx. If the variable is True, the procedure removes the link from hylLogin and changes the text for the control to say that the user is already logged in.
The other three procedures in Listing 4 handle Click event procedures for three other LinkButton controls on Default.aspx. Callout B shows conditional navigation to a standard HTML Web page, Page1OfContent.htm. This type of Web page has no ASP.NET code behind it. If the user isn't logged in when clicking the link for Page 1's content, control transfers to Login.aspx to let the user log in. This redirection blocks people attempting to view Page1OfContent.htm from Default.aspx without first identifying themselves as group members. You can use alternative techniques for managing secure access to Web pages. For example, by placing the content from Page1OfContent.htm on an .aspx page, you can place code behind the page to restrict its viewing to visitors who are already logged in.
Callout C in Listing 4 shows the Click event procedures for the CurrentMembers.aspx and EditMyMemberInfo.aspx pages. These two procedures unconditionally redirect control to another Web page (which one depends on the procedure). These pages have built-in checks to verify that the user is logged in.
The Login.aspx page has two text boxes in which a user can enter ID and password values that the application compares to MemberID and MemberPW column values in the IDInfo table and a button to launch a search for the values entered in the text boxes. Listing 5 includes an excerpt of the code behind Login.aspx. The excluded content, denoted by vertical ellipses, is a section that declares and assigns values to parameters of the ADO.NET SqlCommand object cmd1. Callout A shows the ASP.NET code for opening a connection to the SqlMagMemberApp database. When running the application in your environment, change the Data Source argument in str1 so that it points at the SQL Server instance in your environment that contains the SqlMagMemberApp database. When I run this application for a group that I manage, the Data Source parameter points to an IP address for the SQL Server that contains the membership data.
Callout B in Listing 5 shows the syntax for pointing a SqlCommand object at the IsValidMember stored procedure and invoking the command object. (For more information about using the SqlCommand object, see Michael Otey's article "ADO.NET 101: SqlCommand," page 25.) The code uses the ExecuteNonQuery method for the cmd1 object because the IsValidMember stored procedure doesn't return a result set. Callout C shows the code for processing the return status value from the IsValidMember stored procedure. The @OK parameter represents the return status value. If the parameter is 1, the code sets the LoggedIn session variable to True; otherwise, the value of this session variable is False.
If a site visitor clicks the third LinkButton control on Default.aspx after logging in, the application presents the CurrentMembers.aspx page that Figure 3, page 23, shows. This page presents in a DataGrid control the list of current members in the SqlMagMemberApp database. The data comes from the CurrentMembers view in the SqlMagMemberApp database. Which members appear depends on the current date relative to the member expiration date. Figure 3 shows the DataGrid in a browser session that I ran for this article.
The code behind CurrentMembers.aspx starts by redirecting control to the NotLoggedIn.htm page if a visitor isn't already logged in; this redirection restricts the functionality on the current page to current members. Next, the code behind CurrentMembers.aspx forms a database connection based on the syntax at callout A in Listing 5. Listing 6, page 23, shows an excerpt of the code behind CurrentMembers.aspx. The code declares a SqlCommand object (cmd1) based on the CurrentMembers view and uses the SqlCommand object as the SelectCommand property setting for an ADO.NET SqlDataAdapter (dap1). This data adapter serves as a pump to populate the das1 DataSet that is the source for the DataGrid control in Figure 3.
Figure 4 shows the Design view of the EditMyMemberInfo.aspx page. The controls on this page show the MemberID and MemberPW column values for the current member and offer a text box for specifying a new password. Clicking Update Password revises the password on the form and in the SqlMagMemberApp database. A current member can update his or her contact data by revising the contents of any of the four text boxes under the Update Password button and clicking the Update Contact Info button.
Each button invokes a stored procedure that updates the SqlMagMemberApp database. A click of the Update Password button invokes the EditMyPassword stored procedure. Clicking the Update Contact Info button invokes the EditMyRow stored procedure in one of two ways. If the text box for the Phone number (txtPhone) is empty (""), the code invokes the EditMyRow stored procedure without passing a value for the @inPh parameter. This operation assigns a NULL value to the Phone column value for the row in the ContactInfo table. If txtPhone contains a nonempty string, the code uses that string as the value for @inPh when calling the EditMyRow procedure. Listing 7 shows the Click event procedure for the Update Contact Info button.
Using ASP.NET with SQL Server
As you can see, ASP.NET is much more powerful than ASP. This application, based on ASP.NET and SQL Server, illustrates how to perform two fundamental database tasks, data access and manipulation, with ASP.NET. With this application, you can manage the basic data access and manipulation tasks that you need to perform for any group membership application and many other applications. I hope this article's example can be a starting point for more advanced membership applications--or an ending point for those without the time or resources to create a more sophisticated application.
End of Article
Prev. page
1
2
[3]
next page -->
