Conclusion

One Last Pass on the Code

Just to make sure everyone is clear, the following block of code is all the ADO related stuff (minus initialization) we did and where it fits in the page. The validation and HTML form code was omitted because that has not really changed. Also, I added a confirmation message so that the user knows his/her submission was successful.

'The last field validated sits above this block...
if ErrorMsg = "" then
	strSQL = "SELECT * FROM tbl_users;"
	rst.Open strSQL, conn, 3, 3


	'Now write the data.
	rst.AddNew
		rst("Username") = username
		rst("Password") = password
		rst("First_Name") = first_name
		rst("Last_Name") = last_name
		rst("Social_Security") = social_security
		rst("Address") = address
		rst("City") = city
		rst("State") = state
		rst("Zip_Code") = zip_code
		rst("Phone") = telephone
		rst("Email") = email
	rst.Update


	'The next four lines are not necessary but help improve
	'server performance by clearing up memory.
	rst.Close
	conn.Close


	set rst = nothing
	set conn = nothing


	'Making ErrorMsg act like a confirmation message!
	ErrorMsg = "Your submission was successful, thanks for registering!"


		end if
	end if
%>
HTML Etc...

Doh! What About Duplicate Usernames?

Thought I forgot, did you? Well it is true, I almost did, but here we are anyway! Cracking down on repeat usernames is as easy as writing to the database - the only problem is that it is going to restructure part of our page a little, so pay close attention. Way back in the validation section we need to query the database to see if the username already exists. So, let's add the following snippet of code up there:

'...
'Username
re.Pattern = "\w{4}"
results = re.Test(username)


if results then
	strSQL = "SELECT Username FROM tbl_users;"
	rst.Open strSQL, conn, 3, 3


	while not rst.EOF
		if rst("Username") = username then
			duplicate = "True"
		end if
		rst.MoveNext
	wend
	if duplicate = "True" then
		errorArray(9) = "True"
		ErrorMsg = ErrorMsg & "Username<br>"
	else
		errorArray(9) = "False"
	end if


	rst.close
	'Make sure you close the recordset or you will get an error
	'when you try to open it below with a different strSQL.
else
	errorArray(9) = "True"
	ErrorMsg = ErrorMsg & "Username<br>"
end if
'...

All that we are doing is saying, "Hey, if the username matches the criteria, let's make sure it is not already in the database. If it is, let's make the array value true and send back an error, if not, we are good to go!" We do it this way because we do not care about matching it to the database if it does not pass the regular expression, that username could not possibly be in there anyway! Also, that while loop is pretty easy to use. All it does is iterate through each record in the Recordset until it comes to the EOF, or End Of File.

You Did It!

Believe it or not reader, we are done! Mission accomplished, database connected, updated and user satisfied! Of course, there is so much more that you can do with ADO. It is an extremely useful and powerful component within the ASP framework. If you want to get daring you can look up more about ADO from some of the resources I provided and start dealing with time outs, transaction-based processing and all sorts of other neat stuff! Do not forget to download the source code and database if you do not want to do it yourself and as always, Feel free to email me with questions. See you next month!

Writing the Data to the Database
Part 3 - Building a Registration Database
Using ASP for Form Handling: Part 4 - Filling the Gaps