|Written by||Imar Spaanjaars|
Are you looking to hire an experienced software developer or .NET consultant? Then get in touch with me through my company's web site at devierkoeden.com
Found an interesting article on this site? Got inspired by something you read here? Then consider making a donation with PayPal.
Like this article? Or do you think it sucks? Make yourself heard by casting your vote below.Total number of ratings: 10
The first Server Behavior you need is the Insert Record behavior. This behavior will generate all the necessary HTML and ASP code to insert a new record in your database using an HTML form. In the book, a similar behavior, the Record Insertion Form Wizard was used to insert a new record in the Events table using an HTML page. Its counterpart, the Record Update Form Wizard behavior, took care of updating existing events in the database.
When you want to add a new user to your database, you're going to have one additional hurdle to take: you have to make sure that the new user you want to insert, is going to be unique. The column Name in the Users table is used to uniquely identify a user, so you can't allow duplicates. Once again, the developers of Dreamweaver has thought about this too, and created the second behavior you need for this task: the Check New Username behavior. This behavior checks in the database if the username you want to insert already exists. If it does, it won't allow the new user to be inserted.
To successfully allow your users to sign up for your site, you'll have to perform the following steps:
The rest of this article assumes you have followed along with the Soccer Site project. If you don't own the book, don't worry. It'll be easy to follow along. You won't get the same screens because you don't have the templates used in this site, but everything will work as expected. If you want, you can even download the necessary file for this article. You do have to set up a connection to your database, though, because this article assumes you have a valid connection to a Microsoft Access database on the Databases panel in Dreamweaver.
The current database for TheSoccerSite has a little problem that hasn't come up in the 7 Chapters of the book Beginning Dreamweaver MX. The column Password in the Users table has in fact an illegal name. Microsoft Access allows a column to be called Password when you create it. This is not a problem for SELECT statements, as you have seen in the login page, but it will cause problems when you use INSERT or UPDATE statements through ADO. You'll see that when you pass the following SQL statement to the database, you'll get an error:
The column name Password is actually a reserved word, although it's not officially listed on the Reserved Words for Microsoft Access list. To fix this, you'll need to change the column Password to UserPassword in the database. If you have Microsoft Access installed, perform the following steps to change the database TheSoccerSite.mdb. If you have completed the Soccer Site project, you can use the database from the last chapter, as it contains all the tables you need. If you don't already have the database, you can download it together with the code for this tutorial at the bottom of this article. In that case, there is no need to change anything and you can skip the next three steps. The database in the code download is already up-to-date.
Figure 1 - Design View for an Access Table
By making these two changes, everything is back as it was. The login page will pass UserPassword as the password column to the database, which will now happily accept this as a valid column, so your users will be validated correctly.
With the database done, it's time to create a new page and then add the Insert Record form and behavior, to allow your users to sign up on your page.
Figure 2 - The Tag Editor Dialog for the Form Tag
Now that you have a complete HTML form, you're ready to add the Insert Record behavior. This behavior will generate the necessary ASP code to insert a new record in the Users table in the database.
If you look at the code that Dreamweaver has generated for you, you may recognize it. In many respects, it's similar to the code that is used for the Record Insertion Form Wizard that was used to insert a new event in the database. The ASP code sets up a few variables that hold the column names in the database and the text boxes on your form. A SQL statement is created that retrieves the value for each column from the form fields. Once the entire SQL statement is complete, it is send to the database by using the Execute method of the ADODB Command object.
The current Sign Up form has a few problems. First of all, it is still possible to insert two users with the same username, something you shouldn't allow in your Web site. Second, when the user is inserted, it has no Access Level yet. Not only is this inconvenient, it's also problematic when it comes to security. An empty access level will pass the access check for levels like Administrators or Members. Take a look at the following code that performs an Access Level check and see how this is possible:
The previous If statement will return True when UserRole contains "Administrators". However, it will also return True when UserRole is an empty string, which will be the case when no access level has been assigned to the user. Whoooops....
To fix this security hole, you have three options:
Although the second method is really easy to implement and pretty secure, I'll discuss the third method in a bit more detail. It's not always possible to let the database decide the default value for new columns; you'll often need to append data like a date or some other value based on the current day, a login account, an order or any other business logic decision you may have to make. In the next steps, I'll show you how you can add the AccessLevel column to the SQL statement, so your users will be assigned a level of Members by default.
Figure 4 - The Users Table in the Database
Without the two lines of code you added, the variables MM_tableValues and MM_dbValues could contain something like this when you submit the form:
MM_tableValues = Name, UserPassword
MM_dbValues = 'NewUser', 'MySecret'
By appending these values to the rest of the SQL statement, you end up with a complete and valid SQL string. However, before you sent the string to the database, you added ", AccessLevel" to MM_tableValues and ", 'Members'" (including the ' characters) to MM_dbValues. This way, the entire SQL statement ended up like this:
which eventually added Members as the AccessLevel for the new users in the database.
With the Access Level done, there is still one improvement to make to this page; you'll need to prevent two users from registering with the same name. The Check New Username behavior that Dreamweaver supplies makes this very easy. The next steps will show you how to apply this behavior.
Figure 5 - The Check New Username Behavior
Figure 6 - The Sign Up Page in the Browser Showing the Duplicate Username Errormessage
To see what makes this magic happen, let's take a look at the code that Dreamweaver has inserted for you:
This block of code first checks if the form has been submitted to the server. If it has, MM_flag will have a value (it's retrieved from a hidden form field, MM_insert). Then the page that the user is redirected to if they try to claim a name that has already been taken is set up, followed by the connectionstring used for the database lookup. In the last line of this block, a SQL statement is created that will simply perform a SELECT on the Users table to find out if the name is already in use by passing the username from the form to the WHERE clause.
The next block of code creates and initializes an ADODB Recordset object:
The ActiveConnection is set to MM_rsKeyConnection, that points to the database used for this example. Once the Recordset is set up, it is opened using the Open method.
With the Recordset open, the code checks if the the recordset contains the requested username. If it does, the EOF property of the Recordset will be False, so the code in the If block will run. Inside that block, the redirect string is created. The name of the page you want to redirect to is extended with a QueryString name/value pair. requsername is passed as the name and the requested username is passed as the value. This allows you to see what name was initially requested when you display the error message.
If the username was not found, the page will continue after the End If statement, so the code for the Insert Record will fire normally, adding the user to the database.
The last part that needs some explanation is the error message:
If the username was already taken, requsername will have a value, so the code in the If block will run. An error message is displayed onscreen, indicating that the requested username has already been taken.
With the Check New Username in place, you have a fully functional Sign Up page. Users can simply add their username and password and get immediate access to the restricted sections of your site. The Check New Username behavior will make sure that you don't end up with two members sharing the same username, so you're always able to uniquely identify your members. The additional code you added to the Insert Record behavior ensures that your new members are assigned the AccessLevel of Members by default. If you want to assign them a different level, you'll need to modify the code, or change the level in the database directly.
This article has just shown you the basic steps you need to perform to have a user sign up at your Web site. However, there are quite a lot of extensions possible you can add to this page:
Wonder where to go next?
You can read existing comments below
or you can post a comment yourself on this article
Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.
I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.
When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.
For more information about the Talk Back feature, check out this news item.
Unfortunately, something went wrong and your message or comments have not been submitted successfully.
There's a fair chance things broke down because you tried to post something that looks like HTML. Things that look like HTML include (X)HTML, obviously, XML, ASP.NET markup and c# generics syntax as all of them use the < and > characters.
If that's the case, try altering your message and remove anything that looks like an angled bracket. You can replace them with [ and ] for example so you can still make it look like HTML to some extend.
If, on the other hand, you were trying to spam this web site, I am pretty glad I caught you in the act and stopped you from doing so ;-)
The number you entered is not correct. Please enter the sum of the two numbers again.