Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

Notice: Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's Check/Money Order' AND cqb.qbi_paycart_mod=c.qbi_paycart_mod SQL: SELECT * FROM qbi_paycart AS c, qbi_paycart_payqb AS cqb, qbi_payqb AS qb WHERE c.qbi_paycart_text='Send Cashier's Check/Money Order' AND cqb.qbi_paycart_mod=c.qbi_paycart_mod AND cqb.qbi_payqb_refnum=qb.qbi_payqb_refnum in /home/ron-ri2/public_html/caddybug-usa/catalog/admin/includes/classes/qbi_mysql.php on line 119

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/ron-ri2/public_html/caddybug-usa/catalog/admin/includes/classes/qbi_mysql.php on line 160

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ron-ri2/public_html/caddybug-usa/catalog/admin/includes/classes/qbi_mysql.php on line 176

 

I've just bought and installed 3.10 and setup and matched products, shiping, and payments.

 

I clicked on Create iif and clicked on All Customers - Download and get the above error. I get the exact same error clicking on All Previous Orders - Download.

 

Ideas ?

 

RonR

Link to comment
Share on other sites

Notice: Query failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 's Check/Money Order' AND cqb.qbi_paycart_mod=c.qbi_paycart_mod SQL: SELECT * FROM qbi_paycart AS c, qbi_paycart_payqb AS cqb, qbi_payqb AS qb WHERE c.qbi_paycart_text='Send Cashier's Check/Money Order' AND cqb.qbi_paycart_mod=c.qbi_paycart_mod AND cqb.qbi_payqb_refnum=qb.qbi_payqb_refnum in /home/ron-ri2/public_html/caddybug-usa/catalog/admin/includes/classes/qbi_mysql.php on line 119

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/ron-ri2/public_html/caddybug-usa/catalog/admin/includes/classes/qbi_mysql.php on line 160

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ron-ri2/public_html/caddybug-usa/catalog/admin/includes/classes/qbi_mysql.php on line 176

 

I've just bought and installed 3.10 and setup and matched products, shiping, and payments.

 

I clicked on Create iif and clicked on All Customers - Download and get the above error. I get the exact same error clicking on All Previous Orders - Download.

 

Ideas ?

 

RonR

Please post your question in the QBI forum at the QBI website and I would be glad to help you! Paid versions of QBI can not be supported here.

 

Thanks,

Adam

Link to comment
Share on other sites

  • 3 weeks later...

A warning message pops up when I try to import one of the QBI files (.IIF).

 

The pop up reads:

 

QuickBooks has detected that one of your items does not have an account associated with it. Go to the item list to make sure all items are associated with accounts.

 

And then many pop up windows open and you end up having to cancel/stop the attempt to import the data…

 

Error on line 21 35 45 60 72 84 96 106 117129 142 154 168 182 194 204 221 232 243…. Can’t record invalid transaction. Each one of these #’s is a separate popup window and they keep going until you click cancel.

 

Any ideas here? Should I purchase the latest version of QBI? Thank you for any help!

Link to comment
Share on other sites

A warning message pops up when I try to import one of the QBI files (.IIF).

 

The pop up reads:

QuickBooks has detected that one of your items does not have an account associated with it. Go to the item list to make sure all items are associated with accounts.

You have an account missing. Make sure that you have completely filled out the Configuration screen and that all required account names have been entered. They must also match the accounts you are using in QB -- you can't create a new account from within QBI. Also, if you have matched any items, these items when viewed in QB must have all their accounts filled in also.

 

Thanks,

Adam

Link to comment
Share on other sites

  • 2 weeks later...

Sorry, originally posted this question to the wrong forum:

 

I just tried to export my latest 4 orders from QBI 2.10c using the "Export New Orders" button. When I attempted to import them into QB, I got a warning that "That invoice number exists". Upon opening the IIF file in notepad, sure enough, the one invoice that I imported last was the only one in the IIF file. No other orders were shown. I then tried to export "Previous Batch", and then "All Previous Orders", and each time the IIF file only has the single order (the last one I exported) in it.

 

Any thoughts on what might be going on? I have not made any changes to my system recently, although my web host upgraded mySQL and PHP, but I can't recall if that happened before or after my last import. Everything else seems to be working fine. I do not get any type of error messages.

 

Thanks for your help!

 

John

Link to comment
Share on other sites

