QXL Desktop uses the QODBC Desktop driver to query QuickBooks Desktop. Commands available in QODBC Desktop will also work in the QXL desktop application.

QXL Online uses the QODBC Online driver to query QuickBooks Online. Commands available in QODBC Online will also work in the QXL Online application.

Please refer to: https://qodbc.com/links/2343.

QXL Desktop uses the QODBC Desktop driver to query QuickBooks Desktop. Commands available in QODBC Desktop will also work in the QXL desktop application.

QXL Online uses the QODBC Online driver to query QuickBooks Online. Commands available in QODBC Online will also work in the QXL Online application.

Please refer to: https://qodbc.com/links/2342

Problem Description:

I am running QXL and MS Office 365.

I have tried exporting to XLSX, XLS, and even CSV format, but only the file header row writes.

Also, I am getting the following error in QXL logs:

Error Occurred: When attempting to update the value: 80000007-1539326465 - The input string was not in the correct format. Select * from Account, QXL, AdditionalInfo: table selection - Account, Table-0, Tables-151.

Solution:

Please open the 'Region Setting' and verify each 'Region setting' as formatted in the screenshots below.

Please press the 'Windows' key and type 'Region Settings' in the search bar.

Please open 'Region Settings.'

Please click on 'Related Settings.

Please click on 'Region.'

Please click on 'Additional Settings...', and it should open the 'Customize Format' window.

In the 'Number' tab, please ensure you have given input as "."(Dot) in 'Decimal Symbol,' which you can see as displayed in the screenshot below.

Using a comma or any other decimal symbol may result in a "The input string was not in a correct format" error.

Please select a 'Currency Symbol' according to your country's currency symbol in the' Currency' tab.

As per my regional settings, I have selected the UK currency symbol (£) here.

In the 'Time' tab, the 'Short Time' should be in a valid format.

Please specify the 'Short Date' format in the 'Date' tab according to your country's date format.

Please click on " Apply " and save the settings.
Please retest the same issue you are facing.

If you still face the issue, please raise a support ticket. (Click Here to submit a support ticket.)

Please attach the files listed below while submitting the ticket to analyze your issue.

1) Screenshot of QXL Application -- >Settings/Options--> About Tab
2) Screenshot of the problem you are facing. (as an attachment) - Full Screen
Share the entire log files as an attachment in text format from
3) Share>> C:\Users\yourUserName\AppData\Roaming\QXL - QuickBooks Data Export Made Easy">>Config.xml
4) QXL Logs from QXL -> Options -> Messages -> Review QXL Message Or %AppData%\QXL - QuickBooks Data Export Made Easy\QXLMessages.txt
5) QXL Logs from QXL -> Options -> Messages -> Review SDK Messages (as an attachment)
6) Which Windows OS are you using? Please share OS details.
7) Did you upgrade anything?
Please refer to How to take a screenshot: www.qodbc.com/links/screenshot.htm.

Problem Description:

I am trying to query the Transaction table based on the TxnDate field.

I tried the code below.

OdbcCommand SourceCmd = new OdbcCommand("Select * from Transaction where TxnDate >= {d '2010-01-01'} and TxnDate <= {d '" + DateTime.Now.ToString("yyyy-MM-dd") + "'} ", cnQODBC);

I am getting this error:

ERROR [42000] [QODBC] [sql syntax error] Expected lexical element not found: =

And when I try this one:

OdbcCommand SourceCmd = new OdbcCommand("Select * from Transaction where TxnDate >= #2010-01-01# and TxnDate <= #" + DateTime.Now.ToString("yyyy-MM-dd") + "# ", cnQODBC);

I get this error:

ERROR [42000] [QODBC] Unexpected extra token: #

What is the correct format to pass the date values ??

I am using C#.

Solution:

Please refer to the article below for the date format in QODBC:
How are dates formatted in SQL queries when using the QuickBooks-generated timestamps

Problem Description:

I am trying to insert an Invoice, but I am getting the following error:

[QODBC] Not supported (#10003)

I am using below SQL statements:

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000, 3.00, '', 1)

