1 Topic by marvo (edited by marvo 09/24/2008 12:13:27 pm)

  • From: Kenya, Nairobi
  • Registered: 09/24/2008
  • Posts: 27

Topic: Recieving Payment

I'm trying to receive payment from a customer but I'm getting the error below.
DATABASE ERROR : The debtor transaction record could not be inserted
error code : 1292
error message : Incorrect date value: '' for column 'due_date' at row 1
sql that failed was : INSERT INTO 0_debtor_trans ( trans_no, type, debtor_no, branch_code, tran_date, due_date, reference, tpe, order_, ov_amount, ov_discount, ov_gst, ov_freight, ov_freight_tax, rate, ship_via, alloc, trans_link ) VALUES (10, 12, '8', '10', '2008/09/24', '', '9', '0', 0, 6500000, '0', 0, '0', 0, 1, '', 0, '0')

What strikes me is that there's no due date in the payment form so I'm wondering why it's asking me that question.

Im using PHP 5.2.6, MYSQL 5.0.51, Apache 2.0.63 on Windows Vista

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Recieving Payment

Marvo, the payment uses the same table, and therefore is sending an empty value. Will you do me a favor, helping me test this? I think this might be a MySQL version specific issue.
In file /sales/includes/db/cust_trans_db.inc, line 79, in function write_customer_trans. The parameter $due_date=null. Will you change that to $due_date="" (empty string) and save and see if this helps? It would be very appriciated if you will help us with this.

/Joe

  • From: Kenya, Nairobi
  • Registered: 09/24/2008
  • Posts: 27

Re: Recieving Payment

Hi Joe, thanks for your quick response. i changed the value as advised but it dint work... :-/

  • Registered: 12/25/2007
  • Posts: 2,448

Re: Recieving Payment

Seems this is issue of your version of Mysql, although on 5.0.32 all works fine.
Please check if parameter $due_date='0000-00-00' will work. This is default empty value for data fields.
Janusz

  • From: Kenya, Nairobi
  • Registered: 09/24/2008
  • Posts: 27

Re: Recieving Payment

no luck itronics, below  is the error that i get.

DATABASE ERROR : The debtor transaction record could not be inserted
error code : 1292
error message : Incorrect date value: '20//' for column 'due_date' at row 1
sql that failed was : INSERT INTO 0_debtor_trans ( trans_no, type, debtor_no, branch_code, tran_date, due_date, reference, tpe, order_, ov_amount, ov_discount, ov_gst, ov_freight, ov_freight_tax, rate, ship_via, alloc, trans_link ) VALUES (10, 12, '6', '9', '2008/09/24', '20//', '9', '0', 0, 6500000, '0', 0, '0', 0, 1, '', 0, '0')


does this $SQLDueDate = date2sql($due_date); on line 89 affect the default value by setting it to  '20//' and would commenting that line affect any other part of the application?

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Recieving Payment

Please help us one step further. Instead of $SQLDueDate = date2sql($due_date);, please change into the following:

if ($duedate != "0000-00-00")
    $SQLDueDate = date2sql($due_date);
else
    $SqlDueDate = $due_date;

so it looks something like this

if ($due_date != "0000-00-00")
    $SQLDueDate = date2sql($due_date);
else
    $SQLDueDate = $due_date;

and see if this helps.

/Joe

7 Reply by marvo (edited by marvo 09/25/2008 07:14:51 am)

  • From: Kenya, Nairobi
  • Registered: 09/24/2008
  • Posts: 27

Re: Recieving Payment

seems to have worked but then there's another error,

DATABASE ERROR : The debtor transaction record could not be inserted
error code : 1366
error message : Incorrect integer value: '' for column 'ship_via' at row 1
sql that failed was : INSERT INTO 0_debtor_trans ( trans_no, type, debtor_no, branch_code, tran_date, due_date, reference, tpe, order_, ov_amount, ov_discount, ov_gst, ov_freight, ov_freight_tax, rate, ship_via, alloc, trans_link ) VALUES (10, 12, '8', '10', '2008/09/25', '0000-00-00', '9', '0', 0, 6500000, '0', 0, '0', 0, 1, '', 0, '0')

corrected this by changing $ship_via="" to $ship_via=0 on line 89

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Recieving Payment

Hello marvo,
Seems like we have found the problem. Date values and non string values can not have empty strings.
I'll install the version 5.0.51 of MySQL and try to trap eventually more errors.
The repository will be updated when this has been done.
I see that you reported another error in a different thred.
Please, marvo, if you have the time. Help with further testing.

/Joe

9 Reply by marvo (edited by marvo 09/25/2008 08:01:23 am)

  • From: Kenya, Nairobi
  • Registered: 09/24/2008
  • Posts: 27

Re: Recieving Payment

I'll faithfully do so. Thank you joe for your prompt responses. i've just encountered an issue with the balance sheet. let me just try again before i post

the balance sheet is fine...

10 Reply by itronics (edited by itronics 09/25/2008 08:16:07 am)

  • Registered: 12/25/2007
  • Posts: 2,448

Re: Recieving Payment

The reason for these problems is not MySQL version but  you have strict sql mode enabled. FA takes care over data integrity so strict mode should be off currently. See http://dev.mysql.com/doc/refman/5.0/en/ … -mode.html
Janusz

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Recieving Payment

Yes, in Windows vista, you can set the sql-mode to an empty string in my.ini (inside the MySQL folder). The filename in Linux is my.cnf. Restart the MySQL server and you are done!
However, I have decided to change about 5 to 6 files for 'sloppy' sql and thereby minimize the risk for running into this. I have been through all of FA and not found that many errors in the strict sql. I gues about 5 or 6 files. The biggest 'problem' is the data that is already in the database.
So the best advice is to set the sql-mode in the my.xxx file to empty and go with that.

/Joe

  • From: Kenya, Nairobi
  • Registered: 09/24/2008
  • Posts: 27

Re: Recieving Payment

still testing, will post any errors i come accross