Hello,

 

I installed QBI Version 2.10.

 

Everything is great until I try to setup products.

 

I get the dreaded "SETUP_FAIL".

 

The iif. file is detected. The name is exactly as described in the user manual.

 

Any idea on what is wrong?

Link to comment
Share on other sites

Hello,

 

I installed QBI Version 2.10.

 

Everything is great until I try to setup products.

 

I get the dreaded "SETUP_FAIL".

 

The iif. file is detected. The name is exactly as described in the user manual.

 

Any idea on what is wrong?

 

 

By the way, the "Discount Fees", "Payments" and "Shipping" setups work. But the "products" setup doesn't... :huh:

Link to comment
Share on other sites

Sorry, originally posted this question to the wrong forum:

 

I just tried to export my latest 4 orders from QBI 2.10c using the "Export New Orders" button. When I attempted to import them into QB, I got a warning that "That invoice number exists". Upon opening the IIF file in notepad, sure enough, the one invoice that I imported last was the only one in the IIF file. No other orders were shown. I then tried to export "Previous Batch", and then "All Previous Orders", and each time the IIF file only has the single order (the last one I exported) in it.

 

Any thoughts on what might be going on? I have not made any changes to my system recently, although my web host upgraded mySQL and PHP, but I can't recall if that happened before or after my last import. Everything else seems to be working fine. I do not get any type of error messages.

 

Thanks for your help!

 

John

Are you sure that the iif file you are opening is the new one and not the old one? When you create the new orders iif with the 4 new orders, they should move down and become the previous orders (you need to refresh the page to see this after downloading) and the new orders should reset to zero. Does this happen?

 

Thanks,

Adam

Link to comment
Share on other sites

Another problem, when I try to download the iif file for new orders, I get this:

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'envoi' AND qbi_shiposc_serv_text='Forfait' AND so.qbi_shiposc_car_code=soqb.qbi_' at line 1

SELECT * FROM qbi_shiposc AS so, qbi_shiposc_shipqb AS soqb, qbi_shipqb AS sqb WHERE qbi_shiposc_car_text='Frais d'envoi' AND qbi_shiposc_serv_text='Forfait' AND so.qbi_shiposc_car_code=soqb.qbi_shiposc_car_code AND so.qbi_shiposc_serv_code=soqb.qbi_shiposc_serv_code AND sqb.qbi_shipqb_refnum=soqb.qbi_shipqb_refnum

[TEP STOP]

Link to comment
Share on other sites

Another problem, when I try to download the iif file for new orders, I get this:

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'envoi' AND qbi_shiposc_serv_text='Forfait' AND so.qbi_shiposc_car_code=soqb.qbi_' at line 1

SELECT * FROM qbi_shiposc AS so, qbi_shiposc_shipqb AS soqb, qbi_shipqb AS sqb WHERE qbi_shiposc_car_text='Frais d'envoi' AND qbi_shiposc_serv_text='Forfait' AND so.qbi_shiposc_car_code=soqb.qbi_shiposc_car_code AND so.qbi_shiposc_serv_code=soqb.qbi_shiposc_serv_code AND sqb.qbi_shipqb_refnum=soqb.qbi_shipqb_refnum

[TEP STOP]

This version of QBI doesn't like the ' in Fraise d'envoi. This is fixed in newer versions of QBI. It is rare for a shipping method to contain ' (at least in English, not French) and this hasn't been reported before.

 

You can fix it as follows:

file: qbi_functions.php

line: 948

change: $carrier=substr($title,0,$end);

to: $carrier=mysql_real_escape_string(substr($title,0,$end));

 

Thanks,

Adam

Link to comment
Share on other sites

lists.iif

It doesn't make sense that it would work for the others and not work for products. Make sure that you checked off "item list" when you made your lists.iif file in QB. Open it in Excel and check that your items are actually in it, and that you have enabled the type of items (or all items) in the QBI config.

 

Thanks,

Adam

Link to comment
Share on other sites

This version of QBI doesn't like the ' in Fraise d'envoi. This is fixed in newer versions of QBI. It is rare for a shipping method to contain ' (at least in English, not French) and this hasn't been reported before.

 

You can fix it as follows:

file: qbi_functions.php

line: 948

change: $carrier=substr($title,0,$end);

to: $carrier=mysql_real_escape_string(substr($title,0,$end));

 

