Modifying User Stats Based on ASPs and SQL Stored Procedures
| Introduction.
This is the last big site functionality we need to implement. It is
also the most complicated.
It is made somewhat more complicated by the fact that the user might modify their e-mail address and password. So we need to make certain to keep track of what their e-mail and password were when they started the modification in order to identify which record in the database should be modified. Fortunately this is not a particularly big deal. But we also need to make certain the user doesn't change their e-mail address to something already in the database table. Thus we will be making use of three stored procedures with appropriate code segments.
Notice that two of these stored procedures already exist. This is another advantage of developing stored procedures. They are often used in more than one location. You want to create the following file called sp_UpdateUser.asp. Then you need to change the name of the table yourInitials_UserRegistration and the name of the stored procedure yourInitials_UpdateUser in the appropriate places and upload it to your directory that contains a copy of the adovbs.inc. Then when you execute this ASP via your browser on the web it will create the appropriate stored procedure. |
| <%@ Language=VBScript %> <% Option Explicit %> <!--#include File="adovbs.inc"--> <% ' Open a connection to our SQL Server database ' We will use the ADO Driver connection Dim connfoxFire, strSQLCreate Set connfoxFire = Server.CreateObject("ADODB.Connection") connfoxFire.ConnectionString="Driver={SQL Server}; Server=cisdev.quinnipiac.edu;" & _ "Database=ASP_SQL;UID=learn; PWD=learnSQL" connfoxFire.Open ' Creating the SQL String to create the stored procedure strSQLCreate = "CREATE PROCEDURE yourInitials_UpdateUser " & _ "@OriginalEMail varchar(50), " & _ "@OriginalPassword varchar(50), " & _ "@FirstName varchar(50), " & _ "@MiddleName varchar(50), " & _ "@LastName varchar(50), " & _ "@EMail varchar(50), " & _ "@strPassword varchar(50), " & _ "@Age int, " & _ "@ECommerce bit, " & _ "@SmallBusiness bit, " & _ "@WebDevelopment bit, " & _ "@Java bit, " & _ "@CPP bit, " & _ "@VisualBasic bit, " & _ "@CreditCard varchar(50), " & _ "@NoCollege bit, " & _ "@College bit, " & _ "@Masters bit, " & _ "@PhD bit, " & _ "@AnnualSalary money AS " & _ "update yourInitials_UserRegistration set " & _ "FirstName = @FirstName, " & _ "MiddleName = @MiddleName, " & _ "LastName = @LastName, " & _ "EMail = @EMail, " & _ "strPassword = @strPassword, " & _ "Age = @Age, " & _ "ECommerce = @ECommerce, " & _ "SmallBusiness = @SmallBusiness, " & _ "WebDevelopment = @WebDevelopment, " & _ "Java = @Java, " & _ "CPP = @CPP, " & _ "VisualBasic = @VisualBasic, " & _ "CreditCard = @CreditCard, " & _ "NoCollege = @NoCollege, " & _ "College = @College, " & _ "Masters = @Masters, " & _ "PhD = @PhD, " & _ "AnnualSalary = @AnnualSalary " & _ "where EMail = @OriginalEMail and strPassword = @OriginalPassword" connfoxFire.execute(strSQLCreate) connfoxFire.Close Set connfoxFire = Nothing Response.Write "<font size = 5>The SQL has executed</font>" %> |
| This code will execute only once to create the stored
procedure unless you get me to remove it. After executing this you
probably should delete this file from your web directory. Notice how the portion in purple shows all of the values that need to be passed to the stored procedure in order for it to insert everything the user might have supplied. It is certainly convenient that the other two stored procedures we need have already been created. A Form Page. Now we to develop the form page for the user to input their e-mail address and password. You should call this page ModifyUser.html. |
| <HTML> <head><title>Validating You Are a Registered User</title></head> <body> <form method=POST action="ModifyGetFeedback.asp"> <center> <font size = 5>Validating You Are a Registered User</font> <br> <font size = 4>In order to modify your stats</font> </center> <P><table cellpadding = 4> <tr> <td align = right><b>E-Mail</b></td> <td align = left><input type=text name="txtEMail"></td> </tr> <tr> <td align = right><b>Password</b></td> <td align = left><input type=password name="txtPassword"></td> </tr> <tr> <td align = right></td> <td align = left><input type=submit value = "Modify My Stats"></td> </tr> </table> </font> </form> </body> </HTML> |
|
This is our standard form so I won't even present an image. The Modification Form Page. Now we to develop the form page for the user to modify their stats. This page must
You should call this page ModifyGetFeedback.asp. |
| <%@ Language=VBScript %> <% Option Explicit %> <!--#include File="adovbs.inc"--> <!--#include File="ConnectToUserRegistration.asp"--> <HTML> <head><title>Modifying the Stats on a Registered User</title></head> <body> <center> <font size = 5>Modifying the Stats on a Registered User </center> <% Dim rsValidateInputs, sqlStatement ' Obtaining what was used for the email and password Session("OriginalEMail") = "'" & Request.Form("txtEMail") & "'" Session("OriginalPassword") = "'" & Request.Form("txtPassword") & "'" Set rsValidateInputs = Server.CreateObject("ADODB.Recordset") ' Call the stored procedure to find ' e-mail and password in the database sqlStatement = "execute yourInitials_RetrieveUser " & _ Session("OriginalEMail") & ", " & _ Session("OriginalPassword") Set rsValidateInputs = connUserRegister.Execute(sqlStatement) If rsValidateInputs.EOF Then
Else
<%End If%> |
The logic is pretty simple in that it chooses what
happens to the user's efforts based on whether the user's inputs are found
in the database table.
If the user reaches this page it is likely to look like the following. Notice how the check boxes, combo box and radio buttons are all correctly representing the original inputs. |
| Notice we are working with text boxes, combo boxes,
check boxes and radio buttons. We also have a variety of data types
such as numeric, money, true/false and date/time. Example code segments that make use of VBScript in ASPs to coordinate the HTML display with data read from SQL Server has been highlighted in colors.
Notice how these segments require a mixture of VBScript If - Then - Else statements with criteria based on the entries in the recordset to correctly format the HTML. The Processing Script Page. Now we need to develop the ModifyUser.asp which actually is a bit more of a modification of what we did previously than you might think. Though it largely has a lot of the same code. This is where we will make use of all our preliminary developments, stored procedures and include file. Obviously, you need to be able to anticipate certain structuring in order to develop the stored procedures before developing the ASPs. Here is the ModifyUser.asp which we will discuss after. You need to change the name of the stored procedure yourInitials_CheckUnique and the name of the stored procedure yourInitials_UpdateUser |
| <%@ Language=VBScript %> <% Option Explicit %> <!--#include File="adovbs.inc"--> <!--#include File="ConnectToUserRegistration.asp"--> <HTML> <BODY> <% ' We first want to check to make sure the user has entered all the required information Dim rsRegisteredUsers, rsCheckUnique, sqlStatement, mustGoBack mustGoBack = false If ((Request.Form("txtFirstName") = "") OR (Request.Form("txtLastName") = "") OR _ (Request.Form("txtEMail") = "") OR (Request.Form("txtPassword") = "")) Then
Else
End If |
| This code can be broken down into several segments. The largest segment is included in an If - Then - Else - End If block that tests for whether the user has entered the required inputs.
Notice that even with this much error handling the logic can get quite nested. Notice how we really needed two different stored procedures. |