• From: Canada, Kitchener, Ontario
  • Registered: 02/03/2009
  • Posts: 24

Topic: SQL Database Error on Direct Invoice Entry

Hi Joe,

Really appreciate your hardwork in this wonderful and simple software. Thank you very much for that. However I am facing trouble entering some data in your Sales module.

I am getting error on entering Direct Invoice Entry in the system. Error is as follows

DATABASE ERROR : FATAL : sales order return nothing - 0
sql that failed was : SELECT 0_sales_orders.*, 0_debtors_master.name, 0_debtors_master.curr_code, 0_locations.location_name, 0_debtors_master.payment_terms, 0_debtors_master.discount, 0_sales_types.sales_type, 0_sales_types.id AS sales_type_id, 0_sales_types.tax_included, 0_shippers.shipper_name, 0_tax_groups.name AS tax_group_name , 0_tax_groups.id AS tax_group_id FROM 0_sales_orders, 0_debtors_master, 0_sales_types, 0_tax_groups, 0_cust_branch, 0_locations, 0_shippers WHERE 0_sales_orders.order_type=0_sales_types.id AND 0_cust_branch.branch_code = 0_sales_orders.branch_code AND 0_cust_branch.tax_group_id = 0_tax_groups.id AND 0_sales_orders.debtor_no = 0_debtors_master.debtor_no AND 0_locations.loc_code = 0_sales_orders.from_stk_loc AND 0_shippers.shipper_id = 0_sales_orders.ship_via AND 0_sales_orders.order_no = 5

However, the entry gets posted into 0_sales_order table with some values.

If these details are insufficient, kindly let me know what more details you would like to have.

Regards,

Harshal

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

Re: SQL Database Error on Direct Invoice Entry

Hello,
Which release are you running?

/Joe

  • From: Canada, Kitchener, Ontario
  • Registered: 02/03/2009
  • Posts: 24

Re: SQL Database Error on Direct Invoice Entry

joe wrote:

Hello,
Which release are you running?

/Joe

2.1 beta

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

Re: SQL Database Error on Direct Invoice Entry

Have you at least one entity defined in  sales type, location, shipper and tax_group?

Janusz

  • From: Canada, Kitchener, Ontario
  • Registered: 02/03/2009
  • Posts: 24

Re: SQL Database Error on Direct Invoice Entry

itronics wrote:

Have you at least one entity defined in  sales type, location, shipper and tax_group?

Janusz

Yes I have
2 Sales Type
2 Locations
1 Shipper
1 Tax Group

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

Re: SQL Database Error on Direct Invoice Entry

So I don't know. If you have in sales order #5 in your database all should work. If you are familiar with SQL you can copy-paste  the SQL displayed in message to phpmyadmin SQL tab and play with it to find why the query result is empty.

Janusz

  • From: Canada, Kitchener, Ontario
  • Registered: 02/03/2009
  • Posts: 24

Re: SQL Database Error on Direct Invoice Entry

Hi Janusz,

I have couple of questions. Trying to debug what can the possible error be:

1. When I am clicking "Direct Sales Invoice" my "CASH PAYMENTS" section is not editable. I see nothing in following 2 fields:
Deliver from Location and Cash account.

can you tell me why can this be. I have a cash account set up. the payment terms has a value "Cash Only" and due by has 0 set.
I have 2 locations and why are they not populated in the form.

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

Re: SQL Database Error on Direct Invoice Entry

Every user has assigned POS definiton in setup. If the user's POS has no allowance for delayed payment transactions there is no respective cash/delayed payment selector in Direct Invoice header, and the location and cash account displayed at the bottom is get from POS definition. Probably you have something broken in user/POS setup. You have to have at least one POS defined and assigned to user.

Janusz

  • From: South Africa
  • Registered: 03/02/2009
  • Posts: 4

Re: SQL Database Error on Direct Invoice Entry

I get  this exact same error. It started after I've upgraded from 2.1beta to 2.1RC and still no change 2.1stable. Is there a quick way of getting to the problem?

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

Re: SQL Database Error on Direct Invoice Entry

Well, which one error you have? Database error from post #1 or the problem described in post #7? Both seems to be result of some fails during db upgrade. The first problem cannot be solved without access to local database (see advice in post #6).

Janusz

  • From: South Africa
  • Registered: 03/02/2009
  • Posts: 4

Re: SQL Database Error on Direct Invoice Entry

The error in the first post. It was a database error. I've exported all the important info and recreated the database. I've noticed that some of the functions like Cash/Delayed in Direct Invoice didn't show up until I fixed the DB.

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

Re: SQL Database Error on Direct Invoice Entry

Could you explain me how have you fixed the problem? I still cannot find the source of the trouble nor reproduce it.

Janusz

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: SQL Database Error on Direct Invoice Entry

Janusz,

The error: (MySQL returned an empty result set (i.e. zero rows)) is coming from statement (AND 0_locations.loc_code = 0_sales_orders.from_stk_loc) in looking at the tables, even thou enters are being made in 0_sales_orders, there are no enters in column (from_stk_loc)

