Using MySQL with ASP

In this article I will show you how to use MySQL with ASP. It is often said that open source and commercial software are not the best of friends. But in this case you will see that there are exceptions.

Contributed by David Web
Rating:

/ 33
October 14, 2008

Rate this Article:
MEH MEH++

SEARCH ASP FREE

MySQL database server, available from http://www.mysql.com/ .
ODBC 3.51 (also available from link above) - This is the driver that will enable you to connect to MySQL from within ASP.
Any MySQL Client (for example, Mysqlfront available from http://www.anse.de/mysqlfront/).
An IIS server (I'm using IIS5) or a web server that is capable of rendering ASP pages.

Now install all of them and restart your computer to make sure that everything is properly set up. After you have finished setting up the MySQL database server and the client, create a new database, add the following SQL and run it.

# Database : `userinfo`

# --------------------------------------------------------

# Table structure for table `guests`

CREATE TABLE `guests` (

`gid` int(4) NOT NULL auto_increment,

`name` varchar(20) NOT NULL,

`comment` text NOT NULL,

`dateAdd` date NOT NULL,

PRIMARY KEY (`gid`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

# Dumping data for table `guests`

INSERT INTO `guests` VALUES (1, 'Dennis Brown', 'Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.', '2008-07-31');

INSERT INTO `guests` VALUES (2, 'Xuros', 'sed diam voluptua. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua.', '2008-07-31');

The SQL above simply creates a table called guests, from which you will be retrieving data shortly. Also, please write down the user name and password that you create during the installation process; you will need it in the next section.

The next step is to set up a system DNS. Open up the control panel on your computer and then go to Administrative Tools->Data Sources (ODBC). A screen that looks like this should come up:

Click on the System DSN tab and then add the button. You should now see a window that looks something like this:

Scroll down, select MySQL ODBC driver and click "Finish." You should now come to a window that looks something like this:

What you need to fill in is the user name and password. If you have installed MySQL 5, your username will almost certainly be "root." As for the password, you will be the only one who knows what it is. Once your password and user name is filled in, you will be able to view a list of databases from the drop-down box. Select the userinfo database from there. Click OK until all the dialog boxes are closed.

Connecting with ASP

After all that hard work setting up the connections, you probably want something a little light to do. Well, that's just what it is to connect to a MySQL database from ASP. It is very simple. Below is the code that does the job:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

</head>

<body>

<tr>

<td colspan="2" class="welcomeheader">Welcome to Davids Website </td>

</tr>

<tr>

Set conx = Server.CreateObject("ADODB.Connection")

Set rs = Server.CreateObject("ADODB.Recordset")

conx.Open "DSN=mysqlasp" ' Data source name

strSQL = "SELECT * FROM guests"

' Execute SQL statement

Set rs = conx.Execute(strSQL)

Do while not rs.eof

<tr>

</tr>

<tr>

<td bgcolor="#999999"><strong>Comment:</strong></td>

</tr>

<tr>

</tr>

<tr>

</tr>

rs.MoveNext

loop

conx.close ' Close database connection

Set conx = nothing 'obj variable released

</table>

</td>

</tr>

<tr>

</tr>

</table>

</body>

</html>

Script Explained

To start with, we declare that we are using the VBScript language, and then we start the HTML page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

</head>

<body>

<tr>

<td colspan="2" class="welcomeheader">Welcome to Davids Website </td>

</tr>

<tr>

Then we build a table that will host the entries of the guest book:

Then we finally start the ASP. First we set up the connections:

Set conx = Server.CreateObject("ADODB.Connection")

Set rs = Server.CreateObject("ADODB.Recordset")

The DSN name or data source name also needs to be added in the initial connection set up stage:

conx.Open "DSN=mysqlasp" ' Data source name

We then start to build the SQL query that will extract all the records from the database:

strSQL = "SELECT * FROM guests"

Then we execute the statement and start a do while loop. This loop will run until it reaches the end of file:

' Execute SQL statement

Set rs = conx.Execute(strSQL)

Do while not rs.eof

As it is iterating through the result set, the the code builds a dynamic set of rows that contains the name, comment and date of each entry that was made in the database:

<tr>

</tr>

<tr>

<td bgcolor="#999999"><strong>Comment:</strong></td>

</tr>

<tr>

</tr>

<tr>

</tr>

Finally, the database connection is closed and the conx variable is released:

rs.MoveNext

loop

conx.close ' Close database connection

Set conx = nothing 'obj variable released

</table>

</td>

</tr>

<tr>

</tr>

</table>

</body>

</html>

Conclusion

That's it for this article. I've created an extra script for you to practice with. This script ask the user to leave a comment in the guest book. See if you can connect to the MYSQL database and add the information. Below is the HTML code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

</head>

<body>

<tr>

<td colspan="2" class="welcomeheader">Welcome to David Webs Guest Book </td>

</tr>

<tr>

<tr>

<td colspan="2" class="header">Please add a comment: </td>

</tr>

<tr>

</tr>

<tr>

</label></td>

</tr>

<tr>

<td valign="top"><strong>Comment:</strong></td>

</label></td>

</tr>

<tr>

</label></td>

</tr>

</table>

</form></td>

</tr>

<tr>

</tr>

</table>

</body>

</html>

And here's what the code produces:

The page basically presents the user with an HTML form that has two fields, one to take the user name and another that will take the comments that the user wants to leave. Once the user clicks on the submit button, the data will be sent to the ASP code. That is where you'd capture the form data and send it to the database. Happy coding!