Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

570 %>

580

590

Thank You, <%Response.Write Request("Contact_FirstName%>

600

610

The first line, like most ASPs, tells the server to use VBScript as the language in this server page. Lines

10–40 tell the browser that this is an HTML document and are followed by the HEAD tags.

The next part of code actually starts the active part of the server page (line 50). Lines 70, 80, and 90

declare the variables. It is customary to name your variable in this manner. The m designates that it is a

class variable. The rest is an abbreviation of the object’s type. The third variable is going to contain your

SQL command. It is going to be quite large, so it is best to build it into a variable and then use that

variable as the command text.

The objects are instantiated and set equal to your variables in lines 110 and 120.

The next segment of code sets the properties of the Connection object. Lines 140 and 150 set the

Command and Connection timeouts to 40. These can be higher or lower, depending on your network’s

speed. For Internet use, they should be a little higher than for intranet use. The connection string is

created in line 160. Notice that you are not using a DSN. Because this is an Internet page, you don’t

want to rely on your ISP to provide and maintain a DSN that affects your business. This is the reason

you chose to use a DSN-less connection. As you can see, you provided the driver name, database,

user identification, and password. Notice how the driver is spelled. This is one of the rare instances

where Microsoft is case sensitive. This driver name must match (both in case and punctuation) with the

name of the driver that was installed. This is an easy one—the access driver is Microsoft Access Driver

{*.mdb}. Makes MySQL look easy. The final step is to open the connection (line 170).

If you were to try opening a connection without providing all the variables beforehand, an error would

occur. These variables must be set prior to opening the connection. Order does matter.

The next segment of code, lines 180–480, creates the SQL command string. It is a normal INSERT

command. The place it starts to get interesting is when you request the values for the columns (lines

250–420). To do this, you use the built-in Request object. Remember that the POST action in your form

sent all the name=value pairs along with the page request. All you have to do is retrieve them. You

provide two checks in the code—one for gender and one for whether the user smokes (lines 330–380

and line 430–480). You do this is because these are enumerated columns and accept only two fields.

You need to massage the data to fit into the database.

You build the Command object starting on line 500. You set the command timeout (line 520) and

command type properties (line 530). You also set the connection for the Command object (line 510).

After all that is done, you set the command text equal to your SQL command (line 540) and then

execute the command (line 560). If there are no errors, the record will be added to the database.

The last little bit of code, lines 580–610, demonstrate that you can place server-side code anywhere in

your Web page. It is not recommended that you interchange server-side and client-side script very

often. It can slow down the loading of the page to the client. But for small pages that do not have a lot of

material to present, it is okay. You personalize your page here a little bit by offering a thank you to the

person who submitted an ad using his or her first name.

This page and code demonstrated how you can add data to a MySQL database using ADO, VBScript,

and Active Server Pages. What if you wanted to display some information? A different method is used

to display a data. You will need to use a Recordset object in addition to the Command and

Connection objects.