This topic is locked

Date in mySQL

12/7/2004 3:02:30 PM
ASPRunnerPro General questions
M
Matth author

Hi
Just downloaded the program and run it to test.
I could successfully connect to my mySQL-DB and create two pages to edit two tables. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=957&image=1&table=forumtopics' class='bbc_emoticon' alt=':)' />
When I then tried to format the date-fields with the datepicker I have two problems.
First the value is not returned in FireFox though it does work in IE. Well, that is not that important. But the value is returned in the local format DD/M/YYYY which is also fine, but when I save the record, mySQL does not understand the date and thus the list page works no longer as it fails with the following error:
ASP error happened
Technical information

Error number -2147467259

Error description Data provider or other service returned an E_FAIL status.

URL /admin/tbl_menu_list.asp

SQL query select `menu_id`, `menu_parent`, `pdate`, `edate`, `menu_de`, `menu_link`, `menu_type`, `menu_width`, `menu_order` From `tbl_menu`
The dates in mySQL are then also reset to Jan 1 0001!
Any ideas about that?

Sergey Kornilov admin 12/14/2004

Matth,
please download this update http://www.asprunner.com/files/asprunnerpro31a.exe which handles MySQL dates more nicely.
To achieve best results I would recommend to use one of dropdown or datepicker controls. If you prefer to use simple edit box make sure you enter date as yyyy-mm-dd which is internal MySQL date format.

M
Matth author 1/9/2005

Hi
I have downloaded that version and have still the same problem.
The date-values are shown as "12/1/2004" for Dec 1, 2004 and when I save that record, the db stores it as "01.01.0001".
When I enter the date as "2004-12-01" then it is stored correctly. How do I set it up, that I can use the datepicker?
Thanks

Matth

S
StuR 1/11/2005

Sergey,
There is a problem running 3.1a against SQL Server on dates as we go over to 2005.
Dates in 2004 display as USA dates. MM/DD/YY but dates starting in 2005 revert to UK Date format DD/MM/YY. I know that the dates in my source are all consistently UK date format.
If I use Long Date then the reverse happens and 2004 dates appear OK. ie 17th December 2004 but after 2004 a date like 11th January 2005 appears as "1st November 2005".
So, long and short date formats reverse the sense of the error.

M
Matth author 1/30/2005

Hi
Even with the registered version 3.1 the datepicker still does not work. Whenever I pick a date, it is entered as the date 1.1.0001. Therefore the listpage shows an error instead of the records.
Any ideas?
Matthias

Sergey Kornilov admin 1/30/2005

Matthias and Stuart,
let me know what date edit controls do you use.
Also I need to see exact SQL query that updates or inserts the record. To display SQL query you need to open include/..._variables.asp file and change

vDebug = false


to

vDebug = true

M
Matth author 1/30/2005

That's the sql-code it does:
update `tbl_menu` set `menu_id`=15, `menu_parent`=2, pdate='1/12/2004', edate='12/31/2199', `menu_de`='Test', `menu_link`='/home.asp', `menu_type`='', `menu_width`=125, `menu_order`=10 where `menu_id`=15
Whenever I pick a date with the datepicker, it adds it as "1/12/2004" format. But mySQL definitely wants a yyyy-mm-dd format or it will fail.
If I enter the dates manually in that format it works.
update `tbl_menu` set `menu_id`=15, `menu_parent`=2, pdate='2004-12-01', edate='2129-12-31', `menu_de`='Test', `menu_link`='/home.asp', `menu_type`='', `menu_width`=125, `menu_order`=10 where `menu_id`=15
Matthias

Sergey Kornilov admin 1/31/2005

Matthias,
please make sure you use the latest ASPRunnerPro update which always sends date to MySQL as yyyy-mm-dd when datepicker is used:

http://www.asprunner.com/files/asprunnerpro31a.exe
If this update doesn't help zip and send to support@xlinesoft.com the whole content of ASPRunner output folder ( ASP files, include files, database etc.) along with the project file. I'll find what's wrong running it on my test box.

M
Matth author 1/31/2005

Hi Sergey
I've mailed the zip-file as it still does not work.
Matthias

J
Jan 2/7/2005

Hi Sergey
I am having the same problem with oracle. The dates are not correctly initialised. I am using ASPRunnerPro31a. I did not had this problem with release 3.1
Error:

ASP error happened

Error number -2147467259

Error description [Oracle][ODBC][Ora]ORA-01861: literal does not match format string

SQL query select BEHANDELAAR, GROEPBEHANDELAAR, ACTIVITEITOMS, BEGINDATUM, PLANDATUM, EINDDATUM, RELATIENUMMER, INITIALEN, VOORVOEGSEL, ACHTERNAAM, STATUS, EIGENAAR, GROEPEIGENAAR From CRM_BHR.CRM_MV_ACTIVITEITEN where (1=1 and BEGINDATUM >='2000-0-0' and BEGINDATUM<='2000-0-0' ) and BEHANDELAAR = 'U77AVM'

500166 2/7/2005

Jan
My problem came from an old template that I built using Version 3.1. When I use a template from 3.1a then it worked for me.
Matth

J
Jan 2/19/2005

Hi,
The cause of the earlier reported problem is:

  1. The format for the date fiels is not initialized. In the dataformatdb function the variable d m and y are not set [missing else].
  2. Another problem is dat for oracle the nls_lang parameter is set to duch. Oracle expects the date in format dd-mm-yyyy. This can be handled by specifying the date fromat like: to_date('2001-01-01','yyyy-mm-dd')