Two Lessons in ASP and MySQL
(Page 1 of 4 )
ASP and MySQL are designed and distributed with completely different rationale, not to mention by two very different companies. But there is a good chance that you may want these two technologies for your dynamic application, in which case you need them to 'talk' to each other. One major obstacle to this could be that they differ in their handling of dates and times. This article will show you two functions you can implement to allow them to communicate peacefully, without trying to re-configure date/time formatting in either.
I remember the initial rushed thrill that I experienced when I had the epiphany of installing MySQL on my home computer. I admit I didn't take it extremely seriously at the time, I just figured it would be an easy way of getting a nice application sandbox at home. But after using it for some time now, I am definitely a proponent of that beautiful open-source database. This article describes an unexpected situation I came up against and the two functions I whipped together to resolve those situations.
To spoil any feelings of suspense that you may be already having, I'll tell you up front that the issue was with variations in handling date and time values. But how I came to have the problem is the exciting part (well, not really exciting, but valuable in the understanding of this situation).
I decided that in my personal website and application it would be good to have some kind of user repository. No, I wasn't in any way expecting thousands of adoring fans flocking to my home page, but I was at least thinking that I could have a family and friends photo album, and they'd have to log in to see anything.
So, I build my users table, with all fields pertinent to my users, and one more to track the date and time that their user profile was created. I set the field data type to datetime, as I'm used to doing with Access and the like. Then in my user creation script, I use the following lines to create each user (I'm omitting most fields for simplicity' sake):
strSQL = "INSERT INTO users (email, pass, created) VALUES ( '" & _
strEmail & "','" & _
strPass & "','" & _
Now() & "')"
Do you see anything wrong with that? You shouldn't, because there's not. But then I take a look in my database, and everything has worked except for the datetime. All I see are a bunch of 0's! What the heck!!! OK, gathering my wits about me, I first turn with a suspicious look toward IIS; how dare it produce a string of 0's for the current date & time? But no, one line of
debug( strSQL ) 'http://www.aspfree.com/c/a/ASP/Easy-Error-Management/3/
shows me that everything is in order. So why is MySQL not storing the proper values; have I found a huge bug?
But a second glance at the string of 0's brings a small revelation. The 0's are in a very distinct format, and distinctly different than how the datetime values appear with ASP, or even in Access. MySQL uses all dashes (-) as separators, instead of slashes and colons. Hmm, so all I needed was some kind of mediator between the two, something to do the conversion, so both ASP and MySQL could be happy with their native data formatting. I'll show you how I first took care of the date, then the time, then strung 'em together.
Next: It's a Date! >>
More MySQL Articles
More By Justin Cook
/ 43 