INSERT INTO "Invoice" ("CustomerRefListID," "ARAccountRefListID," "TxnDate," "RefNumber," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "BillAddressCountry," "spending," "TermsRefListID," "DueDate," "ShipDate," "ItemSalesTaxRefListID," "Memo," "IsToBePrinted," "CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403', '40000-933270541', {d'2002-10-01'}, '1', 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '10000-933272658', {d'2002-10-31'}, {d'2002-10-01'}, '2E0000-933272656', 'Memo Test,' 0, '10000-999022286')

I am getting the following error:

Please let me know what I am doing wrong.

Solutions:

You need to either provide a value for SalesTaxCodeRefListID, remove it, or pass null as a value instead of an empty string from the insert statement.

Your insert statement should be as follows:

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000, 3.00, null, 1)

INSERT INTO "Invoice" ("CustomerRefListID," "ARAccountRefListID," "TxnDate," "RefNumber," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "BillAddressCountry," "IsPending," "TermsRefListID," "DueDate," "ShipDate," "ItemSalesTaxRefListID," "Memo," "IsToBePrinted," "CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403', '40000-933270541', {d'2002-10-01'}, '1', 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '10000-933272658', {d'2002-10-31'}, {d'2002-10-01'}, '2E0000-933272656', 'Memo Test,' 0, '10000-999022286')

Problem Description 1:

I am following the steps below and getting the error.

1. First, we inserted the records in the Bill & BillExpenseLine table, and the papers got inserted successfully.

2. Second, we are inserting the records in BillPaymentCheckLine, and we are getting the following error

"Error Parsing Complete XML return string."

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, APAccountRefListID, BankAccountRefListID, RefNumber, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES('80000EB1-1435326666', '80000037-1409939589', '80000024-1409927427', '555555',1, '81D4-1435326671', 88.3)

Please let me know what I am doing wrong.

Solutions 1:

Either remove RefNumber or IsToBePrinted from the insert statement. QuickBooks SDK will allow only one of the fields during insert/update & due to this issue occurring.

Your query should be as follows.

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, APAccountRefListID, BankAccountRefListID, RefNumber, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES('80000EB1-1435326666', '80000037-1409939589', '80000024-1409927427', '555555', '81D4-1435326671', 88.3) 

Problem Description 2:

I am trying to link BillItemLine with PurchaseOrder. I am using the below query to connect transactions & I am getting an XML error.

"Error Parsing Complete XML return string."

INSERT INTO BillItemLine ( ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity ) values ('271B-1071512692', '271D-1071512692',300 )

Please let me know what I am doing wrong.

Solutions 2:

If you are trying to link transactions with Bill, you should include vendor details in your query. Please refer to the sample query & test again:

Your question should be as follows.

INSERT INTO BillItemLine (VendorRefListID, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity) VALUES ('10000-933272655', '271B-1071512692', '271D-1071512692',30)

Problem Description:

QRemote does not consider FQSaveToCache when working with OdbcCommand & Parameters.

I have an application that creates Sales Orders. This is what I execute for each line item:

Running this code against QODBC DSN works well if everything is local - using a 32-bit local DSN and QuickBooks local to the application. The Sales Order appears correct in QuickBooks.

Taking the same code and connecting it to a 64-bit QRemote DSN connected to a 32-bit qODBC DSN on another machine (via QRemote Server) does not work.

The basic flow is like this:

cnQODBC = New OdbcConnection(ConfigurationManager.AppSettings.Item("QuickBooksConnectionString"))

cnQODBC.Open()

[Repeat for every sales order line item]

Dim cmdQODBC As OdbcCommand = New OdbcCommand("insert into SalesOrderLine (CustomerRefListID, TxnDate, SalesOrderLineClassRefListID, TemplateRefListID, RefNumber, " & "SalesOrderLineItemRefListID, SalesOrderLineDesc, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineAmount, " & "CustomFieldSalesOrderLineOther1, FQSaveToCache) values (??, ??, ????????)", cnQODBC)

cmdQODBC.Parameters.AddWithValue(", "strCustomerListID)

cmdQODBC.Parameters.AddWithValue("", "{d'" & dteInvoiceDate.ToString("yyyy-MM-dd") & "'}")

cmdQODBC.Parameters.AddWithValue(", "strLineClassListID)

cmdQODBC.Parameters.AddWithValue(", "strTemplateListID)

cmdQODBC.Parameters.AddWithValue("", intSalesOrderNumber)

cmdQODBC.Parameters.AddWithValue("", strLineItemListID)

cmdQODBC.Parameters.AddWithValue(", "strain description)

cmdQODBC.Parameters.AddWithValue(", "intQuantity)

cmdQODBC.Parameters.AddWithValue(", "dblLineRate)

cmdQODBC.Parameters.AddWithValue(", "dblLineAmount)

cmdQODBC.ExecuteNonQuery()

[End repeat]

cnQODBC.Close()

cnQODBC = Nothing

cmdQODBC.ExecuteNonQuery()

Solutions:

Integer, double, and long datatype parameter passing in QRemote pass-through string format because of the flow QRemote Client --> QRemote Server --> QODBC Datatype conversion creates the problem in QODBC and C, and due to this issue occurred.

The workaround for this problem is to pass a value in string format instead of basic format, as shown in the example below:

cmdQODBC.Parameters.AddWithValue("", intSalesOrderNumber.ToString()) //cmd.Parameters.AddWithValue("", "3");

cmdQODBC.Parameters.AddWithValue("", intQuantity.ToString()) //cmd.Parameters.AddWithValue("", "44.2");

cmdQODBC.Parameters.AddWithValue("", dblLineRate.ToString()) // cmd.Parameters.AddWithValue("", "113.4");

cmdQODBC.Parameters.AddWithValue("", dblLineAmount.ToString()) //cmd.Parameters.AddWithValue("", "0");

Instead of

cmdQODBC.Parameters.AddWithValue("", intSalesOrderNumber) //cmd.Parameters.AddWithValue("", 3);

cmdQODBC.Parameters.AddWithValue("", intQuantity) //cmd.Parameters.AddWithValue("", 44.2);

cmdQODBC.Parameters.AddWithValue("", dblLineRate) //cmd.Parameters.AddWithValue("", 113.4);

cmdQODBC.Parameters.AddWithValue("", dblLineAmount) //cmd.Parameters.AddWithValue("", 0);

How to use parameters in OPENQUERY

Solutions:

OPENQUERY does not accept variables for its arguments. You need to pass Basic Values as shown in the example below:

Select Query:

DECLARE @TSQL varchar(8000), @ID varchar(25)

SELECT @ID = '19650'

SELECT @TSQL = 'SELECT * FROM OPENQUERY(QRemote ,''SELECT * FROM ReceivePayment WHERE ReceivePayment.RefNumber = ''''' + @ID + ''''''')'

EXEC (@TSQL)

Update Query:

DECLARE @TSQL varchar(8000), @ID varchar(25), @CName varchar(25)

SELECT @ID = '80000146-1513345553'

SELECT @CName = 'New Company'

SELECT @TSQL = 'Update OPENQUERY(QRemote ,''SELECT * FROM Customer WHERE Customer.ListID = ''''' + @ID + ''''''')' + 'SET CompanyName = ''' + @CName + ''''

EXEC (@TSQL)

Please refer to the link below:
How to pass a variable to a linked server query
Including parameters in OPENQUERY

Problem Description:

I am trying to run a report that returns ItemInventory where LastReceived is NULL. I have tried in vain to accomplish this. What syntax do you use to select a blank/null date, or is there a special function for this?

Solution:

You can select a blank/null date using "IS NULL" in your query.

For example, the below query will return a row whose InventoryDate is blank or null:

SELECT * FROM ItemInventory where InventoryDate IS NULL

In the same way, you can use "IS NOT NULL" in your query.

For example, the below query will return a row whose InventoryDate is not blank or null:

SELECT * FROM ItemInventory where InventoryDate IS NOT NULL 

Problem Description:

I want to write some select statements on InvoiceLine and SalesReceiptLine that return all records dated WITHIN the past 30 days relative to whatever TODAY is. I'm very familiar with Microsoft SQL syntax and would normally say... WHERE TxnDate >= getdate()-30

How can I reference "30 days ago" using the QODBC driver?

Solutions:

In QODBC, the function CURATE() – Returns the current computer system date as a date value.

For example, for Today, April 18, 2006, when the following query:-

SELECT {fn CURDATE()} as Today, ({fn CURDATE()}-30) as "30 Days Ago", TxnDate, RefNumber, InvoiceLineDesc FROM invoiceline WHERE TxnDate >= ({fn CURDATE()}-30) is run in QODBC Test Tool. The results were:

Problem Description:

I need to select only the transactions that occurred in the last 90 days. I used the Access functions Date() and DateAdd() in the Criteria to filter for those transactions, but I am getting the error message "Invalid Procedure Call." Here are the Criteria that I am trying to use:

Between Date() And DateAdd("dd",-91,Date())

What am I doing wrong? Does QODBC have different functions for this?

Solutions:

To write Pass-thru queries for reading and to write QuickBooks® data using QODBC and Microsoft® Access, you must use the proper date format.

You may use Date Macros, but you may also use specific from and to dates for more flexibility.

This function formats dates with the QODBC format: {d 'YYYY-MM-DD'}. There is no need to remember the form, just the function's name: fncqbDate.



Function:

Function fncqbDate(myDate As Date) As String
myDate = Nz(myDate, Now)
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"
End Function


Example:

You might use fncqbDate to help create an SQL string with VBA from user input dates.

mySQL = "sp_report customtxnDetail show TxnType,TxnID, RefNumber, Date, Name ,Memo , Amount,account parameters TxnFilterTypes = 'Check',SummarizeRowsBy = 'TotalOnly',dateFROM = " & fncqbDate(BegDate) & ", dateTO = " & fncqbDate(EndDate) & " where account like '%checking%'"



Put Some Checks into a Table:

Try this out and put some checks on a table:
1. Copy and paste fncqbDate (first function above) into a module.
2. Copy and paste fncGetChecks (function below) into a module.
3. If you need QuickBooks® to open to using QODBC, open it and ensure you have authorized QuickBooks® to communicate with QODBC. 4. Make sure the following references are checked in your Microsoft® Access database:
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library


To use fncGetChecks, call it from a form or type fncGetChecks into the immediate window of the Visual Basic Editor.

Change the default connection string if necessary by entering your connection string when prompted.

The function will ask for: a name for the new query (make sure this doesn't already exist in your database)

a beginning date

an ending date

Your connection string, which may or may not be the default offered



Function fncGetChecks()
On Error GoTo fncGetChecks_err
Dim q As String, Date1 As Date, Date2 As Date
q = InputBox("Give your temporary query a name:", "Temporary Pass-Thru Query", "")
Date1 = InputBox("Enter start date:", "Start Date", FormatDateTime(Now, vbShortDate))
Date2 = InputBox("Enter end date:", "End Date", FormatDateTime(Now, vbShortDate))
Dim db As DAO.Database, qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.CreateQueryDef(q)
qd.ReturnsRecords = True
qd.Connect = InputBox("Enter connection string:", "", "ODBC;DSN=QuickBooks Data;SERVER=QODBC")
qd.SQL = "sp_report customtxnDetail show TxnType,TxnID, RefNumber, Date, Name ,Memo , Amount,account " & _
"parameters TxnFilterTypes = 'Check',SummarizeRowsBy = 'TotalOnly'," & _
"dateFROM = " & fncqbDate(Date1) & ", dateTO = " & fncqbDate(Date2) & _
" where account like '%checking%'"
DoCmd.RunSQL "select * into tbl" & q & " from " & q
Set qd = Nothing
Set db = Nothing
DoCmd.DeleteObject acQuery, q
DoCmd.OpenTable "tbl" & q
Exit Function
fncGetChecks_err:

MsgBox Erl & " " & Err.Number & ": " & Err.Description
End Function


Also, refer to the following:

How to Use Prompted Date Ranges in MS Access 2007 using Vista 

Problem Description:

How do I create a note (or a blank line) on the invoice? If I do it the same way that I do it using QODBC for QuickBooks, I get an error :

Error sending txn to QB: 2020: QuickBooks message: Required param missing. We need to supply the required value for the API. QuickBooks message: Required parameter Line.SalesItemLineDetail is missing in the request.

Solutions:

You can create a blank invoice from QODBC Online by using below sample query:

INSERT INTO "InvoiceLine" (CustomerRefFullName,"InvoiceLineItemRefFullName", RefNumber) VALUES ('Aadi Vanhorn','Test','Blank001' )

You must create an item without providing a price/rate in the Item module. Then it would be best if you delivered ItemName in your query. In this example, I have created the Item "Test" & use it in the question. You can use the RefNumber column to insert InvoiceNumber. If you don't want to provide InvoiceNumber, then QuickBooks Online automatically adds the Invoice number to the invoice. Please see the query below without including RefNumber:

INSERT INTO "InvoiceLine" (CustomerRefFullName,"InvoiceLineItemRefFullName") VALUES ('Aadi Vanhorn','Test')

Tags: QuickBooks Online, QBO, Create Blank Invoice, QODBC Online

Problem Description:

I am getting the following error message when trying to SELECT data fields AS Alias, the statement runs fine otherwise.

[sql syntax error] Expected lexical element not found:=

Please see the following SQL statement:

SELECT Item. Name AS SKU, Item.CustomFieldColor AS Item, Item.Description, Item.SalesPrice, Item.PurchaseCost, Item.QuantityOnHand FROM Item Item WHERE (Item.Name<>'IFR' And Item.Name<>'OTW') AND (Item.Description<>'') AND (Item.Type='ItemInventory') ORDER BY Item.Name

The above statement is working fine in QODBC Test Too and MS Access. But I am facing an issue in MS Excel.  

Solutions:

MS Excel has an issue when you alias in the query. When you try to use Microsoft Query to return data from some third-party databases into Microsoft Excel, apostrophes (') around alias names can cause the query to fail.

Please refer to the link below to resolve this issue:

Using a field alias in Query does not work with some third-party databases

You can either apply a hotfix or change registry values.

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.

Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

The result after changing registry values & execute the query again:

Problem Description:

I am trying to run a query using an SQL Server database link to QuickBooks using QRemote. I can set up the linked server fine in SQL Server, and the connection has been tested to work. However, I try to run the query:

SELECT * FROM QRemote...InvoiceLine

The response is:

The OLE DB provider "MSDASQL" for linked server "QRemote" contains multiple tables that match the name "InvoiceLine."

I tried selecting through the QODBC Test Tool, which does not use the linked database. Please help as to where the issue might be.

Solutions:

You have not configured the MSDASQL property for the linked server.

The OLE DB provider options for managing linked queries can be set in SQL Server Management Studio. 

In Object Explorer, right-click the provider name and select Properties for MSDASQL. The first six properties should be enabled. Please enable the first six properties. 

For Multiple tables existing error, the "Level zero only" property should be set.   

Problem Description:

How do I access the alternate email addresses 1 and 2 and the cc email fields? I don't see them at the customer table.

Solutions:

You can get CC Email details from the "Cc" field in the Customer & Entity table.

The Alt Email 1 and 2 fields are not available through the Intuit SDK, so they are not available through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in the SDK, QODBC cannot do it either.

Problem Description:

When I run a report query, the query works very slowly & takes about 1-5 mins per simple report like:

"sp_report ARAgingSummary show Current parameters DateFrom={d'2014-11-12'}, DateTo={d'2014-11-12'} where Blank = 'Total'"

Sometimes it freezes QuickBooks, so the user should restart it.

Solutions:

Please enable the QODBC status panel via Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source.

Go to the "System DSN" Tab, select "QuickBooks Data" DSN & click "Configure."

Navigate to the "Message" tab ->Select "Display Driver Status" and "Display Optimizer Status" options.

Then the next time you run a query, if you see "Waiting for QuickBooks," it means QuickBooks is taking time to process the request. There will be a status panel at the lower right corner of your screen, showing a window with information on what QODBC is working on. Please note the step on which QODBC spends the most time or gets stuck and share it with us.

If you are getting "Waiting for QuickBooks," it means QuickBooks is taking time to process requests. So we may not provide much help on this issue.

QuickBooks SDK provides data in XML format & QODBC displays it in tabular form. QODBC acts as a 'wrapper' around the Intuit SDK so customers can finally get at their QuickBooks data using standard database tools, speeding development time. QODBC accepts SQL commands from applications through the ODBC interface, then converts those calls to navigational XML commands to the QuickBooks Accounting DBMS and returns record sets that qualify for the query results.

QODBC requests the Report information from the QuickBooks SDK, and QuickBooks is the one processing it and sending the output. QODBC formats that output to a data table format.   

Problem Description:

I have QuickBooks 2014 version & I am using QODBC's latest version. I have a problem with querying the customer table using QODBC. 

QODBC driver consistently gets stuck on "Find Next Record" at record #9000. I have tried the following: 

- Reset the optimizer file

- Rebuild the QuickBooks company file

- sp_optimizefullsync Customer: this crashes QuickBooks or does not complete correctly. There are still records missing from the Customer table that appear in QuickBooks.

- Query customer table using UNOPTIMIZED keyword. Unoptimized hangs at about record 9000 and does not return the Customer

On QuickBooks SDK logs, I noticed the below error:

20140923.175608 E 3888 QBSDKProcessRequest *** INTERNAL ERROR WHEN PROCESSING THE QBXML REQUEST ***.

Solutions:

There might be some internal error that occurred during the processing request. To resolve this error, you need to get in touch with Intuit.

Please restart QuickBooks & try again, as you are saying that the query is stuck on #9000. There might be some issue with the company file. Please test the same on another company file or a sample company file to see if the problem is related to the company file.

Also, try changing the Iterator value to 100 on QODBC Setup Screen--Advanced Tab.

If you are still facing the same error, there might be an issue with your company file that would require repairing the company file & you need to get in touch with Intuit.

Problem Description:

I am getting the error "Error 3250 - This feature is not enabled or not available in this version of QuickBooks." while I am trying to insert an Invoice in QuickBooks using QODBC. Below is my insert statement:

INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineGroupItemGroupRefListID, InvoiceLineGroupQuantity, InvoiceLineSalesTaxCodeRefListID, InvoiceLineLotNumber, FQSaveToCache) VALUES ('670000-1071517519', '91047', '320000-1071525597', 'POWER TRAK-2000', 200.00000, 200.00,'300000-933272656',11, '20000-999022286', 'L123',0)

Solutions:

QuickBooks does not allow to use LotNumber when Advanced Inventory is Turned Off. QuickBooks SDK will throw the error "This feature is not enabled or not available in this version of QuickBooks."

The Error message will depend on the operation you are performing.

In this case, you need to enable the Advanced Inventory module & try again.

There might be other possibilities for this error. Please refer below possibilities:

If the Units of Measure feature is not enabled & you are trying to use it in your query.

If the Class feature is not enabled & you are trying to use it in your query. The QuickBooks company file you are using is not set to allow assigning classes to names. This preference must be turned on to include the ClassRef section of your query.

To resolve this error, You need to enable the feature you are using from QuickBooks & try again.   

Problem Description 1:

We have a customer trying to import a credit memo from our database into QuickBooks using a vb.net program. They are getting the following error: ERROR [42S00] [QODBC] Insert value must be a simple value. We have other customers that can import credit memos, and I have compared the imported data and cannot find any difference in the data format. Can you give me an idea of what to look for?

Solutions 1:

You are getting the error " [QODBC] Insert value must be a simple value" because you are not passing the value in the correct format. The VB code generates this error. It is not a QODBC error.

Please verify your insert statement & try again. Please refer below-mentioned link to a sample VB code:

Examples of How to Use QODBC via Visual Basic

Please refer to the below-mentioned article for creating a credit memo:

How to create Credit Memos

Problem Description 2:

When I go to insert a Customer, I receive an error saying [QODBC] Insert value must be a simple value. I need to know what I am doing wrong to insert a new customer.

Please refer below code which I am using:

Public Sub InsertNewCustomers()

Dim name As String

Dim firstName As String

Dim lastName As String

Dim companyName As String

Dim contact As String

Dim billAddressAddr1 As String

Dim billAddressAddr2 As String

Dim billAddressAddr3 As String

Dim billAddressCity As String

Dim billAddressState As String

Dim address postal code As String

Dim phone As String

Dim fax As String

Dim email As String

'Testing to Insert, remove before going LIVE!!!*************************************************************************************************

name = "ABC XYZ"

firstName = "ABC"

lastName = "XYZ"

companyName = "Test Company"

contact = "Jerry"

billAddressAddr1 = "503 Test Club"

billAddressAddr2 = ""

billAddressAddr3 = ""

billAddressCity = "Test City"

billAddressState = "OK"

addressPostalCode = "11644"

phone = "111-111-1111"

fax = ""

email = ""

'*********************************************************************************************************************

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

conn = New ADODB.Connection

conn.ConnectionString = "DSN=Quickbooks Data;OLE DB Services=-2;"

conn.Open()

Try

' Create the new record.

rs = conn.Execute( _

"INSERT INTO customer(name, firstname, lastname, companyName, contact, BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, Fax, Email)VALUES(name, firstname, lastname, companyName, contact, billAddressAddr1, billAddressAddr2, billAddressAddr3, billAddressCity, billAddressState, billAddressPostalCode, phone, fax, email)")

LogEntry("New Customer Added to QuickBooks")

Catch e As an Exception

MsgBox(e.ToString)

End Try

' Close the database.

rs.Close()

rs = Nothing

conn.Close()

conn = Nothing

'*********************************************************************************************************************

End Sub

Solutions 2:

You are getting the error " [QODBC] Insert value must be a simple value" because you are not passing the value in the correct format. The VBA code generates this error. It is not a QODBC error.

There is a problem with your Insert statement. You cannot pass the value directly. Your insert statement should be like as below.

INSERT INTO customer(Name,FirstName,LastName,CompanyName,Contact,BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressState,BillAddressPostalCode,Phone,Fax,Email) " & _

" VALUES( '" + name + "','" + firstName + "','" + lastName + "','" + companyName + "','" + contact + "','" + billAddressAddr1 + "','" + billAddressAddr2 + "','" + billAddressAddr3 + "','" + billAddressCity + "','" + billAddressState + "','" + billAddressPostalCode + "','" + phone + "','" + fax + "','" + email + "')

Please refer to the updated code & try to insert the record with this code.

Public Sub InsertNewCustomers()

Dim name As String

Dim firstName As String

Dim lastName As String

Dim companyName As String

Dim contact As String

Dim billAddressAddr1 As String

Dim billAddressAddr2 As String

Dim billAddressAddr3 As String

Dim billAddressCity As String

Dim billAddressState As String

Dim addressPostalCode As String

Dim phone As String

Dim fax As String

Dim email As String

'Testing to Insert, remove before going LIVE!!!*************************************************************************************************

name = "ABC XYZ"

firstName = "ABC"

lastName = "XYZ"

companyName = "Test Company"

contact = "Jerry"

billAddressAddr1 = "503 Test Club"

billAddressAddr2 = ""

billAddressAddr3 = ""

billAddressCity = "Test City"

billAddressState = "OK"

addressPostalCode = "11644"

phone = "111-111-1111"

fax = ""

email = ""

'*********************************************************************************************************************

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

conn = New ADODB.Connection

conn.ConnectionString = "DSN=Quickbooks Data;OLE DB Services=-2;"

conn.Open()

Try

' Create the new record.

rs = conn.Execute( _

sSQL = "INSERT INTO customer(Name,FirstName,LastName,CompanyName,Contact,BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressState,BillAddressPostalCode,Phone,Fax,Email) " & _

" VALUES( '" + name + "','" + firstName + "','" + lastName + "','" + companyName + "','" + contact + "','" + billAddressAddr1 + "','" + billAddressAddr2 + "','" + billAddressAddr3 + "','" + billAddressCity + "','" + billAddressState + "','" + billAddressPostalCode + "','" + phone + "','" + fax + "','" + email + "')"

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

conn.Open sConnectString

' Create a new record.

rs = conn.Execute(sSQL)

sMsg = sMsg & "Record Added!!!"

MsgBox msg

' Close the database.

Set rs = Nothing

Set conn = Nothing

'*********************************************************************************************************************

End Sub

Problem Description:

I'm trying to create a new table with VB Demo or QODBC Test Tool.

Can you send me a sample query to do this?

Solutions:

QODBC is an ODBC driver. Please remember that QODBC is not a database but rather a translation tool. QODBC acts as a 'wrapper' around the Intuit SDK so customers can finally get at their QuickBooks data using standard database tools;

You cannot create a new table from VB Demo or QODBC Test Tool because QODBC is just reading and writing to the QuickBooks Company file via the QuickBooks SDK. QuickBooks SDK provides data in XML format, and QODBC converts it to a table format.

What you are trying to do is a new table in the QuickBooks Company file, which is not supported. Thus, you cannot create/drop/alter table(s) from the VB Demo or QODBC Test Tool.

Keywords: adding in a column, adding in a table, how to add a new table, how to add a new column

I'm working in PHP and using the QODBC Test Tool. I am trying to insert a value into the Customer table.

But I am getting the following error:

Expected lexical element not found:

My insert statement is:

Insert into Customer ('CompanyName,' 'Phone,' 'Email') values('adsf,' '235632', 'afdsf');

Won't work for me. Any suggestions as to why?

Solution 1:

QODBC will issue this error when there is a syntax error in your SQL statements.

Your insert statement should be like this:

Insert into Customer ("CompanyName," "Phone," "Email") values('Test,' '235632', 'abc@def.com');

Problem Description 2:

I have constructed an insert statement that is getting this error. To narrow it down, I modified the values portion of my query to be a select, inserted, and tested the different data types I was trying to select.

After doing so, I was able to determine that the problem is with both the ts and d functions that we have here, and I had to assume that it was the input's fault and not yours, since the process is referenced everywhere. I seemed to remember reading somewhere that you guys use the computer's regional time settings to figure out how to parse out times, but after my test, I found this not to be the case.

For example
select {ts'8/20/2006 9:26:15 AM'} from customer
and
select {d'9/15/2006'} from customer
both give the lexical element error.

My "Regional and Language settings" in "Control Panel" say my date-time format looks like so:

Time: 10:04:29 AM
Short Date: 8/10/2006
Long Date: Thursday, August 10, 2006

I was hoping that you guys were paying attention to it. After all, that's what .NET does, and it seems to work great.

select {d'2006-06-26'} from customer

This results in "6/26/2006 12:00:00 AM," which is expected, but having to format the time for the select/insert/update/delete statement so that it can be formatted back seems like a lot of overhead...

For those of us using .NET (or any language that supports date/time formatting via a format string), you should be able to use the following:

String date= System.DateTime.Now.ToString("yyyy-MM-dd");
string datetime= System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt");

Which will give you the following:
2006-08-10
and
2006-08-10 10:10:39 AM

Respectively, which can be used in your SQL Statement afterward.

Solutions 2:

QODBC will issue this error when there is a syntax error in your SQL statements.

So please check your statements & try again.

QODBC uses the standard SQL Date {d'YYYY-MM-DD'} and Timestamp {ts 'YYYY-MM-DD HH:mm: SS.zzz'} formats and is the same worldwide regardless of the region: See also: How are dates formatted in SQL queries when using the QuickBooks-generated time stamps

Problem Description 

When trying to execute a query statement, I get the error message " Expected lexical element not found."

Solutions

It seems to be the issue in the SQL Statement. Please check all the field names and table names. Usually, this could be a typo error in the field name in your SQL Statement. To know more about the fields and data layout, please Click Here 

Another Possible Problem & Solution:

Problem Description:

When I issue a SQL statement:

SELECT Desc FROM Charge 

I get "Expected lexical element not found: = <identifier>."

However, if I issue

Select * from Charge

Then, I get a full output with one of the columns named "Desc."

Why can't I query for the column by name?

I have tried this through the QODBC Support Wizard and a C# program. 

Problem Description:

I am trying to clear a date column for the JobStartDate, and I am getting an error. Any help would be great., I could not find a help article on this.

I am using .NET if that matters.


Dim jobStartDate As Date? = Nothing

Dim cmd As New OdbcCommand($"
UPDATE customer
SET jobStartDate = {jobStartDate}
WHERE ListID = '{listID}' ",
con)

cmd.ExecuteNonQuery()


ERROR [42000] [QODBC] [sql syntax error] Expected lexical element not found: = CASE


Dim jobStartDate As Date = Nothing

Dim cmd As New OdbcCommand($"
UPDATE customer
SET jobStartDate = {jobStartDate}
WHERE ListID = '{listID}' ",
con)

cmd.ExecuteNonQuery()

Message = "ERROR [42000] [QODBC] Unexpected extra token: :00:00"

SELECT Desc FROM Charge 

I get "Expected lexical element not found: = <identifier>."

However, if I issue

Select * from Charge

Then, I get a full output with one of the columns named "Desc."

Why can't I query for the column by name?

I have tried this through the QODBC Support Wizard and a C# program.