A frequent task for ASP developers is to create a members area style section of a website. This would normally involve registration, a login page, and then access to a section of the site that is only available to registered users. In this article James shows us how to do exactly that by using ASP and SQL Server. By the end of this article you'll have a fairly good idea of how you can implement your own members area on your site.
Creating a members area with ASP - Creating the database (Page 2 of 6 )
For this example, we are going to use a MS SQL Server database, however this doesn't mean you have to, as the SQL statements will work with Microsoft Access as well -- you'll just need to change the connection string.
Before we start writing any ASP code, create a new MS SQL Server database called testdb, and then create a new table in it called members.
Add the following columns to the members table: Name SQL Server Data Type Access Data Type Notes id int (AutoIncrement=True) AutoNumber Primary Key username varchar (20) Text (FieldSize=20) Unique password varchar (20) Text (FieldSize=20)
Let's now create a short ASP script that connects to the database. Save the following code as inc-dbconnection.asp: <% Dim objConn 'create an ADO connection object Set objConn = Server.CreateObject("ADODB.Connection") ' open the connection to the database 'sqlservername = the name of the SQL server (if used) ' accessdb = the path to the access db from this script ' username = username to connect to the db 'password = password to connect to the db ' // Use this for SQL Server objConn.Open "Driver={SQL Server}; Server=sqlservername;" & _ "UID=username;PASSWORD=password; DATABASE=testdb;" ' // Use this for an Access 2000 database objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("accessdb") ' // Use this for an Access database (earlier than 2000) objConn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & Server.MapPath("accessdb") ' we are now connected to the database %>
Now that our database has been created, we can create our registration page where new members can register online.
First, let's get the basic HTML code together: <html> <head> <title>My Website's Registration Page</title> <head> <body> <h1>Member Registration</h1> <p>Please fill out the following form to register as a member, and gain access to our members area.</p> <form action="register.asp" method="POST"> <input type="hidden" name="action" value="register"> <table border="0"> <tr> <td><b>Username</b></td> <td><input type="text" maxlength=20 name="username"></td> </tr> <tr> <td><b>Password</b></td> <td><input type="password" maxlength=20 name="password"></td> </tr> <tr> <td><b>Password Confirm</b></td> <td><input type="password" maxlength=20 name="password_confirm"></td> </tr> <tr> <td> </td> <td><input type="submit" value="Complete Registration"></td> </tr> </table> </form> </body> </html>
As you can see, this is just plain html. Now it's time to add some ASP code to make our form actually do something! First, we'll check to see if the form has been submitted. We can do this by checking the value of the action field, which is actually a hidden field in the form: 'see if the form has been submitted If Request.Form("action")="register" Then 'the form has been submitted Now we know that the form has been submitted, we need to validate it. There are a number of things we need to check:
A username has been entered
A password has been entered
The passwords are the same
That the username has not already been taken
The last item on the list cannot be done until we connect to the database, so we leave that validation until last. We can use the following code to check if these are correct. If an error does occur, we add the error message to strError.
Dim strError 'validate the form ' check if a username has been entered If Request.Form("username") = "" Then strError = strError & "- Please enter a username<br>" & vbNewLine 'check if a password has been entered If Request.Form("password") = "" Then strError = strError & "- Please enter a password<br>" & vbNewLine 'check if the passwords are the same... but don't display it if the password field is blank. If Request.Form("password") <> Request.Form("password_confirm") _ And Request.Form("password") <> "" Then _ strError = strError & "- Your passwords do not match<br>" & vbNewLine
Once we have performed the validation, we check to see if strError contains any text. If it does, an error has occurred, and we display a message. Otherwise, we can continue: If strError = "" Then 'continue End If If strError <> "" Then 'output the error message ' add extra HTML... strError = "<p><font color=""#FF0000""> The following errors occured:</font> <br>" & vbNewLine & strError End
If You may wonder why we haven't condensed this code into an If... Then... Else statement. This is because strError may well be filled inside the continue block, so we want to check to see if it isn't empty again.
Now, we need to add some extra ASP code in order to display the validation error that occurred, and also remember any text the user has input. For example: <input type="text" maxlength=20 name="username"> becomes <input type="text" maxlength=20 name="username" value="<%=Server.HTMLEncode(Request.Form("username"))%>">
Although in most instances, <input type="text" maxlength=20 name="username" value="<%=Request.Form("username")%>"> would be perfectly sufficient, we add the Server.HTMLEncode command to ensure that any 'funny' characters the user has entered (such as < and ") are still displayed correctly.
You can see the rest of the code we add in the final version of register.asp. For now, we'll move on to creating the user's entry into the database (and checking if the username has been taken or not). At this stage, we need to include the database connection code: <!--#include file="inc-dbconnection.asp"--> and now we can execute an SQL statement to create the new record: On Error Resume Next sSQL = "INSERT INTO members (username,password) VALUES " & _ "('" & fixQuotes(Request.Form("username")) & "','" & _ fixQuotes(Request.Form("password")) & "')" cConn.Execute sSQL
You will notice that within the SQL statement, we call a fixQuotes procedure. This procedure replaces all occurrences of ' with '' in the username and password fields. If we didn't do this, an error would occur when a user entered a ' within the username or password field.
Once this statement has been executed, we need to check if an error has occurred; if it has, there is probably a conflict with an existing entry in the database (i.e. the username is already in use). If Err.Number = 222 Then strError = "- That username is already in use. Please choose another<br>" & vbNewLine Else If Err.Number <> 0 Then strError = "- An error occured. " & Err.Number & " : " & Err.Description & "<br>" & vbNewLine Else 'restore standard error handling On Error Goto 0 ' record created... redirect Response.Redirect "login.asp?msg=" & _ Server.URLEncode("Thank you for registering. Please log in using your new username and password") Response.End End If 'restore standard error handling On Error Goto 0
And that's it! Our registration form is complete. Now we can move on to create our login form.
ASP 
/ 13 
Hey... I Remember You!