What you think?

Alvin

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • Registered: 12/25/2007
  • Posts: 2,446

Re: SQL Database Error on Direct Invoice Entry

Were the fatal records with empty from_stk_loc added after upgrade, or before? Anyway it is not good, but we should know this to decide how to deal with this problem.

Janusz

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: SQL Database Error on Direct Invoice Entry

I am not sure, the file producing the error is a new file. I have a db file that produces the error (new) and one the does not (old), I did a text diff on them

this is non error diff

for table `0_chart_master`
`tax_code` int(11) NOT NULL default '0',

for table `0_company`
`custom0_name` varchar(60) NOT NULL default '',
  `custom0_value` varchar(100) NOT NULL default '',

I tried to get the non error file to produce the error, but could not.  I tried to get the error file to not produce the error but could not.

I tried everything I could, the results are the same using script 2.1 and 2.2

Maybe I could send you the db files and you could play with them?

Let me know, thanks

AM

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • Registered: 12/25/2007
  • Posts: 2,446

Re: SQL Database Error on Direct Invoice Entry

You can send my the db files by email, but once again the date of broken records entry in relation to version upgrade date is important

You have found that the source of error is empty sales_orders.stock_from_loc in some records. Can you find whether those records were added to database BEFORE upgrade, or AFTER? You certainly have db dump made before version upgrade, so it should be easy to find.

When the empty stock_from_loc were in original database, we should deal with bad data during upgrade. Otherwise we have some bug in new code resulting with incorrect sales_order records.

Janusz

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

Re: SQL Database Error on Direct Invoice Entry

Hello,
This ugly upgrade bug has been finally localized and fixed. Main CVS branch updated.

Thanks Alvin for your kindly help.

Janusz

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: SQL Database Error on Direct Invoice Entry

So how can I delete default location and not get this error?

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • Registered: 12/25/2007
  • Posts: 2,446

Re: SQL Database Error on Direct Invoice Entry

1. update source to latest main CVS version - Default POS will be visible
2. enter all defined users, set right POS and update them
3. void broken sales orders
4. delete Default location and POS if you don't want to use them.
Janusz

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: SQL Database Error on Direct Invoice Entry

no, that did not do it, still got the error

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • Registered: 12/25/2007
  • Posts: 2,446

Re: SQL Database Error on Direct Invoice Entry

Well, in fact broken sales orders still can be not readable. I guess the records are also unusable, as they are simply artifacts of failed Direct Invoice entry. If you want get rid with them - do it in phpmyadmin. If you prefer to keep them for some reason change all empty from_stk_loc to some real location.loc_code value from your database.
Janusz

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: SQL Database Error on Direct Invoice Entry

There are no sales orders in the the system,   `0_sales_orders`MySQL returned an empty result set (i.e. zero rows).

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • Registered: 12/25/2007
  • Posts: 2,446

Re: SQL Database Error on Direct Invoice Entry

Well, I'm lost. I have checked procedure from post 19 together with manual setting described in post 21 using sql data file you've send me, and all works right now. I can entry Direct Invoices and read all sales orders without errors.

In post 19 point 3 is obsolete, as voided SO are also in inquiry result and without fixing from_stk_loc are read with errors. So change form post 21 is also necessary.

Janusz

24 Reply by alvin (edited by alvin 03/30/2009 04:55:01 am)

  • alvin
  • Senior Member
  • Offline
  • From: USA, Guatemala, Honduras
  • Registered: 05/20/2008
  • Posts: 301

Re: SQL Database Error on Direct Invoice Entry

In post 19, item 4, you must also change all other pos to other locations (da)  do this before you go and delete locations,

I think this is were the problem was, but works now... on my local that is,  I still get the error no my hosted, same db file, same scripts?

May there is some server side cache or something, I will try later... ok, maybe some browser cache, but all is good, I think this error is gone for good.

Alvin smile and smile

"The roots of education are bitter, but the fruit is sweet."  - Aristotle.
  • Registered: 03/30/2009
  • Posts: 1

Re: SQL Database Error on Direct Invoice Entry

I am having this problem too. to reproduce the error:

1. install version 2.0.x (i m using 2.0.4 and 2.0.7 to be exact).
2. upgrade to 2.1
3. follow the upgrade procedure here: https://frontaccounting.com/wbt/pages/download/update-guide.php
4. login to frontaccouting as admin
5. click on "Direct Invoice"

------------------------- Error Message ----------------------------------------------------------
DATABASE ERROR : could not get POS definition
error code : 1064
error message : You have an error in your SQL syntax near 'AND pos.pos_location=loc.loc_code AND pos.pos_account=acc.id' at line 1
sql that failed was : SELECT pos.*, loc.location_name, acc.bank_account_name, acc.account_code FROM 0_sales_pos as pos,0_locations as loc,0_bank_accounts as acc WHERE pos.id= AND pos.pos_location=loc.loc_code AND pos.pos_account=acc.id
------------------------- Error Message ----------------------------------------------------------

Hope can help.

TQ