Displaying Users Based on ASPs and SQL Stored Procedures

Introduction. Now that we have added some users to our database it can be worthwhile to display them. This will prove to be much simpler than adding a user.

Now we only need one stored procedure since we do not require someone to have registered in order to display the other users.

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

yourInitials_UserRegistration

and the name of the stored procedure

yourInitials_RetrieveAllUsers

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_RetrieveAllUsers AS " & _
"select * from yourInitials_UserRegistration"

connfoxFire.execute(strSQLCreate)

connfoxFire.Close
Set connfoxFire = Nothing

Response.Write "The SQL has executed"

%>

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 that the SQL is quite simple and does nothing more than retrieve all the records from your table. It doesn't require the ASP to supply it with any input parameters.

This will then be used by the processing script we are about to develop to display the entries in a table.

The Processing Script Page. Here is the DisplayUser.asp which we will discuss after. You need to change the name of the stored procedure

yourInitials_RetrieveAllUsers

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



<%

' Instantiate a recordset object and retrieve the info from the table
Dim rsRegisteredUsers, sqlStatement

Set rsRegisteredUsers = Server.CreateObject("ADODB.Recordset")
' Call the stored procedure to retrieve
' all of the records in the table
sqlStatement = "execute yourInitials_RetrieveAllUsers "

Set rsRegisteredUsers = connUserRegister.Execute(sqlStatement)

' Display the contents of the table
' This first section initializes the table
' and fills in the column headers

Response.Write "" & "<center>" & "The Registered Users" & "</center>"
Response.Write "" & "<table align = center border = 2><tr><td>" & "Name" & "</td>"
Response.Write "" & "<td>" & "<center>E-Mail Address</center>" & "</td>"
Response.Write "" & "<td>" & "<center>Registration Date</center>" & "</td>"
Response.Write "" & "<td>" & "<center>Age</center>" & "</td>"
Response.Write "" & "<td>" & "<center>Credit Card</center>" & "</td>"
Response.Write "" & "<td>" & "<center>Annual Income</center>" & "</td>"
Response.Write "" & "<td>" & "<center>Interests</center>" & "</td>"
Response.Write "" & "<td>" & "<center>Education</center>" & "</td></tr>"

' Now we loop through all of the table's entries
' Each record is in its own row in the table

Do While Not rsRegisteredUsers.EOF

Response.Write "<tr><td>" & rsRegisteredUsers("FirstName") & _
" " & rsRegisteredUsers("MiddleName") & _
" " & rsRegisteredUsers("LastName") & "</td>"
Response.Write "<td>" & rsRegisteredUsers("EMail") & "</td>"
Response.Write "<td>" & rsRegisteredUsers("DateRegistered") & "</td>"
Response.Write "<td>" & rsRegisteredUsers("Age") & "</td>"
Response.Write "<td>" & rsRegisteredUsers("CreditCard") & "</td>"
Response.Write "<td>" & rsRegisteredUsers("AnnualSalary") & "</td>"

' Filling in an entry for each check box that was checked
' These are all in the same column
Response.Write "<td>"
If rsRegisteredUsers("ECommerce") = True Then

Response.Write "E-Commerce "

End If
If rsRegisteredUsers("SmallBusiness") = True Then

Response.Write "Small Business "

End If
If rsRegisteredUsers("WebDevelopment") = True Then

Response.Write "Web Development "

End If
If rsRegisteredUsers("Java") = True Then

Response.Write "Java "

End If
If rsRegisteredUsers("CPP") = True Then

Response.Write "C++ "

End If
If rsRegisteredUsers("VisualBasic") = True Then

Response.Write "Visual Basic "

End If
Response.Write "</td>"

' A structure to determine which if any radio button was selected
Response.Write "<td>"
If rsRegisteredUsers("NoCollege") = True Then

Response.Write "No College"

ElseIf rsRegisteredUsers("College") = True Then

Response.Write "College"

ElseIf rsRegisteredUsers("Masters") = True Then

Response.Write "Masters"

ElseIf rsRegisteredUsers("PhD") = True Then

Response.Write "Ph.D."

End If
Response.Write "</td></tr>"

rsRegisteredUsers.MoveNext

Loop

Response.Write "</table>"

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

connUserRegister.Close
Set connUserRegister = Nothing
%>
<html>
<body>
<center>
<a href="RegisterUserHome.html">User Registration Home Page</a>

</center>
</body>
</html>

We first establish the connection to the database and create a recordset that contains all the entries in the table.

Then we develop the column header for the table.

We loop through the recordset putting each record in its own row.

Each column corresponds to a major piece of information

most of the entries are obvious

all of the interests that are checked are placed in the same column

there is an If - ElseIf - End If structure to determine which, if any, radio button was checked

The following is a display of what shows up in my table.