The first thing we need to do is develop our stored procedure for adding users.  Within SQL this is done with an INSERT command.  Remember we have set up our RecordNumber field to be auto incrementing whenever something is inserted.  The RecordNumber is also our primary key.  In fact, we won't be able to insert directly into this field because of this.

But we will want to insert values for all of the other fields the user provides within the form.  So we will set up the stored procedure accordingly.

You want to create the following file called sp_InsertUser.asp.  Then you need to change the name of the table

yourInitials_UserRegistration

and the name of the stored procedure

yourInitials_InsertUser

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.

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.  The second two main sections of the strSQLCreate identify the fields where the the values will be placed in the table and the correspondence between these and what is inputted from the user via the ASP.

You also need to create another stored procedure that we will use throughout our web to check whether a user's inputted e-mail address conflicts with one we already have.  You should call this file sp_CheckUnique.asp.  Then you need to change the name of the table

yourInitials_UserRegistration

and the name of the stored procedure

yourInitials_CheckUnique

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.

This is used to find if there are any records already in the table with the same e-mail address.  Notice how the @email is the only input parameter.

The Form Page.  Now we to develop the form page for the user to input their stats.  You should call this page AddUser.asp.

The Database Connection Include.  Now we need to develop the ConnectToUserRegistration.asp which can be included in our ASPs when we want to connect to the database and your table.  This doesn't make any reference to the table so you don't need to change it.

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 AddNewUser.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_InsertUser

Response.Write "<center><font size = 5>You must enter all of the following <BR></font>"
Response.Write "<font size = 5>First Name and Last Name <BR> EMail Address and Password"
Response.Write "<P>Click on back to go back to the<BR>"
Response.Write "cached input form or click on<BR>"
Response.Write "<A HREF='AddUser.asp'>"
Response.Write "Return to the information input form"
Response.Write "</A></center>"
Else


' If they have entered all of the required information we want to make sure they
' are not using a username that someone else has chosen.
' While this is helped by using the username as the key
' it still is much better to catch the error before it befuddles the user

' Obtain the email address from the form
Session("EMail") = "'" & Request.Form("txtEMail") & "'"


' Instantiate a recordset object and access the info from the RegisteredUsers table

Set rsCheckUnique = Server.CreateObject("ADODB.Recordset")
' Call the stored procedure to check the uniqueness of the
' potential entry  into the database table
sqlStatement = "execute yourInitials_CheckUnique " & _
Session("EMail")

' Execute the SQL statement
Set rsCheckUnique = connUserRegister.Execute(sqlStatement)

If Not rsCheckUnique.EOF Then

' Then this e-mail address already exists in the database
Response.Write "<center><font size = 5>This email address already<BR></font>"
Response.Write "<font size = 5>exists in our database."
Response.Write "<P>Click on back to go back to the<BR>"
Response.Write "cached input form or click on<BR>"
Response.Write "<A HREF='AddUser.asp'>"
Response.Write "Return to the information input form"
Response.Write "</A></center>"

' Clean up the ADO objects
rsCheckUnique.Close
Set rsCheckUnique = Nothing

Else

' Clean up the ADO objects
rsCheckUnique.Close
Set rsCheckUnique = Nothing

' Obtain the rest of the inputs from the form
' and get them into a form so they can be
' inserted into the database
Session("FirstName") = "'" & Request.Form("txtFirstName") & "'"
Session("MiddleName") = "'" & Request.Form("txtMiddleName") & "'"
Session("LastName") = "'" & Request.Form("txtLastName") & "'"
' already obtained email
Session("Password") = "'" & Request.Form("txtPassword") & "'"
Session("DateRegistered") = "'" & Request.Form("txtDateRegistered") & "'"
Session("Age") = Request.Form("txtAge")

If Request.Form("chkECommerce") = "True" Then

Session("ECommerce") = 1

Else

Session("ECommerce") = 0

End If

