We're getting to the end of our tour. But before we go and createsome fabulous pages, we need to look at a bit more SQL. You now know how to pass information to thequerystring using forms. But what good does that do you unless you can getit out of the querystring and onto the ASP page?
No problem. Just throw this line on the page.
variable=Request.Querystring("name_of_the_form_element")The only real problem with the querystring, that I'm aware of, is that theuser can see some of what's going on behind the scenes. So there may betimes whey you want to use the Post method. In that case, do this:
variable=Request.Form("name_of_the_form_element")Let's tackle the single-table Select statement, which clearly won'tcause someone like you anyproblems. You'll just look at it and laugh at the simplicity. Well, stoplaughing.It's just ASCII, and you look weird.
Here are a couple tables we went over inLesson 2 called Clients and Cars.
| clientID | fname | lname | email | carID |
| 85 | John | Doe | jdoe@whatever.com | 45 |
| carID | carname | carrate |
| 45 | Buick LeSabre | 60 |
Here we have two tables that, for obvious reasons, we'd like to join onthe carID field. First, let's retrieve an ADO recordset that lookslike this:
| clientID | fname | lname | email | carID | carName | carrate |
| 85 | John | Doe | jdoe@whatever.com | 45 | Buick LeSabre | 60 |
What we need to do is join these tables based on an equality of thecarID field. This is what's known as an equi-join, reasonablyenough. Our SQL statement needs to reflect that. Below I'm going to buildthe proper SQL statement for this join, while loading the statement into avariable called SQL. Note that the ampersand (&) means concatenate (i.e.,tack on to the end of).
SQL="SELECT fname, lname, email, carID, carName, carrate " SQL=SQL & "FROM Clients, Cars "SQL=SQL & "WHERE email = 'jdoe@whatever.com' "SQL=SQL & "AND Clients.carID = Cars.carID"
Most of this statement should be familiar. Note that, in the secondline, we mention the two tables from which we're calling information,Clients and Cars. In the final line, Clients.carID = Cars.carIDstates the equality we're looking for: The carID field in the Clientstable has to be equal to the carID field in the Cars table.
Not too tough. Using this technique, you'll be able to join abunch of tables. I recently created a database that required a five-tablejoin. Itwasn't difficult, but the text does get bulky, and trying to find a typocan be a major pain.
The equi-join is all we're going to use in our parallel parking Website. But be aware that many other types of joins are possible. The moreyou get into databasing, the more you'll find the need for these techniques.Again, for more information, pick up Michael J. Hernandez's book.
Aside from SELECT, there are two other SQL statements you'll needto learn. The first is INSERT. This, as you can probably guess, puts abrand-new record into a table. The syntax is:
INSERT INTO TableName(column_name1, column_name2, column_name3) VALUES(value1, value2, value3)
If you're making an insertion into a text field, the value will need to besurrounded by single quotes. Numeric fields don't need them.
These statements will always be assembled with variables, which areusually grabbed from the querystring. Let's assume a form was used togather the values for fname,lname, position, email, and carID. The phrase would look like this:
fname=Request.Querystring("fname")lname=Request.Querystring("lname")position=Request.Querystring("position")email=Request.Querystring("email")carID=Request.Querystring("carID")SQL="INSERT INTO Clients (fname, lname, email, carID) VALUES (" SQL=SQL & "'" & fname & "', "SQL=SQL & "'" & lname & "', "SQL=SQL & "'" & position & "', "SQL=SQL & "'" & email & "', "SQL=SQL & carID & ")"Take a look at how this single expression was built, step by step. Justso you know, Icould have included everything in one really long line with a bunch ofampersands, but that would be a bear to troubleshoot. Trust me, this sort ofconstruction is the way to go.
So far, we know how to query the information and how to insert newrecords. But we don't know how to update a record that's already in thedatabase. And we're going to need to do that. Let's take a look at thefabulous UPDATE statement.
Here, the general syntax is UPDATE TableName SET column1=value1,column2=value2 WHERE column3=value3. Using variables to build anUPDATE statement for the Clients table would look like this:
SQL="UPDATE Clients SET staffID=" & staffID & ", "SQL=SQL & "fname='" & fname & "', "SQL=SQL & "lname='" & lname & "', "SQL=SQL & "position='" & position & "'"SQL=SQL & "carID=" & carID & " "SQL=SQL & WHERE email='" & email & "'"
Keep in mind that the column name in the WHERE portion of the statementshould be the primary key of the table. If you don't understand why, youshould go back and reread Lesson2.
next page»
