-----Original Message----- From: Neil Gamon [SMTP:NEI...@bigpond.com] Sent: Thursday, March 22, 2001 7:33 AM To: win...@lists.mysql.com Subject: MS ASP Date Problem with MyODBC driver

Dear Members Volunteer, problem getting an Microsoft ASP script SQL command executed, which compares a database field of DateTime type with a variable which contains a DateTime, if I compare the database field DateTime with the Now() Function, the SQL result is OK. How can I get this compare to work using a variable, as the variable contains a date which comes from a form that is submitted, the database is MySQL using MyODBC.

Here is the code that does NOT work using variable varEndDate using MyODBC & MySQL:

varEndDate = request("enddate") 'from submit form contains date Set Con = Server.CreateObject( "ADODB.Connection" ) Con.Open "db" set Cmd = Server.CreateObject("ADODB.Command") Cmd.ActiveConnection = Con Cmd.CommandType = 1 Cmd.CommandText = "SELECT NameOfOrderer, DeliveryName, " &_ "FROM orderheader " &_ "WHERE (orderheader.DocketDate < " & varEndDate & ")" ERROR HERE getOrderID.Open Cmd

Here is the code that works using the Now() Function using MyODBC & MySQL:

varEndDate = request("enddate") Set Con = Server.CreateObject( "ADODB.Connection" ) Con.Open "db" set Cmd = Server.CreateObject("ADODB.Command") Cmd.ActiveConnection = Con Cmd.CommandType = 1 Cmd.CommandText = "SELECT NameOfOrderer, DeliveryName, " &_ "FROM orderheader " &_ "WHERE (orderheader.DocketDate < Now())" WORKS OK getOrderID.Open Cmd

Here is code that works using CDate function to a Access Database, all OK.

varEndDate = request("enddate") Set Con = Server.CreateObject( "ADODB.Connection" ) Con.Open "db" set Cmd = Server.CreateObject("ADODB.Command") Cmd.ActiveConnection = Con Cmd.CommandType = 1 Cmd.CommandText = "SELECT NameOfOrderer, DeliveryName, " &_ "FROM orderheader " &_ "WHERE (orderheader.DocketDate < CDate('" & varEndDate & "'))" WORKS OK getOrderID.Open Cmd

Have tryed combinations of CDate, FormatDateTime functions to set variable EndDate of type DateTime, have also altered database field to type Date with no time, but still the SQL errors when using MyODBC & MySQL. The Now() Function work OK, but not the variable? The last example using CDate function to a Access database works OK, but doing this to MySQL database causes a error. The MyODBC driver will not accept the CDate function like the Access driver does. The MyODBC driver excepts the Now() function as a datetime object and SQL statment executes OK. So how can we get the MyODBC driver to except a variable of datetime submitted from the previous form?. It seems ASP runs well with Microsoft databases, but can present some unsolvable problems with others, such as MySQL. Can you help please. Reply to nei...@bigpond.com

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail win3...@lists.mysql.com instead.