If Request.Form("chkSmallBusiness") = "True" Then

Session("SmallBusiness") = 1

Else

Session("SmallBusiness") = 0

End If

If Request.Form("chkWebDevelopment") = "True" Then

Session("WebDevelopment") = 1

Else

Session("WebDevelopment") = 0

End If

If Request.Form("chkJava") = "True" Then

Session("Java") = 1

Else

Session("Java") = 0

End If

If Request.Form("chkCPP") = "True" Then

Session("CPP") = 1

Else

Session("CPP") = 0

End If

If Request.Form("chkVB") = "True" Then

Session("VisualBasic") = 1

Else

Session("VisualBasic") = 0

End If

Session("CreditCard") = "'" & Request.Form("cboCreditCard") & "'"

' Trying to deal with a radio button
If Request.Form("rbEducation") = "NoCollege" Then

Session("NoCollege") = 1
Session("College") = 0
Session("Masters") = 0
Session("PhD") = 0

ElseIf Request.Form("rbEducation") = "College" Then

Session("NoCollege") = 0
Session("College") = 1
Session("Masters") = 0
Session("PhD") = 0

ElseIf Request.Form("rbEducation") = "Masters" Then

Session("NoCollege") = 0
Session("College") = 0
Session("Masters") = 1
Session("PhD") = 0

ElseIf Request.Form("rbEducation") = "PhD" Then

Session("NoCollege") = 0
Session("College") = 0
Session("Masters") = 0
Session("PhD") = 1

End If

Session("AnnualSalary") = Request.Form("txtAnnualSalary")

' Inserting the values into the database
Set rsRegisteredUsers = Server.CreateObject("ADODB.Recordset")
' Call the stored procedure to insert the new
' item into the database table
sqlStatement = "execute yourInitials_InsertUser " & _
Session("FirstName") & ", " & _
Session("MiddleName") & ", " & _
Session("LastName") & ", " & _
Session("EMail") & ", " & _
Session("Password") & ", " & _
Session("DateRegistered") & ", " & _
Session("Age") & ", " & _
Session("ECommerce") & ", " & _
Session("SmallBusiness") & ", " & _
Session("WebDevelopment") & ", " & _
Session("Java") & ", " & _
Session("CPP") & ", " & _
Session("VisualBasic") & ", " & _
Session("CreditCard") & ", " & _
Session("NoCollege") & ", " & _
Session("College") & ", " & _
Session("Masters") & ", " & _
Session("PhD") & ", " & _
Session("AnnualSalary")

' Execute the SQL statement
Set rsRegisteredUsers = connUserRegister.Execute(sqlStatement)

Response.Write "<P><center><font size = 4><a href='RegisterUserHome.html'>" & _
"Registered Users Home</a></font></center>"

End If

End If

connUserRegister.Close
Set connUserRegister = Nothing


%>
</body>
</HTML>

The largest segment is included in an If - Then - Else - End If block that tests for whether the user has entered the required inputs. 

• If they haven't then they are displayed a message about what is missing and directed to go back to the form by one means or another.
• If they have entered the required inputs then they are put into a section of code that then
  • tests to see if the user's e-mail address has been used before using the yourInitials_CheckUnique stored procedure while passing the e-mail address  obtained from the form stored in a session variable.
  • This leads us into another If - Then - Else - End If block that tests whether the stored procedure returns any records.
    • If it does then they are directed back to the form by one way or another.
    • If e-mail address hasn't been used previously
      • all of the values from the form are read into session variables
        • notice how the non-numeric inputs are enclosed between a pair of ' marks.
        • some of the logic gets clever in order to get the values into a numeric form that SQL Server will accept
          • this is particularly true for the check boxes
          • and the radio buttons
      • then the session variables are fed into the yourInitials_AddUser stored procedure in order to add the user

Notice that even with this much error handling the logic can get quite nested.  Notice how we really needed two different stored procedures.