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


and the name of the stored procedure


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"


' Creating the SQL String to create the stored procedure
yourInitials_RetrieveAllUsers AS " & _
"select * from


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



<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include File="adovbs.inc"-->
<!--#include File="ConnectToUserRegistration.asp"-->


' 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 "<P>" & "<center><font size = 5><b>" & "The Registered Users" & "</b></font></center>"
Response.Write "<P>" & "<table align = center border = 2><tr><td><font size = 4><b>" & "Name" & "</b></font></td>"
Response.Write "<P>" & "<td><font size = 4><b>" & "<center>E-Mail<BR>Address</center>" & "</b></font></td>"
Response.Write "<P>" & "<td><font size = 4><b>" & "<center>Registration<BR>Date</center>" & "</b></font></td>"
Response.Write "<P>" & "<td><font size = 4><b>" & "<center>Age</center>" & "</b></font></td>"
Response.Write "<P>" & "<td><font size = 4><b>" & "<center>Credit<BR>Card</center>" & "</b></font></td>"
Response.Write "<P>" & "<td><font size = 4><b>" & "<center>Annual<BR>Income</center>" & "</b></font></td>"
Response.Write "<P>" & "<td><font size = 4><b>" & "<center>Interests</center>" & "</b></font></td>"
Response.Write "<P>" & "<td><font size = 4><b>" & "<center>Education</center>" & "</b></font></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><font size = 4>" & rsRegisteredUsers("FirstName") & _
" " & rsRegisteredUsers("MiddleName") & _
" " & rsRegisteredUsers("LastName") & "</font></td>"
Response.Write "<td><font size = 4>" & rsRegisteredUsers("EMail") & "</font></td>"
Response.Write "<td><font size = 4>" & rsRegisteredUsers("DateRegistered") & "</font></td>"
Response.Write "<td><font size = 4>" & rsRegisteredUsers("Age") & "</font></td>"
Response.Write "<td><font size = 4>" & rsRegisteredUsers("CreditCard") & "</font></td>"
Response.Write "<td><font size = 4>" & rsRegisteredUsers("AnnualSalary") & "</font></td>"

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

Response.Write "E-Commerce<BR>"

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

Response.Write "Small Business<BR>"

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

Response.Write "Web Development<BR>"

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

Response.Write "Java<BR>"

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

Response.Write "C++<BR>"

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

Response.Write "Visual Basic<BR>"

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

'  A structure to determine which if any radio button was selected
Response.Write "<td><font size = 4>"
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 "</font></td></tr>"



Response.Write "</font></table>"

' Clean up the ADO objects
Set rsRegisteredUsers = Nothing

Set connUserRegister = Nothing
<P><P><font size=4><a href="RegisterUserHome.html">User Registration Home Page</a>


  • 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.