Thanks,

Adam

 

 

Thanks a lot, I will try this.

Link to comment
Share on other sites

It doesn't make sense that it would work for the others and not work for products. Make sure that you checked off "item list" when you made your lists.iif file in QB. Open it in Excel and check that your items are actually in it, and that you have enabled the type of items (or all items) in the QBI config.

 

Thanks,

Adam

 

 

I just re-made the lists.iif file.

 

It does contain my items.

I re-uploaded it. I enabled all types of items.

 

I still get SETUP_FAIL. Very strange.

 

Do you want to see the lists.iff file?

Link to comment
Share on other sites

This version of QBI doesn't like the ' in Fraise d'envoi. This is fixed in newer versions of QBI. It is rare for a shipping method to contain ' (at least in English, not French) and this hasn't been reported before.

 

You can fix it as follows:

file: qbi_functions.php

line: 948

change: $carrier=substr($title,0,$end);

to: $carrier=mysql_real_escape_string(substr($title,0,$end));

 

Thanks,

Adam

 

I works for this. Thank you. :thumbsup:

Link to comment
Share on other sites

Are you sure that the iif file you are opening is the new one and not the old one? When you create the new orders iif with the 4 new orders, they should move down and become the previous orders (you need to refresh the page to see this after downloading) and the new orders should reset to zero. Does this happen?

 

Thanks,

Adam

 

 

Hi Adam,

 

Thanks for your suggestions. I was pretty sure about the files, but just decided to double check for sure. All order files exported contain only the one order (My Invoice 1316 from 12/18/2006). The New orders display is showing Zero orders right now.

 

The "Previous Batch of Orders" is showing the last 4 orders (1317-1320, from 1/11/2007 to 1/21/2007), but those are the orders I never got to export. Exporting the previous batch again only shows the single invoice 1316.

 

"All Previous Orders" is showing all the invoices UP TO and INCLUDING 1316. Exporting that file again results in just the single order invoice 1316 coming through.

 

The orders are showing up in the order screen in Admin, so I am pretty sure they are still in the database :) . Could the "Previous Orders" counter be messed up or something? I appreciate any insight you may have on what to look for next.

 

Thank you again for your help!

 

John

Link to comment
Share on other sites

Hi Adam,

 

Thanks for your suggestions. I was pretty sure about the files, but just decided to double check for sure. All order files exported contain only the one order (My Invoice 1316 from 12/18/2006). The New orders display is showing Zero orders right now.

 

The "Previous Batch of Orders" is showing the last 4 orders (1317-1320, from 1/11/2007 to 1/21/2007), but those are the orders I never got to export. Exporting the previous batch again only shows the single invoice 1316.

 

"All Previous Orders" is showing all the invoices UP TO and INCLUDING 1316. Exporting that file again results in just the single order invoice 1316 coming through.

 

The orders are showing up in the order screen in Admin, so I am pretty sure they are still in the database :) . Could the "Previous Orders" counter be messed up or something? I appreciate any insight you may have on what to look for next.

 

Thank you again for your help!

 

John

Hi John,

First, for new orders, make sure that the QBI Config is set to the correct Status. For example, if it is set to "Pending," and you change new orders to "Shipped" before you try to export them with QBI, they will be excluded from the iif.

 

Second, there isn't any counter. There is a column added to the orders table called qbi_imported that determines what type each order is. 0=new, 1=previous batch, 2=old orders ("All Previous Orders" exports orders with 1 or 2), and 3 or any other number excludes the order completely.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi John,

First, for new orders, make sure that the QBI Config is set to the correct Status. For example, if it is set to "Pending," and you change new orders to "Shipped" before you try to export them with QBI, they will be excluded from the iif.

 

Second, there isn't any counter. There is a column added to the orders table called qbi_imported that determines what type each order is. 0=new, 1=previous batch, 2=old orders ("All Previous Orders" exports orders with 1 or 2), and 3 or any other number excludes the order completely.

 

Thanks,

Adam

 

Thanks Adam,

 

My QBI export status is set to "Any" as it has always been. I went into the ORDERS table, and looked at the qbi_imported value. For the record that keeps exporting, the value is 2. It should really be a 1, correct (previous batch). My new orders that I wanted to export were a 1 (should be a zero). I reset the orders I wanted as new orders to a 0, and the last order that keeps exporting to a 1 in the database. I then exported all new orders. The numbers updated in the table - the previous batch went from 1 to 2, and the new batch went from 0 to 1. The exported file still contained the single record that used to be the previous batch. The Admin area updates to reflect what the table says.

 

