Exploring the Database Objects
The ASP.NET application requires users to log in before they can do anything except navigate to Default.aspx. The initial navigation to this page in a Web session presents a link to Login.aspx, which calls the IsValidMember stored procedure. Later in the article, I show you how to write the VB.NET code to invoke the stored procedure and process its return status value. But first, let's see how the database objects function.
Listing 1 shows the T-SQL code for the IsValidMember stored procedure. In the case of a successful login, the @inID and @inpassword parameters designate MemberID and MemberPW column values for a row in the IDInfo table. With one exception, the stored procedure returns a value of 1 when the @inID and @inpassword parameters appear as column values for a row in IDInfo. Otherwise (i.e., if the parameters aren't column values in IDInfo), the IsValidMember stored procedure returns 0. Even if the @inID and @inpassword parameters match the column values in an IDInfo row, the return status value can still be 0 if the ExpiryDate column value for the row's MemberID value in the ExpiryDates table is before today's date.
To implement its logic, the IsValidMember stored procedure uses a SELECT statement with an inner join between the IDInfo and ExpiryDates tables. The WHERE clause arguments test for the required matching conditions. An IF statement transfers control to the final statement, which returns a value of 1 (logged in) or 0 (not logged in).
The code behind Login.aspx doesn't rely on T-SQL syntax for invoking the IsValidMember stored procedure. Nevertheless, when debugging ASP.NET applications that call stored procedures, you might want to test a stored procedure in Query Analyzer to confirm its operation. The following code shows one approach to testing the stored procedure:
DECLARE @return_status int
Exec @return_status = IsValidMember 'HN1', 'HDoe1'
SELECT @return_status AS 'Return Status'
The value of @return_status will be 1 whenever HN1 and HDoe1 are the MemberID and password values in a row in the IDInfo table.
The CurrentMembers view uses the same kind of SELECT statement as the IsValidMember stored procedure uses. In particular, both SELECT statements join the ExpiryDates table with another table, and the WHERE clause includes a criterion based on a comparison between the ExpiryDate column value and today's date. One distinction between the view and the stored procedure is that the view returns from the ContactInfo table a result set containing column values for FirstName, LastName, EmailAddr, and Phone. With the view's result set, you can populate a DataGrid control that shows the contact information for all current members.
Besides validating membership status, the SqlMagMemberApp application lets users modify their passwords and personal contact data. The EditMyPassword stored procedure in Listing 2 shows the T-SQL syntax that enables user-password updates. The procedure takes two input parameters. The @inID parameter designates the MemberID column value from the IDInfo table whose MemberPW column value should change. The @inpassword parameter specifies the new password. An UPDATE statement assigns the new password value. Although the stored procedure uses qualifiers (ii and ed) that denote IDInfo and ExpiryDates, respectively, for expressions in the ON and WHERE clauses, a qualifier is inappropriate for the UPDATE statement's SET clause because the column names in the SET clause must be from the table you list after the UPDATE keyword.
The ShowMyRow and EditMyRow stored procedures work together in the ASP.NET application. The ShowMyRow stored procedure provides the data to populate text boxes that show contact data for the member identified in the current ASP.NET session. The EditMyRow stored procedure takes input parameters, which the application supplies from text boxes, and updates a row in the ContactInfo table with a MemberID column value that matches the current session's member.
Prev. page
1
[2]
3
next page 
