Using Dreamweaver MX with ASP and mySQL

This tutorial shows how to use Dreamweaver MX with ASP pages talking to a mySQL database. The only main problem you will run into is how mySQL handles dates, and this tutorial shows you how to fix that.

Most of the tutorials, snippets, and behaviors that you will find are for using PHP with mySQL. There is virtually no information on using ASP with mySQL. For many people, this is a very viable option as mySQL is a much more powerful database than using an Access file and is a free download versus having to pay for Microsoft’s SQL Server. This makes mySQL an excellent choice for small to medium sized sites that want a faster database solution.

This article will cover connecting to the database and the few things to watch out for when working with ASP and mySQL.

Requirements

Dreamweaver MX
mySQL (http://www.mysql.com)
A web browser
Microsoft IIS 5.0 or later

Getting Started

Once you have downloaded and installed mySQL, you need to create a user account for use with your web database. This technote, http://www.macromedia.com/support/dreamweaver/ts/documents/mysql_config.htm, explains how to configure mySQL and set up a site definition.

To manually create the connection string, the following example will get you started:

"Driver={mySQL}; Server=server_address; port="3306;" option="131072;" stmt=";" Database=database_name; Uid=User_Account; Pwd=User_Password"

Working with mySQL

Whether you are using Access, SQL Server, or mySQL, virtually everything you will do within Dreamweaver will be identical. All of the tutorials and code snippets all work the same. The only issue you will run into is when working with Date fields. Unlike Access and SQL Server, mySQL stores dates in a YYYY-MM-DD format although normal display functions display the dates as MM/DD/YYYY format the same as Access or SQL Server.

If you try to send a date to mySQL in the more common MM/DD/YYYY format, it will effectively be translated to 0000-00-00 rendering the saved date useless.

When using Server Behaviors to create update and insert record fields, there is no translation done, thus your dates will not function properly.

To correct this problem, we need to add some code to our pages to convert from our standard date format to mySQL’s required format.

Dreamweaver will create a list of the fields that are available for inserting and updating. We will need to look at this list to determine how to write our translation code. The following is the fields list for a simple database:

MM_columnsStr = "StartDate|',none,NULL|EventDate|',none,NULL|EventText|',none,''"

In this example, we have two date fields and a text field. Dreamweaver will eventually split this string up to create the insert and update queries, but we will analyze it to get the information we need to create our code. The string is pipe-delimited so when split into an array, it will look like this:

MM_columns(0) = StartDate
MM_columns(1) = none,NULL
MM_columns(2) = EventDate
MM_columns(3) = none,NULL
MM_columns(4) = EventText
MM_columns(5) = None,’’

While the two date fields are indicated as being in positions 0 and 2, those positions store the field name, the position following the field name will actually be used to store the field value. These means the two positions we need to convert to mySQL date format are positions 1 and 3.

In the page you are working on, find the line that reads:

' create the sql update statement

Immediately after this line, we are going to insert our custom code. The first step is to split the date from the input form into a new array

Dim TmpDate TmpDate = Split(MM_fields(1),"/")

If we had entered 02/23/1965 into our form field, our temporary array would now look like this:

TempDate(0) = 02
TempDate(1) = 23
TempDate(2) = 1965

Next, we take the new date array we created and convert it into mySQL format:

MM_fields(1) = TmpDate(2) & "-" & TmpDate(1) & "-" & TmpDate(0)

Our date now looks like: 1965-02-23

With the date now in the proper format, mySQL will accept it into its Date/Time format.

Since our example has two date fields, the final code will look like this:

' create the sql update statement
TmpDate = Split(MM_fields(1),"/")
MM_fields(1) = TmpDate(2) & "-" & TmpDate(1) & "-" & TmpDate(0)
TmpDate = Split(MM_fields(3),"/")
MM_fields(3) = TmpDate(2) & "-" & TmpDate(1) & "-" & TmpDate(0)

Once we have added this code snippet to both the data insert and data update pages, our code is now totally compliant with mySQL.

Kerry Garrison is the Product Development Manager for XO Communications (http://www.xo.com) and the publisher of Wildhobbies.com (http://wildhobbies.com), the #1 On-Line Hobby Magazine on the web. Kerry Garrison is also heavily involved in creating large dynamic websites for clients through his business, Remote Brains (http://www.remotebrains.com) and can be contacted at kerryg@wildhobbies.com or from http://kerryg.wildhobbies.com.

 

Comments

Er....what??

May 30, 2004 by jim holmes
I didn't actually understand any of that

RE: Er....what??

May 30, 2004 by Kerry Garrison
When you are using ASP pages with Access or SQL, you can use the data format of mm/dd/yyyy, however, with mySQL, the data format that you need to pass to the database is in yyyy-mm-dd.

Ah!

May 30, 2004 by jim holmes

I see! Thnx kerry. Have you written any tutorials on creating a 'cart' system using asp and mySQL?

cheers,

Jim

RE: Ah!

May 30, 2004 by Kerry Garrison
No. I typically use osCommerce for shopping cart systems. Its Open Source and quite powerful, although it is in PHP instead of ASP.
See all 6 Comments

You must me logged in to write a comment.