I just can't seem to get those records out. I am baffled! :'(

 

Thank you for your patience in trying to help troubleshoot this problem.

 

John

Link to comment
Share on other sites

Thanks Adam,

 

My QBI export status is set to "Any" as it has always been. I went into the ORDERS table, and looked at the qbi_imported value. For the record that keeps exporting, the value is 2. It should really be a 1, correct (previous batch). My new orders that I wanted to export were a 1 (should be a zero). I reset the orders I wanted as new orders to a 0, and the last order that keeps exporting to a 1 in the database. I then exported all new orders. The numbers updated in the table - the previous batch went from 1 to 2, and the new batch went from 0 to 1. The exported file still contained the single record that used to be the previous batch. The Admin area updates to reflect what the table says.

 

I just can't seem to get those records out. I am baffled! :'(

 

Thank you for your patience in trying to help troubleshoot this problem.

 

John

John,

 

I will PM you to take a quick look at your admin.

 

Thanks,

Adam

Link to comment
Share on other sites

I'm looking forward to using this contribution. So far I have used and modified a number of other brilliant contributions but I seem to have an issue launching this one.

 

For example, I installed everything and uploaded the files but I get the following browser error:

 

FIREFOX says ... "The page isn't redirecting properly". The url is: [...]/catalog/admin/qbi_config.php?msg=1

 

IE just times out.

 

I get the same error with and without my lists.iif uploaded. With and without the sql files uploaded. I've both uploaded and installed the sql files on my own seeing how the drop tables command is there and I have removed the alter statements so they wouldn't error out. Did the manual db alterations because of the initial error. I'm probably way over-thinking this and a search on "bi_config.php?msg=1" shows some old issues but I can't tell how this would relate to me.

 

Thanks in advance and I hope that this isn't a big deal. Many seem to get it all loaded and started easily. Thanks again for your time.

__

 

Duende

Link to comment
Share on other sites

I'm looking forward to using this contribution. So far I have used and modified a number of other brilliant contributions but I seem to have an issue launching this one.

 

For example, I installed everything and uploaded the files but I get the following browser error:

 

FIREFOX says ... "The page isn't redirecting properly". The url is: [...]/catalog/admin/qbi_config.php?msg=1

 

IE just times out.

 

I get the same error with and without my lists.iif uploaded. With and without the sql files uploaded. I've both uploaded and installed the sql files on my own seeing how the drop tables command is there and I have removed the alter statements so they wouldn't error out. Did the manual db alterations because of the initial error. I'm probably way over-thinking this and a search on "bi_config.php?msg=1" shows some old issues but I can't tell how this would relate to me.

 

Thanks in advance and I hope that this isn't a big deal. Many seem to get it all loaded and started easily. Thanks again for your time.

What version of PHP are you using? Is it 4.23 or later?

Thanks,

Adam

Edited by adam5532
Link to comment
Share on other sites

Hi Adam,

 

My Qbi has been working perfectly till recently. I'm getting an error message saying

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'oise' AND zone_country_id='73'' at line 1

 

SELECT * FROM zones WHERE zone_name='val d'oise' AND zone_country_id='73'

 

I think it is orders shipped to France that caused the problem. I don't know if our hosting company has made any changes to SQL software.

 

Please help me to fix the error.

 

Thanks so much.

 

Wendy

Link to comment
Share on other sites

Hi Adam,

 

My Qbi has been working perfectly till recently. I'm getting an error message saying

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'oise' AND zone_country_id='73'' at line 1

 

SELECT * FROM zones WHERE zone_name='val d'oise' AND zone_country_id='73'

 

I think it is orders shipped to France that caused the problem. I don't know if our hosting company has made any changes to SQL software.

 

Please help me to fix the error.

 

Thanks so much.

 

Wendy

Wendy,

 

It doesn't like the single quote character in the zone name. This has been fixed in later versions of QBI. For QBI 2.10, you can fix it by doing the following:

 

File: qbi_functions.php

After Line: 308

function get_state_code($state_name,$country_id) {

Add:

$state_name=mysql_real_escape_string($state_name);

 

Thanks,

Adam

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...