Deleting Users Based on ASPs and SQL Stored Procedures

Introduction. Now we want to extend our site functionality to allow users to delete their registration information, but only theirs. Thus we require them to know both the e-mail address and password in order to have the delete be performed.

This will rely on a stored procedure to

validate the user exists in the database

based on the SQL select command

another to do the deletion

based on the SQL delete command

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

yourInitials_UserRegistration

and the name of the stored procedure

yourInitials_DeleteUser

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 %>


<%
' 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_DeleteUser " & _
"@email varchar(50), @password varchar(50) AS " & _
"delete from yourInitials_UserRegistration where EMail = @email and strPassword = @password"

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. These are used within a where clause to uniquely identify the record that should be deleted.

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_RetrieveUser.asp. Then you need to change the name of the table

yourInitials_UserRegistration

and the name of the stored procedure

yourInitials_RetrieveUser

<%@ Language=VBScript %>
<% Option Explicit %>


<%
' 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_RetrieveUser " & _
"@email varchar(50), @strPassword varchar(50) AS " & _
"select * from yourInitials_UserRegistration where EMail = @email and strPassword = @strPassword"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "<font size = 5>The SQL has executed</font>"

%>

Notice that this is based on a SELECT SQL command.

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

The Form Page. Now we to develop the form page for the user to input their e-mail address and password in order to help validate their identity. You should call this page DeleteUser.html.

<HTML>
<head><title>Validating You Are a Registered User</title></head>
<body>
<form method=POST action="DeleteUser.asp">
<center>
<font size = 5>Validating You Are a Registered User</font>
<br> <font size = 4>In order to delete 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><b><input type=reset></b></td>
<td align = left><input type=submit value = "Delete My Stats"></td>
</tr>
</table>
</font>
</form>
</body>
</HTML>

This form is so basic I won't include an image.

The Processing Script Page. Now we need to develop the DeleteUser.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 pre-developed, stored procedures. Obviously, you need to be able to anticipate certain structuring in order to develop the stored procedures before developing the ASPs.

Here is the DeleteUser.asp which we will discuss after. You need to change the name of the stored procedure

yourInitials_RetrieveUser

and the name of the stored procedure

yourInitials_DeleteUser

<%@ Language=VBScript %>
<% Option Explicit %>



<HTML>
<head><title>Deleting the Stats on a Registered User</title></head>
<body>
<center>
<font size = 5>Deleting the Stats on a Registered User
</center>

<%
Dim rsValidateInputs, rsRegisteredUsers, strEMail, strPassword, sqlStatement

' Obtaining what was enetered for the EMail and Password

strEMail = "'" & Request.Form("txtEMail") & "'"
strPassword = "'" & 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 " & _
strEMail & ", " & _
strPassword

Set rsValidateInputs = connUserRegister.Execute(sqlStatement)

If Not rsValidateInputs.EOF Then

rsValidateInputs.Close
Set rsValidateInputs = Nothing

Set rsRegisteredUsers = Server.CreateObject("ADODB.Recordset")
' Call the stored procedure to find
' e-mail and password in the database
sqlStatement = "execute yourInitials_DeleteUser " & _
strEMail & ", " & _
strPassword

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

connUserRegister.Close
Set connUserRegister = Nothing

Response.Write "<center><font size = 4><b>Your registration has been deleted</b></font></center>"

Else

connUserRegister.Close
Set connUserRegister = Nothing
Response.Write "<center><font size = 4><b>The E-Mail Address and Password were not found"
Response.Write "<BR><a href = DeleteUser.html>Try Again</b></font>"
Response.Write "</a></center>"

End If

' Give feedback that the deletion has occurred

Response.Write "<BR><a href='RegisterUserHome.html'><center><font size = 4>"
Response.Write "<b>User Registration Home Page</b></font></center></a>"

%>

</body>
</HTML>

This code can be broken down into several segments.

First we take the inputs from the form, adjust their format slightly so they will work in the stored procedure, then we use yourInitials_RetrieveUser to see if they actually exist in the table.

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

If they haven't then they are displayed a message 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

deletes the user's record via the yourInitials_DeleteUser stored procedure based on the inputted e-mail address and password.

Finally, a message is displayed if the deletion was successful.