SQL and Recordsets

Basics of SQL

SQL, or Structured Query Language, is the standard way of communicating with relational databases. Even though you do not know it, everything you do with the Microsoft Access GUI (like declare field names or build a query) is actually being done with SQL behind the scenes. For instance, when you create a new table in Access, the DBMS (Database Management System - ie. Access in this case) actually goes behind the scenes and uses the CREATE TABLE SQL command to create the table you specified. So, while it is not imperative that you learn every bit of SQL that exists, you should learn the fundamental commands. This is not an SQL tutorial, so I cannot teach you all of them, but we will go over what you need to get the job done, the SELECT command. Again, there are links at the end of this page for those who want to or need to learn more.

The nice thing about SQL is that, unlike other programming languages, you do not need to tell it how to extract data - you simply tell it what you want and it handles the rest. Because it is simpler, more english-like and focuses on "what" one needs to extract from the system, it is called a 4GL language (C++ and others are called 3GL). You may never need to know that, but if you are ever with programmers, you can try to look hip by flashing that little tidbit of knowledge.

As you can tell, unless you are trying to do something pretty advanced, SQL is a fairly easy language. There are a few syntactical things you should know before we examine the SELECT statement. First, there is not any "correct" format for SQL. A statement can be four lines long or one long line - SQL does not care. Second, there is no one "correct" way to use SQL commands, you can use them ALL CAPS or no caps, that is up to you, I prefer to make them ALL CAPS so I can distinguish between the command and the stuff that follows after. I also prefer to write each command per line, as you will see in the next paragraph. Please note, SQL is extremely picky about case with field names - they have to match whatever case you used in the database definition! Third, SQL is not very space sensitive. As long as you put one space in between parameters and commands, SQL is fine. If you want to put tabs, 20 spaces or whole lines, go ahead, SQL does not care. Finally, SQL statements are terminated by a semi-colon. That really is all you need to know to dig right in.

SELECT Statements

"SELECT" is a very powerful SQL command, because with it, one is able to query (or request information) from the database. We will be using SELECT exclusively for the registration database scenario - and not even anywhere close to its full potential. Let's take a look at where we left off on the code from the previous page.

dim conn, strSQL, rst
set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "dsn=wdvl_test"
strSQL = "SELECT * FROM tbl_users;"
set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, conn, 3, 3

strSQL = "SELECT * FROM tbl_users;" is about as simple of an SQL command you can get! SELECT statements are composed of the SELECT, followed by a list of field names you want to select, followed by FROM (more SQL syntax) and then the name of a table or another query from which you wish to pull the data. In this line, * is used the same way it is used by Windows - as a wildcard. When you use the asterisk in SQL, you are telling the DBMS to select all of the fields from the designated table, which is tbl_users in this example. Because we are not talking to the database directly, we assign the whole command to a variable, strSQL, which will later be sent to the database by ADO.

Now, for example's sake, let's say you just want to access the first name and email addresses of your registrants, perhaps to send out an important announcement later on. Also, we should probably order those names alphabetically from A to Z. Here is how you would do it:

SELECT First_Name
FROM tbl_users
ORDER BY First_Name;

Simple stuff! We told it what field name (note case-sensitive), what table and used "ORDER BY field_name" to sort the results from A to Z. To order them from Z to A, simply tack on a DESC (for descending) to the end of the ORDER BY line. Now suppose you wanted ADO to have that SQL command. We have not talked about sending that command to the database, but you should store it as a string in some variable to keep your code clean, but how is it possible to store three lines in one variable? You can do three things. One, crunch the SQL up into one line (strSQL = "SELECT First_Name FROM tbl_users ORDER BY First_Name;"). Two, store each line separately in strSQL, by combining strings each time:

strSQL = "SELECT First_Name " <-- note the space before the quote, 
			that is not a typo, but correct syntax.
strSQL = strSQL & "FROM tbl_users "
strSQL = strSQL & "ORDER BY First_Name;"

Or, third, you can make the string run three lines long, as illustrated by the following code:

strSQL = "SELECT First_Name " & _
	 "FROM tbl_users " & _
	 "ORDER BY First_Name;"

You can pick whichever one suits you best when working with ADO in the future. One thing I did not answer at this point is why we need all the fields in our SQL statement. The answer is due to the fact that we need some sort of outline, if you will, of the data we will be manipulating - which leads us to the ADO Recordset.

Recordsets are Good

A Recordset is basically a picture of the database, drawn by you, that can be used to manipulate data in the data source. By using the SQL statement we created above and then making a Recordset based off of it (consider the SQL statement the blue-print for the Recordset), a snapshot of the entire tbl_users is created and made available to the web page. Creating a Recordset is about as simple as a Connection, with a few more parameters.

set rst = Server.CreateObject("ADODB.Recordset") invokes an instance of the Recordset object. To actually use the Recordset, you need to define some parameters with the Open method of the Recordset object, not much unlike ConnectionString for the Connection object.

rst.Open strSQL, conn, 3, 3 opens a Recordset using the SQL stored in strSQL, the connection defined by conn, a dynamic cursor (the first 3 - more later), and an optimistic lock (the second 3).

If you can make it through this, you will have made it through the hardest part! Let's take a closer look at that Open method. The first part is fairly obvious, it needs to know a blue-print for how to build the Recordset, and that is our SQL stored in strSQL. Next, the Open method needs to know where the database is and how to connect to it - that is where our friend, conn comes in. This instance of the Connection object will give the Open method all the info it needs to create the appropriate Recordset.

The next two are a bit more vague, but understandable. Recordsets use cursors to navigate through the data they represent. Think of the cursor like a little arrow that points to where one is currently operating within the Recordset. For instance, when first opened, the Recordset's cursor points to the first record (or table row) in the set. If you do some manipulation and move to the next record (or row) then the cursor moves there as well. ADO has four cursor types which all behave in different ways. You really do not need to know those for this particular task, but know that we are using the most common one - the dynamic cursor. It allows us to read or write to any record within the set, whether the cursor is currently pointing to it or not. In other words, it allows us to jump around the set and also see changes made by other users as they occur.

The final parameter tells the Open method how it should handle record locking - or how record/rows should be dealt with when multiple people are acting on the database at the same time. Remember, this is going to be on a web site where many users may be looking at the same exact page at the same exact time, it is important that the data maintain its integrity through it all! There are four types of record locks, but again, you do not need to know those right now (see the links below for references). What you do need to know is that we are using optimistic locking (3), which means that records are only locked to other users when they are being updated/modified.

Move onto the next page and we will put this all together and have a working page!

SQL References
WDVL: Simple SQL - Getting Started with SQL
WDVL: Retrieving and Manipulating Data
SQL Tutorial
Database Central: SQL Resources

ADO is Our Friend
Part 3 - Building a Registration Database
Writing the Data to the Database