How FQSaveToCache Works

The process involves a sequence of INSERT statements for the line items of a transaction, with the FQSaveToCache flag controlling when the data is sent to QuickBooks.²

1. Caching the Lines (FQSaveToCache = 1 or TRUE)

• For the first line item INSERT statement, and all subsequent intermediate line items, you must set the FQSaveToCache value to 1 OR TRUE

  • Action: When QODBC sees this value, it takes the data from the INSERT statement and stores it locally in a cache specific to your current QODBC connection.

  • Result: The data is not yet written to the QuickBooks company file.

• Example (Conceptual):

  SQL

   

  INSERT INTO "InvoiceLine" (...) VALUES (..., 1) -- First line, cache it
  INSERT INTO "InvoiceLine" (...) VALUES (..., 1) -- Second line, cache it

2. Finalizing and Writing the Transaction ( FQSaveToCache = 0 or FALSE)

• For the last line item INSERT statement for that transaction, you must set the FQSaveToCache value to 0 OR FALSE. If you do not include FQSaveToCache field and value in the insert statement, it will be considered as FQSaveToCache =0

  • Action: When QODBC sees this value, it performs two main steps:

    1. It combines the data from this final INSERT statement with all the previous cached line items for the same connection.

    2. It sends this complete, combined transaction (header and all lines) as a single batch to QuickBooks via the QuickBooks SDK.

  • Result: The complete transaction is now permanently saved in your QuickBooks company file.

• Example (Conceptual):

  SQL

   

  INSERT INTO "InvoiceLine" (...) VALUES (..., 0) -- Final line, write all cached lines + this line to QuickBooks
  

I am getting the following error trying to insert a record in Invoice:

[QODBC] Not supported - At least one Line record is required. Please insert Child/Detail record(s) before inserting Parent/Header record. (#10074)

Solution:

How to Fix the Error
To resolve this issue, you must change the order of your data insertion or SQL statements to ensure the required detail exists before the header.

Insert the Detail Records First: Before you attempt to insert data into the Parent/Header table (e.g., Invoice), you must successfully insert at least one corresponding record into the Child/Detail/Line table (e.g., InvoiceLine).

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ( '320000-1071525597', 'POWERTRAK-2000', 200.00000, 200.00, '20000-999022286',1)

INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache) VALUES ('POWERTRAK-3000', 1)

INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache) VALUES ('POWERTRAK-4000', 0)

Insert the header record: 

INSERT INTO Invoice (CustomerRefListID, RefNumber, FQSaveToCache) VALUES ('670000-1071517519', '71099',0)

Insert the Header Record Last: Once the detail record(s) are in place, you can then insert the Parent/Header record, completing the transaction.

MS Access:

If you are using MS Access application, the connection string or the connection object is getting closed after you insert the Invoice Line and before you can insert the Invoice (Header) record.
This could be because your linked tables have different connection strings. You can delete the linked table and re-add the linked tables.

If you are using ADO/DOA objects, please ensure you do not close the connection between the insert of InvoiceLine and the Invoice table.

Stored Procedures Command List

Applicable to [QODBC-Desktop], [QODBC-Online], [QODBC-POS], [QXL- Desktop] & [QXL- Online] 

Stored Procedures

SP_COLUMNS table name

Instruction: Returns a recordset of the columns in the specified table.

Example: sp_columns Customer

Returns:

SP_FQSAVETOCACHEROLLBACK on|off

Instruction: Sets or clears whether the cache is backed up or reset if an error occurs when doing multiple inserts using the FQSaveToCache flag field. The off mode can help with performance if the rollback is not needed. The default is on.

Example: sp_fqsavetocacherollback off

Returns: This is an execute the command and does not return a recordset.

SP_LASTINSERTID

Instruction: Returns a recordset with one row (or multiple rows if done after a sp_batchupdate command) and two columns containing the last ListID or last TxnID from the last insert done and its error status on the current connection. The value is obtained from the return value of the previous insert (or inserts if done inside a sp_batchstart/sp_batchupdate block) performed on the same connection for that table.

Example: sp_lastinsertid Customer

Returns:

SP_LASTINSERTIDRETURN  on|off

Instruction: Sets or clears whether the last insert ID is returned.

Example: sp_lastinsertidreturn off

Returns: This is an execute the command and does not return a recordset.

SP_OPTIMIZEFULLSYNC  tablename|ALL

Instruction: This command will reload the specified table from scratch. It helps ensure that the optimizer perfectly syncs with the QuickBooks company file. The ALL option will do all tables.

Example: sp_optimizefullsync Customer

Returns: This is an execute the command and does not return a recordset.

SP_OPTIMIZEUPDATESYNC tablename|ALL

Instruction: This command will synchronize the specified table with the QuickBooks data file using changed and deleted data. It helps ensure that the optimizer is up to date with the QuickBooks company file. The ALL option will do all tables.

Example: sp_optimizeupdatesync Customer

Returns: This is an execute the command and does not return a recordset.

SP_PRIMARYKEYStablename

Instruction: Returns a recordset of the primary key segments in the specified table.

Example: sp_primarykeys Customer

Returns:

SP_QBFILENAME

Instruction: This command returns the full path to the open QuickBooks company file. It returns a one-column, one-row record set.

Example: sp_qbfilename

Returns:

*Note: sp_report and other report commands are unavailable for QODBC POS. QuickBooks POS SDK does not support reports, and thus it is not available in QODBC POS.

SP_REPORTtablename [show]

Instruction: Similar to the SQL Keyword SELECT. Used to run built-in QuickBooks reports. See http://www.qodbc.com/data, make your country selection, then select the REPORTS link.

Example: sp_report ARAgingSummary show Current_Title, Amount_Title, Text, Label, Current, Amount parameters DateMacro = ‘Today', AgingAsOf = ‘Today'

Returns:

SP_REPORTS

Instruction: Returns a recordset of the information of all reports.

Example: sp_reports  

Returns:

SP_SPECIALCOLUMNS  tablename [ ROWVER ]|[ BEST_ROWID ]

Instruction: Returns a recordset of the special columns. RowVer describes which column holds the row ID, and Best_RowID describes the column that is the row identifier. Best_RowID is the default if neither is specified.

Example: sp_specialcolumns Customer Best_RowID

Returns:

Example2: sp_specialcolumns Customer RowVer  

Returns:  

SP_TABLES  table name

Instruction: Returns a recordset with the list of tables available from the ODBC Driver.

Example: sp_tables

Returns:

SP_CATEGORIES

Instruction: Returns a recordset with the list of categories for all the tables.

Example: sp_categories

Returns:

SP_CATEGORYTABLES

Instruction: Returns a recordset with the list of all the tables and the categories they belong to.

Example: sp_categorytables

Returns:

SP_CATEGORYREPORTS

Instruction: Returns a recordset with the list of all the reports and the categories they belong to.

Example: sp_categoryreports

Returns:

SP_FOREIGNKEYS  table name table name

Instruction: Returns a recordset of the detailed relationship information of two tables.

Example: sp_foreignkeys Customer Invoice

Returns:

SP_PARAMETERS

Instruction: Returns a recordset with the list of the detailed information of parameters of each report.

Example: sp_parameters

Returns:

SP_STATISTICS  tablename

Instruction: Returns a recordset with the list of all the indexes and a list of statistical information of the specified table or indexed view.

Example: sp_statistics Customer

Returns:

Keywords: last insert id

Function List

Applicable to [QODBC-Desktop], [QODBC-Online], [QODBC-POS], [QXL- Desktop] & [QXL- Online] 

Functions

     This is a list of all of the SQL functions supported by the QODBC Driver and their associated syntax.

QODBC String Functions

ASCII ( string_exp )

Instruction: Returns the ASCII code value of the leftmost character of string_exp as an integer.

Example: SELECT {fn ASCII("Name")} AS "ASCII", "Name" FROM Customer

Returns:

CHAR ( code )

Instruction: Returns the character with the ASCII code value specified by the code. The code value should be between 0 and 255; otherwise, the return value is data source-dependent.

Example: SELECT {fn CHAR(65)} +  {fn CHAR(66)} AS "APlusB", "Name" FROM Customer

Returns:

CONCAT ( string_exp1, string_exp2 )

Instruction: Returns a character string that results from concatenating string_exp2 to string_exp1. A NULL value will be returned if the column represented by string_exp1 or string_exp2 contains a NULL value.

Example: SELECT {fn CONCAT("BillAddressState", "BillAddressPostalCode")} AS "STZip", "Name" FROM Customer

Returns:

DIFFERENCE ( string_exp1, string_exp2 )

Instruction: Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.

Example: SELECT {fn DIFFERENCE("Name", 'Abercrombie, Kristy')} AS "Difference", "Name" FROM Customer

Returns:

INSERT ( string_exp1, start, length, string_exp2 )

Instruction: Returns a character string where length characters have been deleted from the string_exp1 beginning at the start and where string_exp2 has been inserted into string_exp1, beginning at the start.

Example: SELECT {fn INSERT("Name", 3, 2, '*Inserted*')} AS "Inserted", "Name" FROM Customer

Returns:

LCASE ( string_exp )

Instruction: Converts all uppercase characters in string_exp to lowercase.

Example: SELECT {fn LCASE("Name")} AS "LCase", "Name" FROM Customer

Returns:

UCASE ( string_exp )

Instruction: Converts all lowercase characters in string_exp to uppercase.

Example: SELECT {fn UCASE("Name")} AS "UCase", "Name" FROM Customer

Returns:

LEFT ( string_exp, count )

Instruction: Returns the leftmost count of characters of string_exp.

Example: SELECT {fn LEFT("Name", 5)} AS "Left5", "Name" FROM Customer

Returns:

RIGHT ( string_exp, count )

Instruction: Returns the rightmost count of characters of string_exp.

Example: SELECT {fn RIGHT(“Name”, 5)} AS "Right5", "Name" FROM Customer

Returns:

LENGTH ( string_exp )

Instruction: Returns the number of characters in string_exp, excluding trailing blanks and the string termination character.

Example: SELECT {fn LENGTH("Name")} AS "Length", "Name" FROM Customer

Returns:

BIT_LENGTH ( string_exp )

Instruction: Returns the bit length of string_exp, excluding trailing blanks and the string termination character.

Example: SELECT {fn BIT_LENGTH("Name")} AS "BitLength", "Name" FROM Customer

Returns:

CHAR_LENGTH ( string_exp )

Instruction: Returns the number of chars in string_exp, excluding trailing blanks and the string termination character.

Example: SELECT {fn CHAR_LENGTH("Name")} AS "CharLength", "Name" FROM Customer

Returns:

CHARACTER_LENGTH ( string_exp )

Instruction: Returns the number of characters in string_exp, excluding trailing blanks and the string termination character. (Almost the same as the LENGTH function)

Example: SELECT {fn CHARACTER_LENGTH("Name")} AS "CharacterLength", "Name" FROM Customer

Returns:

OCTET_LENGTH ( string_exp )

Instruction: Returns the octet length of string_exp, excluding trailing blanks and the string termination character. (Almost the same as the LENGTH function)

Example: SELECT {fn OCTET_LENGTH("Name")} AS "OctetLength", "Name" FROM Customer

Returns:

LOCATE ( string_exp1, string_exp2[, start] )

Instruction: Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first position in string_exp2 unless the optional argument is specified. If the start is set, the search begins with the character position indicated by the value birth. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned.

Example: SELECT {fn LOCATE('a', "Name", 2)} AS "LocationOfA", "Name" FROM Customer

Returns:

LTRIM ( string_exp )

Instruction: Returns the characters of string_exp, with leading blanks removed.

Example: SELECT {fn LTRIM("Name")} AS "LTrim", "Name" FROM Customer

Returns:

RTRIM ( string_exp )

Instruction: Returns the characters of string_exp, with trailing blanks removed.

Example: SELECT {fn RTRIM("Name")} AS "RTrim", "Name" FROM Customer

Returns:

REPEAT ( string_exp1, repeat times )

Instruction: Returns the characters of string_exp1, repeating it repeat times.

Example: SELECT {fn REPEAT("Name",2)} as "Repeat2","Name" FROM Customer

Returns:

POSITION ( string_exp1, string_exp2 )

Instruction: Returns an integer value that shows the position where string_exp1 first begins in string_exp2 (including spaces)

Example: SELECT {fn POSITION('a' IN "Name")} As "PositionOfA", "Name" FROM Customer

Returns:

REPLACE ( string_exp1, string_exp2,string_exp3 )

Instruction: Returns the characters of string_exp3, which takes the place of string_exp2 value in column string_exp1.

Example: SELECT {fn REPLACE("NAME",'330 Main St','abc')} AS "Replace","Name" FROM Customer

Returns:

SOUNDEX ( string_exp )

Instruction: Returns a character string representing the sound of the words in string_exp.

Example: SELECT {fn SOUNDEX("Name")} AS "Soundex", "Name" FROM Customer

Returns:

SPACE ( count )

Instruction: Returns a character string consisting of count spaces.

Example: SELECT '[' + {fn SPACE(10)} + ']' AS "TenSpaces", "Name" FROM Customer

Returns:

SUBSTRING ( string_exp, start, length )

Instruction: Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters.

Example: SELECT {fn SUBSTRING("Name", 2, 5)} AS "Middle5Characters", "Name" FROM Customer

Returns:

QODBC Numeric Functions

ABS ( numeric_exp|float_exp|integer_exp )

Instruction: Returns the absolute value of numeric_exp.

Example: SELECT "Name", {fn ABS(Balance)} AS "ABSBalance", "Balance" FROM Customer

Returns:

ACOS ( float_exp )

Instruction: Returns the arccosine of float_exp as an angle, expressed in radians.

Example: SELECT {fn ACOS({fn CONVERT(0, SQL_FLOAT)})} AS "ACOSValue", CompanyName AS "CompanyName" FROM Company

Returns:

ASIN ( float_exp )

Instruction: Returns the arcsine of float_exp as an angle, expressed in radians.

Example: SELECT {fn ASIN({fn CONVERT(0, SQL_FLOAT)})} AS "ASINValue", CompanyName AS "CompanyName" FROM Company

Returns:

ATAN ( float_exp )

Instruction: Returns the arctangent of float_exp as an angle, expressed in radians.

Example: SELECT {fn ATAN({fn CONVERT(1, SQL_FLOAT)})} AS "ATANValue", CompanyName AS “CompanyName” FROM Company

Returns:

ATAN2 ( float_exp1, float_exp2 )

Instruction: Returns the arctangent of the x and y coordinates specified by float_exp1 and float_exp2, respectively, as an angle, expressed in radians.

Example: SELECT {fn ATAN2({fn CONVERT(1, SQL_FLOAT)}, {fn CONVERT(2, SQL_FLOAT)})} AS "ATAN2Value", CompanyName AS “CompanyName” FROM Company

Returns:

COS ( float_exp )

Instruction: Returns the cosine of float_exp, where float_exp is an angle expressed in radians.

Example: SELECT {fn COS({fn CONVERT(1, SQL_FLOAT)})} AS "COSValue", CompanyName AS “CompanyName” FROM Company

Returns:

COT ( float_exp )

Instruction: Returns the cotangent of float_exp, where float_exp is an angle expressed in radians.

Example: SELECT {fn COT({fn CONVERT(1, SQL_FLOAT)})} AS "COTValue", CompanyName AS “CompanyName” FROM Company

Returns:

CEILING ( numeric_exp|float_exp|integer_exp )

Instruction: Returns the smallest integer greater than or equal to numeric_exp.

Example: SELECT "Name", {fn CEILING("Balance")} AS "CeilingBalance", "Balance" FROM Customer

Returns:

DEGREES ( numeric_exp|float_exp|integer_exp )

Instruction: Returns the number of degrees converted from numeric_exp radians.

Example: SELECT {fn DEGREES(1)} AS "DegreesReturned", CompanyName AS “CompanyName” FROM Company

Returns:

EXP ( float_exp )

Instruction: Returns the exponential value of float_exp.

Example: SELECT {fn EXP({fn CONVERT(1, SQL_FLOAT)})} AS "ExpReturned", CompanyName AS “CompanyName” FROM Company

Returns:

FLOOR (numeric_exp|float_exp|integer_exp)

Instruction: Returns the largest integer less than or equal to numeric_exp.

Example: SELECT "Name", {fn FLOOR("Balance")} AS "FloorBalance", "Balance" FROM Customer

Returns:

LOG ( float_exp )

Instruction: Returns the natural logarithm of float_exp.

Example: SELECT {fn LOG({fn CONVERT(25, SQL_FLOAT)})} AS "LogReturned", CompanyName AS “CompanyName” FROM Company

Returns:

LOG10 ( float_exp )

Instruction: Returns the base ten logarithm of float_exp.

Example: SELECT {fn LOG10({fn CONVERT(25, SQL_FLOAT)})} AS "Log10Returned", CompanyName AS “CompanyName” FROM Company

Returns:

MOD ( integer_exp1, integer_exp2 )

Instruction: Returns the remainder (modulus) of integer_exp1 divided by integer_exp2.

Example: SELECT {fn MOD(27, 7)} AS "Mod7Returned", CompanyName AS “CompanyName” FROM Company

Returns:

PI ()

Instruction: Returns the constant value of pi as a floating point value.

Example: SELECT {fn PI()} AS "PI", CompanyName AS “CompanyName” FROM COMPANY

Returns:

POWER ( numeric_exp|float_exp|integer_exp, integer_exp )

Instruction: Returns the value of numeric_exp to the power of integer_exp.

Example: SELECT {fn POWER(4, 3)} AS "PowerValue", CompanyName AS "CompanyName" FROM COMPANY

Returns:

RADIANS ( numeric_exp|float_exp|integer_exp )

Instruction: Returns the number of radians converted from numeric_exp degrees.

Example: SELECT {fn RADIANS(57.29578)} AS "RadiansValue", CompanyName AS “CompanyName” FROM COMPANY

Returns:

RAND ([ integer_exp|float_exp|numeric_exp ])

Instruction: Returns a random floating point value using integer_exp as an optional seed value.

Example: SELECT {fn RAND()} AS "RandValue" FROM COMPANY

Returns:

ROUND ( numeric_exp|float_exp|integer_exp, integer_exp )

Instruction: Returns numeric_exp rounded to integer_exp placed right of the decimal point. If integer_exp is negative, numeric_exp is rounded to | integer_exp | and placed to the left of the decimal point.

Example: SELECT "Name", {fn ROUND(Balance, 1)} AS "RoundBalance", "Balance" FROM Customer

Returns:

SIGN ( numeric_exp|float_exp|integer_exp )

Instruction: Returns an indicator or the sign of numeric_exp. If numeric_exp is less than zero, -1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is more significant than zero, one is returned.

Example: SELECT "Name", {fn SIGN(Balance)} AS "SignOfBalance", "Balance" FROM Customer

Returns:

SIN ( float_exp )

Instruction: Returns the sine of float_exp, where float_exp is an angle expressed in radians.

Example: SELECT {fn SIN({fn CONVERT(1, SQL_FLOAT)})} AS "SINValue", CompanyName AS “CompanyName” FROM Company

Returns:

SQRT ( float_exp )

Instruction: Returns the square root of float_exp.

Example: SELECT {fn SQRT({fn CONVERT(47, SQL_FLOAT)})} AS "SQRTValue", CompanyName AS “CompanyName” FROM Company

Returns:

TAN ( float_exp )

Instruction: Returns the tangent of float_exp, where float_exp is an angle expressed in radians.

Example: SELECT {fn TAN({fn CONVERT(1, SQL_FLOAT)})} AS "TANValue", CompanyName AS “CompanyName” FROM Company

Returns:

TRUNCATE ( numeric_exp|float_exp|numeric_exp, integer_exp )

Instruction: Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to | integer_exp | places to the left of the decimal point.

Example: SELECT "Name", {fn TRUNCATE(Balance, 1)} AS "TruncateBalance", "Balance" FROM Customer

Returns:

QODBC Time and Date Functions

CURDATE ()

Instruction: Returns the current date as a date value.

Example: SELECT {fn CURDATE()} AS "CurDate" FROM Company

Returns:

CURRENT_DATE ()

Instruction: Returns the current date as a date value.

Example: SELECT {fn CURDATE_DATE()} AS "CurrentDate" FROM Company

Returns:

CURTIME()

Instruction: Returns the local time as a time value.

Example: SELECT {fn CURTIME()} AS "CurTime" FROM Company

Returns:

CURRENT_ TIME()

Instruction: Returns the current time as a time value.

Example : SELECT {fn CURRENT_TIME()} AS "CurrentTime" FROM Company

Returns:

CURRENT_ TIMESTAMP()

Instruction: Returns the current date and time as a timestamp value.

Example: SELECT {fn CURRENT_TIMESTAMP()} AS "CurrentTimeStamp" FROM Company

Returns:

DAYNAME ( date_exp )

Instruction: Returns a character string containing the data source-specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English) for the day portion of date_exp.

Example: SELECT {fn DAYNAME({fn CURDATE()})} AS "CurDayName", CompanyName AS "CompanyName" FROM Company

Returns:

DAYOFMONTH ( date_exp| string_exp|timestamp_exp )

Instruction: Returns the day of the month in date_exp as an integer value in the range of 1-31.

Example: SELECT {fn DAYOFMONTH({fn CURDATE()})} AS "CurDayOfMonth" FROM Company

Returns:

DAYOFWEEK ( date_exp )

Instruction: Returns the day of the week in date_exp as an integer value in the range of 1-7, where one represents Sunday.

Example: SELECT {fn DAYOFWEEK({fn CURDATE()})} AS "CurDayOfWeek" FROM Company

Returns:

DAYOFYEAR ( date_exp )

Instruction: Returns the day of the year in date_exp as an integer value in the range of 1-366.

Example: SELECT {fn DAYOFYEAR({fn CURDATE()})} AS "CurDayOfYear" FROM Company

Returns:

EXTRACT( date_exp|timestamp_exp )

Instruction: Returns the part of date_exp or timestamp_exp.

Example: SELECT {fn EXTRACT(YEAR FROM{fn CURRENT_DATE()})} AS "Year",{fn EXTRACT(MONTH FROM{fn CURRENT_DATE()})} AS "Month",{fn EXTRACT(DAY FROM{fn CURRENT_DATE()})} AS "Day", CompanyName as "CompanyName" FROM Company

Returns:

HOUR ( time_exp| string_exp|timestamp_exp )

Instruction: Returns the hour in time_exp, char_exp, or timestamp_exp as an integer value in the range of 0-23.

Example: SELECT {fn HOUR({fn CURTIME()})} AS "CurHour" FROM Company

Returns:

MINUTE ( time_exp| string_exp|timestamp_exp )

Instruction: Returns the minute in time_exp as an integer value in the range of 0-59.

Example: SELECT {fn MINUTE({fn CURTIME()})} AS "CurMinute" FROM Company

Returns:

MONTH ( date_exp| string_exp|timestamp_exp )

Instruction: Returns the month in date_exp as an integer value in the range of 1-12.

Example: SELECT {fn MONTH({fn CURDATE()})} AS "CurMonth" FROM Company

Returns:

MONTHNAME ( date_exp| string_exp|timestamp_exp )

Instruction: Returns a character string containing the data source-specific name of the month (for example, January through December or Jan. through Dec. for a data source that uses English) for the month portion of date_exp.

Example: SELECT {fn MONTHNAME({fn CURDATE()})} AS "CurMonthName”, CompanyName AS "CompanyName" FROM Company

Returns:

NOW ()

Instruction: Returns the current date and time as a timestamp value.

Example: SELECT {fn NOW()} AS "Now" FROM Company

Returns:

QUARTER ( date_exp| string_exp|timestamp_exp )

Instruction: Returns the quarter in the date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31.

Example: SELECT {fn QUARTER({fn CURDATE()})} AS "CurQuarter", CompanyName AS "CompanyName" FROM Company

Returns:

SECOND ( time_exp| string_exp|timestamp_exp )

Instruction: Returns the second in time_exp as an integer value in the range of 0-59.

Example: SELECT {fn SECOND({fn CURTIME()})} AS "CurSecond" FROM Company

Returns:

TIMESTAMPADD ( interval, integer_exp, timestamp_exp )

Instruction: Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_SECOND, SQL_TSI_DAY, SQL_TSI_MINUTE, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR, where fractional seconds are expressed in billionths of a second.

Notes: If timestamp_exp is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp.

     If timestamp_exp is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp.

Example: SELECT Name, {fn TIMESTAMPADD(SQL_TSI_YEAR, 1, HiredDate)} AS "Anniversary" FROM Employee

Returns:

TIMESTAMPDIFF ( interval, timestamp_exp1, timestamp_exp2 )

Instruction: Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_SECOND, SQL_TSI_DAY, SQL_TSI_MINUTE, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR, where fractional seconds are expressed in billionths of a second.

Note: If either timestamp expression is a time value and the interval specifies days, weeks, months, quarters, or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps.

     If either timestamp expression is a date value and the interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps.

Example: SELECT Name, {fn TIMESTAMPDIFF(SQL_TSI_YEAR, {fn CURDATE()}, HiredDate)} AS "YearsWorked” FROM Employee

Returns:

WEEK ( date_exp )

Instruction: Returns the week of the year in date_exp as an integer value in the range of 1-53.

Example: SELECT {fn WEEK({fn CURDATE()})} AS "CurWeek" FROM Company

Returns:

YEAR ( date_exp|string_exp|timestamp_exp )

Instruction: Returns the year in date_exp as an integer value.

Example: SELECT {fn YEAR({fn CURDATE()})} AS "CurYear" FROM Company

Returns:

QODBC System Functions

DATABASE ()

Instruction: Returns the name of the database in use at the time this function is called.

Example: SELECT {fn DATABASE()} AS "OpenDatabase" FROM Company

Returns:

IFNULL ( exp, value )

Instruction: If exp is null, the value is returned. If exp is not null, exp is replaced. The possible data type(s) of value must be compatible with the data type of exp.

Example: Select Name, {fn IFNULL(Fax, 'Missing Fax')} as "FixedFax" from Employee

Returns:

PARENT( string_exp )

Instruction: Returns the characters before the colon it finds in string_exp

Example : SELECT {fn PARENT(FullName)} AS “ParentValue”,FullName FROM Account CALLDIRECT

Returns:

CHILD ( string_exp )

Instruction: Returns the characters after the colon it finds in string_exp

Example: SELECT {fn CHILD(FullName)} AS “ChildValue”,FullName FROM Account CALLDIRECT

Returns:

CONVERT (integer_exp, sqltype_exp)

Instruction: Returns the slqtype value, which is converted from an integer value and can be used in numeric functions.

Example: SELECT {fn CONVERT(21,SQL_FLOAT)} AS "ConverValue", CompanyName AS "CompanyName" FROM Company

Returns:

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:

Ran this SQL to move an invoice from one Customer to another:

Update invoice set CustomerRefListID = '1500' where TxnNumber = 8453

It took about 15+ minutes, and the status dialog showed it waiting on QuickBooks as it went through all 2,500 customers - but it took over 15 minutes to complete! I have a situation where moving an invoice to another Customer may occur several times in a batch of invoices. Are there additional conditions I can put in the WHERE clause that would speed this up?

Solution:

Please make sure Optimizer is enabled.  - https://qodbc.com/links/2358

If you are executing an SQL statement for the first time on this table, QODBC has built the optimizer before processing the SQL statement. "Waiting on QuickBooks" may take longer than usual.

Please run the SQL statement again, and if you are still facing the same issue, please try the following options.

Make use of the index fields, such as TxnID or TxnDate, to filter the number of records scanned.

Update invoice set CustomerRefListID = '1500' where TxnID='ValueOfYourTxnID'

or

Update invoice set CustomerRefListID = '1500' where TxnDate>={d'2022-11-10'} and  TxnDate<={d'2022-11-15'} and  TxnNumber = 8453

If you are still facing the issue, please try using the UNOPTIMIZED keyword.

Update invoice UNOPTIMIZED set CustomerRefListID = '1500' where TxnID='ValueOfYourTxnID'

Problem Description:

We have low performance in SQL Server Linked Server using QREMOTE.

For example, the below statement takes less than 2 seconds in the QODBC 64-bit Test tool and more than 2 minutes in our linked server:
UPDATE Customer SET JobTitle = 'Job3' WHERE ListID='8000000F-1466614340'.

In SQL Server, the statement seems to scan the whole table to be executed (14000 records)

We are running QuickBooks and SQL Server on the same machine on a cloud server. All drivers are 64bits. The same system DSN is used in Test Tool and in SQL Linked server: QuickBooks Data 64-bit QRemote.

Solution:

Please make sure Optimizer is enabled.  - https://qodbc.com/links/2358

Update QREMOTE...customer set JobTitle = 'Job' where ListID='8000000F-1466614340'

When you use the above query, SQL Server will fetch all the data from the Customer table. SQL Server will then apply the filter of List ID.

SQL Server will generate an update statement from the derived result and send it to QODBC/QRemote.

Thus, for a single record update, the entire table is scanned.

To avoid this, use OpenQuery and select only the record you want to update.

Please use the Index fields in the "select" and "update" clauses.

Example:
update openquery (QRemote, 'select ListID, JobTitle from Customer where ListID=''8000000F-1466614340''') set JobTitle = 'Job' where ListID='8000000F-1466614340'

Note: Here, we are using ListID (the index field) in the select statement and the OpenQuery update statement.

If you are still facing the issue, please use the UNOPTIMIZED keyword.

update openquery (QRemote, 'select ListID, JobTitle from Customer UNOPTIMIZED where ListID=''8000000F-1466614340''') set JobTitle = 'Job' where ListID='8000000F-1466614340'

Problem Description:

We have searched the wiki and the internet and tried endlessly to 'Insert an Employee.'

Below is our latest effort, all to no avail-

Insert into Employee (FirstName, LastName, EmployeeAddressAddr1, EmployeeAddressAddr2, EmployeeAddressState, EmployeeAddressPostalCode) Values ('Jake,' 'Wrestl,' '123 Main','456 stand', 'FL,' '12345')

I do not know what is wrong.

Can you shed some light on the error below, and 'More Simply' provide a Sample to 'Insert an Employee'?

Solutions: 

The solution is to include the field 'EmployeeAddressCity' in your SQL statement.

For Example:

Insert into Employee (FirstName, LastName, EmployeeAddressAddr1, EmployeeAddressAddr2, EmployeeAddressState, EmployeeAddressCity, EmployeeAddressPostalCode) Values ('Jake,' 'Wrestl,' '123 Main','456 stand', 'FL,' 'Coral,' '12345')

You can view the newly added Employee records in the QuickBooks application.

Go to QuickBooks, and you will see the Employee has been added.

In the "Address & Contact" tab, you will see the city information added in QuickBooks.

The "EmployeeAddressCity" field is not mandatory to use when inserting an Employee record, but there are specific fields that are conditionally required.

For Example:

If you specify FirstName, the Last name is optional. Either FirstName or LastName is required.

Tags: Employees, Employee insert

Download Sample

Sample Code for using QODBC DSN:

$connectstring = "DSN=QuickBooks Data;"

$sql = "Select TOP 10 Name from Customer"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

$conn.open()

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)

$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)

$dt = New-Object system.Data. data table

$null = $da.fill($dt)

$conn.close()

$dt

Script execution & Result:

Open Windows PowerShell (x86) from the Start menu:

Navigate to your PowerShell script folder:

Run your PowerShell script:

Result:

Sample Code for using QRemote DSN:

$connectstring = "DSN=QuickBooks Data QRemote;"

$sql = "Select TOP 10 Name from Customer"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

$conn.open()

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)

$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)

$dt = New-Object system.Data. data table

$null = $da.fill($dt)

$conn.close()

$dt

Please refer to how to configure QRemote.

Please Note: If you have a 64-bit application, you must use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote."

Also, Refer:

QODBC Reports Architecture Mismatch Problem When Connecting

Problem Description:

I am trying to update records through the select statement. But it is not working.

UPDATE Customer SET CustomFieldProjectNumber = i.JobNumber FROM ( SELECT ListID, {fn SUBSTRING("Name", 1, 5)} AS JobNumber FROM Customer WHERE TimeCreated > {ts '2016-01-01 00:00:00.000'} ) i WHERE i.ListID = Customer.ListID

I am getting the "Unexpected extra token: (" error in QODBC Test Tool.  

Any Assistance Is Appreciated.

Solution:

You can execute the query below in MS Access linked table without any issue.

Please refer to How to Use QODBC with MS Access 2016 / 365 / 2013 32-bit

You need to link the Customer table in MS Access & execute the query in the Access query editor.

MS Access>> create tab >> Query Design >> Paste query >> Execute query using Run button

Update Customer qe inner join Customer de on qe.ListID = de.ListID set qe.CustomFieldProjectNumber = MID (de.Name,1,5) WHERE qe.TimeCreated >#2016-01-01 00:00:01#

Problem Description:

Good Day To You. Using This Sp_report And Having Some Problems With The Column Layout. Command Text Is As Follows: Sp_report Profitandlossbudgetvsactual Show Account name, Account number, Amount, Budget Parameters Fiscalyear=2015, Classfilterfullnames =' health Center,' 'administrative.'

I'm Getting 12 Columns, As One Would Expect, But The 12th Column Is The Sum Of The Previous 11 Columns, Not The 12th Month. This Is True For Both The Amount And Budget Segments. Also, The Budget Amount For The 11th Month is exactly one-half of the Actual Budgeted Amount. I Have Run The Actual Report, And The Totals For Each Month Are Correct For Both the Amount And Budget Until The Last Two Months In The report.

Also, if I replace the FiscalYear=2015, with DateMacro=’ThisYear’ I get this



Any Assistance Is Appreciated.  

Solution:

Please ensure that you are passing the same parameter in the report query you passed in QuickBooks UI.

Also, for the budget report, the Fiscal Year parameter is required. Without it, a report will not execute. So you need to keep the Fiscal Year parameter & add other date parameters.

For Example:

sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, DateFrom={d'2015-10-01'}, DateTo={d'2016-09-30'}, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

Problem Description:

I am using the code below. It is returning records & giving me the correct result. But if I change the query from "SELECT Name FROM customer" to "SELECT * FROM customer," it does not return any records & giving me the incorrect result. I am getting the following error:

[QODBC] Internal error
440: BOF or EOF is True, or the current record has been deleted. The requested operation requires a current record.



Public Sub example select()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\QuickBooks\Maintenance.qbw;OpenMode=F;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(100)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(100)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************"
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub 

Solution:

To resolve the issue, you need to add the line below to your connection string.

OptimizerAllowDirtyReads=N

For Example:

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\QuickBooks\Maintenance.qbw;OpenMode=F;OLE DB Services=-2;OptimizerAllowDirtyReads=N"

After changing this, you can get the correct result.

#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include <conio.h>

using namespace std;

void ConvertTimeStampStructToString(TIMESTAMP_STRUCT tTimeStamp, CHAR *strTimeStamp, LONG lSizeTimeStamp)
{
strcpy(strTimeStamp, "");
sprintf(sr timestamp, "%d-%d-%d %d:%d:%d," tTimeStamp.year, tTimeStamp.month, tTimeStamp.day, tTimeStamp.hour, tTimeStamp.minute, tTimeStamp.second);
}

int main()
{
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle

SQLCHAR sListId[256] = "";
SQLCHAR sEditSequence[256] = "";
SQLCHAR sName[256] = "";
BOOL bIsActive = 0;
TIMESTAMP_STRUCT tTimeModified = {0};
TIMESTAMP_STRUCT tTimeCreated = {0};
SQLINTEGER cbData;

SQLRETURN retcode;
int iKey;

CHAR *sTimeStamp = new CHAR[32]();

SQLCHAR statement[]="SELECT * FROM ShipMethod";

retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if(retcode != SQL_SUCCESS){
cout << "SQLAllocHandle failed. exit program" << endl;
return 0;
}
cout << "SQLAllocHandle: Environment handle initialized." << endl;

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(SQLCHAR *)(void*)SQL_OV_ODBC2, -1);
if(retcode != SQL_SUCCESS){
cout << "SQLSetEnvAttr failed. exit program" << endl;
return 0;
}
cout << "SQLSetEnvAttr : ODBC 2.0 initialized." << endl;

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if(retcode != SQL_SUCCESS){
cout << "SQLAllocHandle failed. exit program" << endl;
return 0;
}
cout << "SQLSetEnvAttr: Database handle initialized." << endl;

retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
if(retcode != SQL_SUCCESS){
cout << "SQLSetConnectAttr failed. exit program" << endl;
return 0;
}
cout << "SQLSetConnectAttr : Set Login Timeout." << endl;

retcode = SQLConnect(hdbc, (SQLCHAR*) "QuickBooks Data" , SQL_NTS, (SQLCHAR*) NULL, 0, NULL, 0);
if(retcode != SQL_SUCCESS){
cout << "SQLConnect failed. exit program" << endl;
return 0;
}
cout << "SQLConnect : Database connection successfully." << endl;

retcode = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
if(retcode != SQL_SUCCESS){
cout << "SQLAllocHandle failed. exit program" << endl;
return 0;
}
cout << "SQLAllocHandle : Handle initialized." << endl;

retcode = SQLExecDirect(hstmt, statement, SQL_NTS);
if(retcode != SQL_SUCCESS){
cout << "SQLExecDirect failed. exit program" << endl;
return 0;
}
cout << "SQLExecDirect : Query executed successfully." << endl;

cout << endl << endl << endl;
cout << "Query: SELECT * FROM ShipMethod " << endl;
cout << endl << endl << endl;
cout << "ListId" << ", " << "TimeModified" << ", " << "TimeCreated" << ", " << "EditSequence" << ", " << "Name" << ", " << "IsActive" << endl << endl;
cout << endl << endl;
while(SQLFetch(hstmt) == SQL_SUCCESS){

retcode = SQLGetData(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)sListId, sizeof(sListId), &cbData);

retcode = SQLGetData(hstmt, 2, SQL_C_TIMESTAMP, (SQLPOINTER)&tTimeModified, sizeof(tTimeModified), &cbData);

retcode = SQLGetData(hstmt, 3, SQL_C_TIMESTAMP, (SQLPOINTER)&tTimeCreated, sizeof(tTimeCreated), &cbData);

retcode = SQLGetData(hstmt, 4, SQL_C_CHAR, (SQLPOINTER)sEditSequence, sizeof(sEditSequence), &cbData);

retcode = SQLGetData(hstmt, 5, SQL_C_CHAR, (SQLPOINTER)sName, sizeof(sName), &cbData);

retcode = SQLGetData(hstmt, 6, SQL_C_BIT, (SQLPOINTER)bIsActive, sizeof(bIsActive), &cbData);

cout << sListId << ", ";
ConvertTimeStampStructToString(tTimeModified, sTimeStamp, sizeof(sTimeStamp));
cout << sTimeStamp << ", ";
ConvertTimeStampStructToString(tTimeCreated, sTimeStamp, sizeof(sTimeStamp));
cout << sTimeStamp << ", ";
cout << sEditSequence << ", ";
cout << sName << ", ";
cout << bIsActive << endl;
}
delete [] sTimeStamp;
cout << endl << endl;

retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if(retcode != SQL_SUCCESS){
cout << "SQLFreeHandle failed. exit program" << endl;
return 0;
}
cout << "SQLFreeHandle : Handle freed." << endl;

retcode = SQLDisconnect (hdbc);
if(retcode != SQL_SUCCESS){
cout << "SQLDisconnect failed. exit program" << endl;
return 0;
}
cout << "SQLDisconnect : Close database connection." << endl;

retcode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
if(retcode != SQL_SUCCESS){
cout << "SQLFreeHandle failed. exit program" << endl;
return 0;
}
cout << "SQLFreeHandle : Database handle freed." << endl;


retcode = SQLFreeHandle(SQL_HANDLE_ENV, henv);
if(retcode != SQL_SUCCESS){
cout << "SQLFreeHandle failed. exit program" << endl;
return 0;
}
cout << "SQLFreeHandle : Environment handle freed." << endl;

printf("Press Any Key To Exit...");
getch();
return 0;
}

Please click here to download the C++ code.

Problem Description:

Could someone kindly provide me with a sample, or is there a stored procedure that would allow me to only pull out back-ordered invoices from the invoice table?

If a customer orders some items, we create an invoice. Then, if some things are back-ordered, when the packing slip is made, it shows they were not sent out, and that item is back-ordered. I was looking for a query or an SP that would lead me to only invoices that have not been completely shipped, or all the items in backorder. 

Solution:

All you need to do is use the ItemInventory table to pick up the quantity on hand and compare it with the ordered quantity ordered and invoiced to date (invoiced or not), and call the column "Qty to Pick."

Something along the lines of:

SELECT SalesOrderLine.SalesOrderLIneItemRefFullName as Type, SalesOrderLine.TxnDate as Date, SalesOrderLine.DueDate, SalesOrderLine.RefNumber as Num, SalesOrderLine.CustomerRefFullName as Name, SalesOrderLine.SalesOrderLineQuantity as "Qty Ordered," SalesOrderLine.SalesOrderLineInvoiced as "Qty Invoiced," (SalesOrderLine.SalesOrderLineQuantity - SalesOrderLine.SalesOrderLineInvoiced) as "Qty to Pick," ItemInventory.QuantityOnHand as "Qty Available," ItemInventory.QuantityOnOrder as "Qty on Back Order" FROM SalesOrderLine, ItemInventory where SalesOrderLine.SalesOrderLineItemRefListID = ItemInventory.ListID and SalesOrderLine.IsFullyInvoiced = FALSE and SalesOrderLine.SalesOrderLineInvoiced < SalesOrderLine.SalesOrderLineQuantity

Problem Description:

All our products are Inventory Assemblies. I am using MS Access connected to QuickBooks via QODBC. I am using queries in MS Access to load/synchronize Inventory data from my master inventory list, which is kept outside of QuickBooks, to make sure QuickBooks has the latest inventory data.

I have successfully inserted ItemInventoryAssemblyLine entries, but now I'm trying to update the Quantity information on parts for existing assemblies.

To do the update, I need to join a local table I've created from the external inventory data, which I've loaded with the ListID, ItemInventoryAssemblyLnItemInventoryRefListID, and FQPrimaryKey values from ItemInventoryAssemblyLine, in order to facilitate joins.

Here's my SQL (from MS Access Query Designer):

UPDATE ItemInventoryAssemblyLine INNER JOIN Assemblies_with_PK ON ItemInventoryAssemblyLine.FQPrimaryKey = Assemblies_with_PK.FQPrimaryKey SET ItemInventoryAssemblyLine.ItemInventoryAssemblyLnQuantity = [Qty];

"Qty" is a column in the local table with the updated Quantity that I'd like to edit into the QuickBooks table.

When I run this query, I get the dreaded "Error parsing complete XML return string." error. I have tried alternatively joining on the combination of ListID and ItemInventoryAssemblyLnItemInventoryRefListID instead of FQPrimaryKey with the same result.

I have updated Custom Fields in the parent ItemInventoryAssembly table using the same approach with no problems.

Any help with this would be greatly appreciated! 

Solution:

Please update your QODBC to the latest QODBC version and test again.

Please click here to get the latest QODBC version.

If you are still getting the same error after updating the version, I kindly request you to please raise a support ticket to the QODBC Technical Support department by clicking here.

Problem Description:

How do I update Customer custom fields? 

Solution:

You can update Customer custom fields using the update query.

For Example:
Update Customer Set CustomFieldContract='QODBCJOB1' where ListID='800000F2-1513344011'

And there you have it! To check that everything worked, just do the following:
Select ListID, Name, FullName, CustomFieldContract from Customer where ListID='800000F2-1513344011'

You can check it on QuickBooks, also.

You can insert Custom fields during Customer creation also. Please refer to the sample query for the same.

INSERT INTO "customer" ("Name," "LastName," "FirstName," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalcode," "CustomFieldBDay," "CustomFieldContract," "CustomFieldSpousesName") values('Juniga, Daphne,' 'Juniga,' 'Daphne,' 'Daphne Juniga,' '561 W 4th St.', 'Carlsbad,' 'CA,' '92009', '26/01/1987', 'QODBCJOB1', 'Jack')

And there you have it! To check that everything worked, just do the following:
Select Name, CustomFieldBDay, CustomFieldContract, CustomFieldSpousesName from Customer where Name='Juniga, Daphne'

You can check it on QuickBooks, also.

Problem Description 1:

I recently installed QODBC to get data out of a QuickBooks company file, but I get this error: "Index was outside the bounds of the array."

Problem Description 2:

We are attempting to import tables into SQL Server, which we use as a staging environment. We are not doing any transformations, simply trying to copy the data from QuickBooks into SQL Server using the Import Wizard. Below is the screenshot of the error we receive when attempting to do so. Details of the configuration are provided below.

Source data (QuickBooks): Microsoft .NET provider for ODBC - QODBC 32-bit configured DSN with default settings. Destination Data ( SQL Server): Microsoft OLEDB provider for SQL Server

We are attempting to import four tables for now.

- Account, Class, Entity, Transactions

The reported error below occurs for each table.

Error: Can not load the column attributes from the schema for the current provider descriptor.
- COLUMN_NAME
- TYPE_NAME
- COLUMN_SIZE
- DECIMAL_DIGITS
- COLUMN_SIZE

Solution:

Please follow the steps below to resolve this error:

1. Go to Import Export Wizard by right-clicking on your SQL Server database, clicking on Task, & Click on Import Data.

2. Choose ODBC Data Source & Enter DSN & Connection String, and click "Next."

3. Choose Destination and click on "Next."

4. Choose the second option (Write a query to specify the data to transfer) and click "Next."

5. Write the query and click on "Next." You can change the table name & question as per your requirement.
For Example:
Select * from the Account

6. Change the destination table name to your requirement and click "Edit Mapping."

7. You must verify the date type of all columns from the "Type" column.

You need to make sure that you are not getting 23 in Type. You will get 23 date-type columns.

You need to replace 23 with the current date for all columns whose Type is 23 and click on "OK."

8. Click on "next" until the finish.

Save and Run Package.

Complete the Wizard.

9. You can see the table data imported to SQL Server's local database table.

Keywords: How to use DTS to import QuickBooks Data into MS SQL Server

Problem Description:

I'm trying to link a general ledger report in Excel. I've created the sp_report SQL query in Microsoft Query just fine, but the data it returns contains subtotals and blank lines. I know I can write a script/filter in Excel to eliminate these lines, but I can't figure out what parameters to use to do it using QODBC.

Solution:

In QODBC, you can remove subtotals and blank lines in sp_report by adding the following condition.

where RowType='DataRow'

For example:

sp_report ProfitAndLossDetail show TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisYearToDate' where RowType='DataRow'

Problem Description:

I have a custom program written in Access 2003 communicating with QuickBooks 2013 using QODBC 13, which works fine. Will upgrading to the latest QuickBooks and QODBC require any code changes to the Access 2003 program?

Problem Description:

We are upgrading from QuickBooks Pro 2011 to QuickBooks Enterprise (the latest) and need a developer/consultant to assist in checking/upgrading queries and code to work with the new versions of QODBC/QuickBooks. Our company uses the QODBC Driver for QuickBooks to synchronize data between our Microsoft Access database and QuickBooks. We push customers, jobs, cheques, and items from Access to QuickBooks and pull data from QuickBooks to Access for reports.  

Solution:

It is unlikely that you will have a problem as QODBC is upward compatible with QuickBooks, so the same queries you have running now should continue to work.

Suggest you purchase the latest version of QODBC, which supports the newest version of QuickBooks, then run some tests. If you have any trouble with connections, please refer to the following:

Connecting QODBC Driver to QuickBooks for the First Time (Video)
How to Use QODBC with Microsoft Access 2010 to Build Linked Table(32-Bit)
How to Configure QODBC to Work With 64-bit MS Access

Or Raise a Support Ticket

There shall be no code change required in your MS-Access Application/Queries, and it should be able to work with the latest QuickBooks & QODBC.

Additional Information:
With every release of QuickBooks/QODBC, new fields or features might be added. If you want to leverage the new areas, you will have to re-link the tables in Access. Refer: How to Re-Link QuickBooks Data in MS Access using QODBC.

Please refer to the link below for details about the updated QODBC table & report schema and the relation between tables. You will also get information on each report column parameter that is supported. You can also view all table/column details.

Please refer to QODBC Data Layout for more details.

Problem Description:

I need to use Between, in a range of dates, to return new customers in one period. I try this:

SELECT FullName FROM Customer Where TimeCreated Between {d'2007-12-18'} AND {d'2007-12-19'} AND SalesRepRefFullName='PD'

Is the use of QODBC correct? 

Solution:

The use of BETWEEN here is fine. It's just TimeCreated is a TIMESTAMP, not a date. It should be:

SELECT FullName FROM Customer Where TimeCreated Between {ts'2007-12-18 00:00:00.000'} AND {ts'2007-12-19 00:00:00.000'} AND SalesRepRefFullName='PD'

Problem Description:

I'm trying to make Inventory Items inactive after a sale programmatically or Active after voiding a sale.

I thought the following SQL statement would have worked fine, but I got error 214217887(80040e21), saying the ODBC driver doesn't support this statement.

sSQL = "UPDATE ItemInventory SET ItemInventory.IsActive = True " & _
"WHERE (((ItemInventory.ListID)='ED0000-1173904787'))" 

Solution:

QODBC doesn't support the Table name used in the SET columns. So you need to remove the table name before the column name in the SET condition. For example:

sSQL = "UPDATE ItemInventory SET IsActive = True " & _
"WHERE (((ListID)='ED0000-1173904787'))"

Problem Description:

What is the fastest way to get the number of records from a table? 

Solution:

QODBC would need to scan the whole table to get the record or row count. So you shouldn't do it if you don't need to. If you must do it, run the query against the optimized table using nosync like this:

SELECT count(listid) as "Table Row Count" from Customer nosync

Problem Description:

I created a report using a custom set with the customer as the parent and InvoiceLine as the child, giving me items ordered in the past.

My problem is that I only want the last time an item was ordered and want to eliminate some things, such as shipping.

When I try to filter out items like shipping, it also takes out any invoice line items.

I'm at a complete loss when it comes to getting only the last time an item was ordered.  

Solution:

You can get items ordered by a customer, in part name order, with the last date ordered first,t by using the below query:

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian: Kitchen' and InvoiceLineItemRefFullName is not null Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc



Or for the last time an item was ordered by a customer:

SELECT Top 1 TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName = 'Appliance' Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc

Problem Description:

We are involved in a new project with our bank involving a 'Positive Payment' solution that requires I send them a file with the check summary data each time we do a check printing run. The example layout I received from this looks like this: (best viewed with a fixed font)

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8

RECONCILIATIONHEADER0075207990012345600000001000000005
20799001234560000071909000005100020040512 Abelman, Frayne & Schwab
20799001234560000071910000000200020040512 American Express
20799001234560000071911000000200020040512 Arizona Dept. of Revenue
20799001234560000071912000000200020040512 Arrow Electronics Inc.
20799001234560000071913000000200020040512 ASCOM Hasler Mailing Sys

I can manage the formatting if I can get the data into Access, but I need to grab the check number, check amount, issue date, void indicator, if that's available, and the payee name.

Can I do this? 

Solution:

If you are paying bills each day and printing them in a daily batch, then you can try looking at the following:

SELECT RefNumber, Amount, TxnDate, Memo, PayeeEntityRefFullName FROM BillPaymentCheck where BankAccountRefFullname='Checking' and IsToBePrinted is NULL and TxnDate={fn CURDATE()}

Problem Description:

I want to update the salesrep field in my existing invoices. I want to use the salesrep value stored in the Customer file. I would normally do this in SQL using a correlated subquery. When I run the following query: update Invoice set SalesReprefFullName = (select SalesRepRefFullName from Customer where Invoice.CustomerrefFullName = Company.FullName), I get the following error: [QODBC] Expected lexical element not found:

Solution:

Sorry, even your nested SELECT statement is invalid. It would be best if you always referenced the TxnID for all update operations using QODBC. For example, for Invoices, the update operation would look like this:

Update Invoice set SalesRepRefListID='10000-1197757777' where Invoice.TxnID='2B55-1071523067'

Where:

SalesRepRefListID is the List ID for an existing Sales Representative from the SalesRep table.
TxnID is the Transaction ID for the existing Invoice from the Invoice table.

Note: QODBC does not support direct import, but you can write VBA code that reads a CSV / Excel file & generates insert statements.

There are two ways to import data to QuickBooks through CSV using QODBC.

1) Using Wizard

Create a new MS Access Database file & link the Customer table.

Please refer to Using QuickBooks Data with Access 2016 / 365 / 2013 32-bit for linking the Customer table.

Go to the "External Data" tab on Microsoft Access, as shown in the screenshot below, and click on the "Text File."

The "Get External Data" screenshot will be shown below. Select the CSV file by clicking the "Browse" button. (*Note: the file name must be less than 64 characters. Otherwise, Microsoft Access will not be able to import it. Choose the second option. Append the copy of the records to the table.

Next, select the option "Delimited - Characters such as comma or tab separate each field," as shown in the screenshot below, and click "Next."

Next, make sure "Comma" is selected in the "Choose the delimiter that separates your fields" and the double quotes symbol "s selected in the "Text Qualifier" as shown in the screenshot below, and click "Next" to continue.

Now, click on the Advanced button to configure import fields.

Now write the field name you want to insert from the CSV file & click OK.

Click Finish to complete the import process.

Import Done. Customer data from CSV is inserted into the QuickBooks customer table.

Or

2) Using VBA Code

Please refer to Using QuickBooks Data with VBA

In this tutorial, we are showing a sample VBA script inserting Customer & Invoice.

Importing Customer to QuickBooks through CSV file using QODBC & Microsoft Access VBA

We have a Customer CSV file with Customer Name, Company Name, Phone & Email fields.

We will import these fields to QuickBooks using VBA. You can see the records below from the Customer CSV file.

Please refer to the sample VBA code for Importing Customers to QuickBooks through a CSV file using QODBC.

By clicking on the "Add Customer" button, the Customer list in a CSV file will be imported into QuickBooks using QODBC.

Please Note: You need to change the CSV file location & VBA script according to your CSV file location & file data.

Please refer to the code below code which is used in this example:

Import Customer:

Option Compare Database

Public Sub exampleCsvImportCustomer()

Dim oConnection As New ADODB.Connection
Dim sConnectString
Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim sMsg
Dim rs
Dim I As Integer
i = 0
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"

sSQL = "SELECT * FROM customer"

Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs. Open sSQL, Connection, adopt dynamic, adLockOptimistic

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("C:\Input\Customer.csv") Then
Set objStream = fso.OpenTextFile("C:\Input\Customer.csv", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
ReDim MyArray(0)
MyArray = Split(strLine, ",")
rs.AddNew
rs("Name") = MyArray(0)
rs("CompanyName") = MyArray(1)
rs("Phone") = MyArray(2)
rs("Email") = MyArray(3)
rs.Update
i = i + 1

Loop

sMsg = sMsg & "Customer Added!!!"
MsgBox sMsg

End Sub

Please Note: If you have a 64-bit application, then you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote" (i.e., sConnectString = "DSN=QuickBooks Data 64-bit QRemote; OLE DB Services=-2;")

Importing Invoice to QuickBooks through CSV file using QODBC & Microsoft Access VBA

We have an Invoice CSV file with CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, InvoiceLineSalesTaxCodeRefListID & FQSaveToCache fields.

In this example, we are creating two Invoices, each having 3 invoice lines. We will import these fields into QuickBooks using VBA. You can see the records below from the Invoice CSV file.

Please refer to the sample VBA code for Importing Invoices to QuickBooks through a CSV file using QODBC.

By clicking the "Add Invoice" button, the Invoice list in a CSV file will be imported into QuickBooks using QODBC.

Please Note: You need to change the CSV file location & VBA script according to your CSV file location & file data.

Please refer to the code below code which is used in this example:

Import Invoice:

Option Compare Database

Public Sub exampleCsvImportInvoice()
Dim oConnection As New ADODB.Connection
Dim sConnectString
Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim rs
Dim sMsg
Dim I As Integer
i = 0

sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"

sSQL = "SELECT * FROM InvoiceLine"

Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs.Open sSQL, oConnection, adOpenDynamic, adLockOptimistic

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("C:\Input\Invoice.csv") Then
Set objStream = fso.OpenTextFile("C:\Input\Invoice.csv", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
ReDim MyArray(0)
MyArray = Split(strLine, ",")
rs.AddNew
rs("CustomerRefListID") = MyArray(0)
rs("RefNumber") = MyArray(1)
rs("InvoiceLineItemRefListID") = MyArray(2)
rs("InvoiceLineDesc") = MyArray(3)
rs("InvoiceLineRate") = MyArray(4)
rs("InvoiceLineQuantity") = MyArray(5)
rs("InvoiceLineSalesTaxCodeRefListID") = MyArray(6)
rs("FQSaveToCache") = MyArray(7)
rs.Update
i = i + 1

Loop

sMsg = sMsg & "Invoice Added!!!"
MsgBox sMsg

End Sub

Please Note: If you have a 64-bit application, then you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote" (i.e., sConnectString = "DSN=QuickBooks Data 64-bit QRemote; OLE DB Services=-2;")

Download Sample

Also, Refer:

QODBC Reports Architecture Mismatch Problem When connecting

Problem Description:

How to stop any query on VBDEMO when it takes time.

How to convert the date to a QuickBooks date.

I want to insert TxnDate in the ReceivePayment table, but it pops up the operator and operand error.

I am just inserting values in the ReceivePayment Table. My Query is:

INSERT INTO ReceivePayment (CustomerRefListID, ARAccountRefListID,txndate, RefNumber, PaymentMethodRefFullName, Memo, DepositToAccountRefListID, TotalAmount, IsAutoApply) VALUES ('B480000-1171302186', '3C0000-1164634404', '2007-02-12', '10667', 'SECPAY,' '72465', '80000-1164634403',54.04, TRUE)

I am looking for your early reply with thanks. 

Solution:

You can stop query execution by clicking "Stop Query" from the QODBC Icon on the system tray. Right-click on the QODBC Icon in the system tray and click on "Stop Query" for 5 or 10 minutes.



If the above does not work, and you need to close VB Demo, use Alt-Ctrl-Del and the Windows Task Manager to end the VB Demo application task:





QODBC uses the standard SQL date format: {d'YYYY-MM-DD'}. You will get an Invalid operand for operator error when you provide the wrong data type values in your query. Please refer to Troubleshooting - [QODBC] Invalid operand for operator

INSERT INTO ReceivePayment (CustomerRefListID, ARAccountRefListID,txndate, RefNumber, PaymentMethodRefFullName, Memo, DepositToAccountRefListID, TotalAmount, IsAutoApply) VALUES ('B480000-1171302186', '3C0000-1164634404', {d'2007-02-12'}, '10667', 'SECPAY,' '72465', '80000-1164634403',54.04, TRUE)

Problem Description:

The following selection gives me the correct data using DISTINCT, but how do I get a sum of it, which is what I need?

Select distinct billtopayduedate, billtopayrefnumber, and bill.vendorreffullname, billtopayamountdue, assetaccountreffullname from bill nosync, bill pay nosync, billitemline nosync, item nosync where account to pay. billtopaytxnid = bill. tuned and bill.ispaid=false and bill. txnid = billitemline. txnid and BillItemLine.ItemLineItemRefListID = Item.ListID and assetaccountreffullname like '%inventory%.'

I want my SELECT statement to return one number: a sum of all the records in "Bill to Pay" where one or more corresponding line items in the expense line or item line are for inventory.

Solution:

In SQL, we do sum(column) and group by, for example:

SELECT Employee.PayrollInfoClassRefFullName, sum(Transaction.Amount) as Amount FROM Transaction, Employee where Transaction.EntityRefListID=Employee.ListID and Transaction.TxnType='Paycheck' and (Transaction.TxnDate >= {d'2017-01-01'} and Transaction.TxnDate < {d'2018-01-01'}) Group by Employee.PayrollInfoClassRefFullName

To total all the BillToPay amounts, you do the following:

Select sum(BillToPayAmountDue) as OpenAPBalance from BillToPay.

To total all the BillToPay amounts involving stock (inventory) only and not expenses, you do the following:

Select sum(BillToPayAmountDue) as OpenInvtBalance from BillToPay, BillItemLine where BillToPay.BillToPayTxnID = BillItemLine.TxnID and BillItemLine.ItemLineSeqNo = 1

Problem Description:

I don't see any indicator in the Invoice table. How do I void an Invoice? And how do I query for invoices that have been voided? 

Solution:

You can use SP_VOID just like you would use DELETE. It will void the invoice instead of deleting it.

For Example:

SP_VOID FROM Invoice WHERE TxnID = '1B03F-1513346399'



You can use the query below to get a list of Voided Invoices.

Select RefNumber, CustomerRefFullName, and Memo from Invoice, like 'VOID:%.'

If no InvoiceLine is returned, you can resync your optimized InvoiceLine table by running:

Problem Description:

This is a no-technical question, but one I need to answer myself before jumping into QODBC. I'm considering working with QODBC to get the information we can't get easily from QuickBooks reports. I want to ask people working with QODBC, QuickBooks, and QuickBooks POS if QODBC can access and work with information related to the time of purchases. For example, view items that sell the most at 11 AM or between 4 and 5 PM, and also see what those items are. I don't know if this information is located in the QuickBooks or the QuickBooks POS file. If someone could tell me if I could access this type of information, I could start working with QODBC. I would appreciate any help. 

Solution:

You can use the query below to get time frame information using QODBC.

For Example:

SELECT {fn HOUR(TimeCreated)} as Hour, SalesLineQuantity as Qty, Type, RefNumber as Num, SalesLineItemRefFullName as Item, SalesLineDesc as Memo, SalesLineRate as SalesPrice, SalesLineAmount as Amount, TimeModified, CustomFieldContract as Contract# FROM SalesLine NOSYNC where (TxnDate >= {d'2007-12-01'} and TxnDate <={d'2007-12-31'}) and {fn CONVERT( {fn HOUR(TimeCreated)}, SQL_VARCHAR)} = '22' and SalesLineType='Item' and SalesLineItemRefFullName is not null and SalesLineQuantity > 0 order by SalesLineQuantity DESC, SalesLineItemRefFullName



Where '22' is the hour using a 24-hour format.
(TxnDate >= {d'2007-12-01'} and TxnDate <={d'2007-12-31'}) is the start and end date for the report

Problem Description:

The InvoiceLine table contains orphan records (the parent invoice record no longer exists). I tried:

DELETE FROM Invoice WHERE TxnID = 'xxxx-xxxxxxxxx'

The TxnID is supplied from a list before running the DELETE command.

However, no records are deleted. What am I doing wrong?  

Solution:

The first step is actually to check that there are orphaned InvoiceLines. I would hazard a guess that you have orphaned optimized InvoiceLine only and no real orphaned InvoiceLines in QuickBooks. Run the following query with the unoptimized tag using QODBC Test Tool:-

SELECT TxnID, InvoiceLineTxnLineID, InvoiceLineDesc, InvoiceLineAmount, RefNumber FROM InvoiceLine unoptimized where RefNumber = 'YourRefNumber.'

Where 'YourRefNumber' is the Reference to the Invoice.

If no InvoiceLine is returned, you can resync your optimized InvoiceLine table by running:

sp_optimizefullsync InvoiceLine

How can I get daily payment bill information? And how can I get daily Bill Payment information using QODBC, and how can I get these details?  

Solution:

You can use the query below to get details about daily Bill Payments.

For Example:

SELECT DueDate, VendorRefFullName as Vendor, RefNumber as Ref, AmountDue as "Amt. Due" FROM Bill where IsPaid = 0 and DueDate <= {d'2007-12-25'} order by DueDate

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Or

You can go straight to the BillToPay table like this.

SELECT BillToPay.BillToPayDueDate as "Date Due," Bill.VendorRefFullName as Vendor, BillToPay.BillToPayRefNumber as Ref, BillToPay.BillToPayAmountDue as "Amt. Due", BillToPay.CreditToApplyCreditRemaining as "Credit Remaining" FROM BillToPay, Bill where BillToPay.BillToPayTxnID = Bill.TxnID and BillToPay.BillToPayDueDate <= {d'2007-12-25'} order by BillToPay.BillToPayDueDate

Problem Description:

I need to compute a Year-to-Date total paid using the Invoice table. I'm working in MS-Access and need to run the following statement:

SELECT SUM(Subtotal) FROM Invoice WHERE CustomerRefListID = 'some customer ID' AND IsPaid AND TimeCreated BETWEEN date1 AND date2  

Solution:

You can use the query below to get details about records for a specified Year.

For Example:

Select * from Invoice where {fn YEAR(TxnDate)} = 2017

For more details, refer to: How are dates formatted in SQL queries when using the QuickBooks generated timestamps?

Problem Description:

How can I change the price of each item in a group? Is it possible? If yes, please give me an example.

Solution:

The ItemInventory, ItemService, and ItemOtherCharge item itself determines the price of an item within an ItemGroup. The cost of an ItemService item can be calculated using standard UPDATE statements, for example:

For Example:

Update ItemService Set SalesOrPurchasePrice=40 where Name='Floor Plans'

You cannot change the price or quantity of individual item group lines within an Invoice. To do that, you should read the item group lines and write each item separately on separate invoice lines. You can, however, state the quantity of the item group itself.

Problem Description:

The field ExpenseLineBillableStatus contains the status of the BillExpenseLine and uses the following stored procedure "sp_columns" command in QODBC Test Tool:

sp_columns BillExpenseLine

shows the: ExpenseLineBillableStatus valid values as: |Billable|NotBillable|HasBeenBilled|

However, the following insert failed:-

INSERT INTO BillExpenseLine (VendorReflistid, APAccountRefFullName,txndate, RefNumber, ExpenseLineAccountRefFullname, ExpenseLineAmount, ExpenseLinememo, ExpenseLineBillableStatus, ExpenseLineTaxCodeReffullname, FQSaveToCache ) VALUES ('290000-1045709633', 'Accounts Payable', {d'2006-02-01'}, '111111', 'Boosters COGS', 244, '2 of 222233433 aaaaaabbbbbca', 'Billable', 'NCG',0)

With an "Insert into a linked table, "BillExpenseLine failed with an error. 

Solution:

For the Billable, NotBillable, and HasBeenBilled flags to be used on a Bill Expense Line within QuickBooks, a Customer Job "ExpenseLineCustomerRefListID" must also be assigned.

To create a BillExpenseLine with a "Billable" status using two SQL insert statements:

Statement 1

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID," "ExpenseLineAmount," "ExpenseLineMemo," "ExpenseLineCustomerRefListID," "ExpenseLineBillableStatus," "ExpenseLineTaxCodeRefListID," "FQSaveToCache") VALUES ('270000-1045536344', 500.00, 'QODBC Expense Line Memo Test 1', '100000-1045537156', 'Billable,' 'C0000-1045536338', 1)

Statement 2

INSERT INTO "Bill" ("VendorRefListID," "APAccountRefListID," "TxnDate," "RefNumber," "TermsRefListID," "DueDate," "Memo") VALUES ('200000-1045537156', '3D0000-1045609540', {d'2006-02-02'}, '20061', '20000-1045536343', {d'2006-03-02'}, 'QODBC Bill Expense Memo Test')

The key to this process is in the field named "FQSaveToCache." This field is not part of the table but is used as a flag to the QODBC driver. In the sequence above, you should note that the value of "FQSaveToCache" is set to 1 (trustworthy) in the first statement.

A proper (1) setting of "FQSaveToCache" instructs QODBC to take the values from the INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 (false) or not specified, as shown in the second statement, the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection and saved as a batch into QuickBooks.

Note: The BillExpenseLine.ExpenseLineAccountRefListID must be an asset type. You cannot use a COGS (cost of goods sold) type account.

Create an ItemGroup with Three Lines

Note: Let's create a new item group example with three lines using QODBC. Note the FQSaveToCache field, set to True except on the last one.

First Line

INSERT INTO "ItemGroupLine" ("Name," "IsActive," "ItemDesc," "IsPrintItemsInGroup," "ItemGroupLineItemRefListID," "ItemGroupLineQuantity," "FQSaveToCache") VALUES ('Test1 W3 Lines 1', 1, 'Test Item Dec, 1, '8000009D-1481800631', 1.0, 1)

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Second Line

INSERT INTO "ItemGroupLine" ("Name," "IsActive," "ItemDesc," "IsPrintItemsInGroup," "ItemGroupLineItemRefListID," "ItemGroupLineQuantity," "FQSaveToCache") VALUES ('Test1 W3 Lines 2', 1, 'Test Item Dec, 1, '670004-1044572237', 2.0, 1)

Third (Last) Line

INSERT INTO "ItemGroupLine" ("Name," "IsActive," "ItemDesc," "IsPrintItemsInGroup," "ItemGroupLineItemRefListID," "ItemGroupLineQuantity," "FQSaveToCache") VALUES ('Test1 W3 Lines 3', 1, 'Test Item Dec, 1, '6D0003-1044572301', 3.0, 0)

Result in QuickBooks

The result looks like this in QuickBooks 2022:

SELECT ListID, Name, ItemDesc, IsActive FROM ItemGroup

Invoice the ItemGroup

Note: We can now create a PurchaseOrder using the item group by utilizing the ListID:'8000009F-1481791619' and making one simple QODBC insert statement below.

INSERT INTO "PurchaseOrderLine" ("VendorRefListID," "RefNumber," "PurchaseOrderLineGroupItemGroupRefListID," "FQSaveToCache") VALUES ('390000-1039739488', 'Group2', '8000009F-1481791619', 0)

Result in QuickBooks

When we look at the new invoice in QuickBooks 2013, we will see the following:

Note: Qty, Rate, and the Amount are shown as per Qty, Rate, and the Amount for the service or stock item

Problem Description:

I want to list the subtotal of all Invoices by a customer for not only that particular customer but all jobs assigned to that customer within a specified date range.

What QODBC SQL Statement would I use?

This is what I have so far:

SELECT SUM(Subtotal) AS gt FROM Invoice WHERE CustomerRefListId='30000-1166980720' AND (TxnDate>={d'2006-12-24'} AND TxnDate<={d'2007-01-23'})

But that will only get me the subtotal of invoices for the primary customer, but it doesn't get me the combined subtotal of all the invoices for each job under that customer.

Is there a way to combine it all into one SQL Statement? Or do I have to use multiple statements and then manually combine all the totals?

Solution:

You can use the query below to get the Subtotal of Invoices Within the Date Range using QODBC.

You can run this query:

SELECT Invoice.CustomerRefFullName, Sum(Invoice.SubTotal) AS SumOfSubTotal FROM Invoice WHERE (((Invoice.TxnDate)>={d'2007-01-01'})) and (((Invoice.TxnDate)<{d'2007-12-01'})) GROUP BY Invoice.CustomerRefFullName



You can change the greater than or equal (>=) and the less than (<) dates to the date range you wish to use.


If you want to get the SubTotal of all Invoices for Customer 2 and all the Customer 2 Jobs combined, I suggest you go straight to the QuickBooks reporting engine instead, which will add up and total things for you (including any credits). QuickBooks reports can be accessed using the QODBC stored report procedures:

sp_report SalesByCustomerSummary show Text as Customer, Label as Job, Amount_1 as Total parameters DateFrom = {d'2007-01-01'}, DateTo= {d'2007-12-01'}, SummarizeColumnsBy = 'TotalOnly'

Problem Description:

The InvoiceLineRate value passed in the insert query to create the InvoiceLine record is not being loaded into QBO.

Below are the two queries I am using to create the invoice.

Insert into "InvoiceLine" ("CustomerRefListID," "CustomerRefFullName," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineQuantity," "InvoiceLineRate," "InvoiceLineAmount," "FQSaveToCache") VALUES ('11', 'Alder Electric,' '19', 'Todd Gardner', 37.5, 42.50, 1593.75, 1)

Insert into "Invoice" ("CustomerRefListID," "CustomerRefFullName," "TxnDate," "RefNumber," "BillAddressAddr1", "BillAddressAddr2", "BillAddressAddr3", "BillAddressAddr4", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "TermsRefListID," "TermsRefFullName," "DueDate")VALUES ('11', 'Alder Electric,' {d'2015-12-06'}, '1388', 'Alder Electric,' 'Dannon Alder,' '605 N 1250 W', '', 'Centerville,' 'UT,' '84014', '5', 'Due upon Receipt,' {d'2015-12-21'}) 

Solution:

QODBC Online has the logic that if the user has provided Quantity, Rate & Amount, QODBC will send Quantity & Amount to QuickBooks.

So you need to send Quantity & Rate and remove Amount. The Amount will be auto-calculated.

For Example:

Insert into "InvoiceLine" ("CustomerRefListID," "CustomerRefFullName," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineQuantity," "InvoiceLineRate," "FQSaveToCache") VALUES ('11', 'Alder Electric,' '19', 'Todd Gardner', 37.5, 42.50, 1)

Insert into "Invoice" ("CustomerRefListID," "CustomerRefFullName," "TxnDate," "RefNumber," "BillAddressAddr1", "BillAddressAddr2", "BillAddressAddr3", "BillAddressAddr4", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "TermsRefListID," "TermsRefFullName," "DueDate")VALUES ('11', 'Alder Electric,' {d'2015-12-06'}, '1388', 'Alder Electric,' 'Dannon Alder,' '605 N 1250 W', '', 'Centerville,' 'UT,' '84014', '5', 'Due upon Receipt,' {d'2015-12-21'})

Tags: QuickBooks Online, QBO, QODBC Online, invoice line rate, Quantity, Rate, invoice line amount, invoice line

Problem Description:

I am getting the error "Invalid operand for the operator: <assignment>" while trying to insert an invoice line item:

INSERT INTO InvoiceLine ( CustomerRefListID, TemplateRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineRate, InvoiceLineAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache) VALUES ( '800019E1-1428688811', '8000001B-1360624407', '17003', '80000129-1362767268', 65.0000, '130.00', '80000002-1358615111', 0)

Solution:

You will get an Invalid operand for operator error when you provide the wrong data type values in your query.

For Example:

The column data type is decimal/integer/bit & you are inserting the value as a string/character.

In your query, the InvoiceLineAmount column data type is decimal & you provided the value as a string/character.

Please refer to the sample query & test again:

INSERT INTO InvoiceLine ( CustomerRefListID, TemplateRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineRate, InvoiceLineAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache) VALUES ( '800019E1-1428688811', '8000001B-1360624407', '17003', '80000129-1362767268', 65.0000, 130.00, '80000002-1358615111', 0)

Please refer to QODBC Data Layout for more details.

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 evaluating the product. When I made this SQL Statement:

Select * from Customer where ListId=557.

I was getting a column not found error. The QBO online documentation says the Customer table has a field called "Id," but this also comes back as "column not found."

Can you let me know if this unique ID column is being renamed or something?

Solution:

ListID column data type is varchar, so you need to add quotes (i.e.,') around it. Please use the query below & try again.

select * from Customer where ListId='557'

Regarding ID, I think you might have tried

select * from Customer where ID='557'

ID is not part of the QODBC-Online Schema. Please use the listed.

You can get the QODBC Online table schema from the link below:

Please refer to QODBC Online Data Layout for more details:

Tags: QuickBooks Online, QBO, QODBC Online, Customer

Problem Description:

I get a generic error when using QODBC to create an invoice.

I am using the following query:

INSERT INTO InvoiceLine ("TemplateRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineAmount," "InvoiceLineQuantity," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('80000011-1203804797', '80002C72-1345040417', 'My item 1',19.95, 1, '', 1)

INSERT INTO InvoiceLine ("TemplateRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineAmount," "InvoiceLineQuantity," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('80000011-1203804797', '80003194-1404606055', 'My item 2',22.50, 1, '', 1)

INSERT INTO Invoice ("TemplateRefListID," "CustomerRefListID," "CustomerRefFullName," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "ShipAddressAddr1", "ShipAddressAddr2", "ShipAddressCity," "ShipAddressState," "ShipAddressPostalCode," "SalesRepRefFullName," "ShipMethodRefListID") VALUES ('80000011-1203804797', '80000440-1448981709', 'FAKEEBAY,' 'Someone,' 'Address 1 ', 'The city,' 'State,' 'Zip,' 'Someone,' 'Address 1', 'City,' 'State,' 'Zip,' 'Rep,' '80000004-1202486852')

The error is:

[QODBC] Error: 3000 - The given object ID "in the field "list id" is invalid.

Solution:

You are trying to insert a blank value for the field "InvoiceLineSalesTaxCodeRefListID."

To resolve this issue, insert a valid value for the InvoiceLineSalesTaxCodeRefListID field or remove the InvoiceLineSalesTaxCodeRefListID field from your insert statement.

Remove the LISTED field from the query. Example:
INSERT INTO InvoiceLine ("TemplateRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineAmount," "InvoiceLineQuantity," "FQSaveToCache") VALUES ('80000011-1203804797', '80002C72-1345040417', 'My item 1',19.95, 1, 1)


Specify a value for LISTED field Example:
INSERT INTO InvoiceLine ("TemplateRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineAmount," "InvoiceLineQuantity," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('80000011-1203804797', '80002C72-1345040417', 'My item 1',19.95, 1, '90000011-1234567800', 1)


So please insert a valid value in the InvoiceLineSalesTaxCodeRefListID field or remove the InvoiceLineSalesTaxCodeRefListID field from your insert query.

How to insert employee time information from an MS Excel time spreadsheet into QuickBooks using QODBC

Solution:

You cannot perform insert/update operations directly in MS Excel because Excel is just exporting tables in the spreadsheet. Excel does not create link tables like MS Access. If you want to insert/update using MS Excel, then you need to use MS Query, which will execute the statement you enter in the editor, or you can write VBA code in Excel.

Please refer to the article for how to write SQL statements using MS Query: Microsoft Office with MS Query and QODBC.

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.

You can use ADO in VB to connect to QuickBooks via VBA in MS Excel.

Please refer to the sample code for inserting records through VBA:

Sub test()
Const adopt static = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "INSERT INTO TimeTracking (EntityRefListID, DurationMinutes, TxnDate, CustomerRefFullName, ItemServiceRefFullName, PayrollItemWageRefFullName) VALUES ( '370000-933272659', 480, {d'2007-12-18'}, 'Pretell Real Estate:75 Sunset Rd.', 'Removal,' 'Salary')."
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg
Set oRecordset = Nothing
Set oConnection = Nothing
End Sub

Please note: All query values are coming from sample company files. Please change it according to your company file value before using it.

Please click here to download a sample MS Excel file.

How can I add a blank InvoiceLine in QuickBooks using QODBC?

Solution:

You can create an empty Invoice, neither in QODBC nor QuickBooks. But in QuickBooks, you could leave lines blank and ensure at least one line with data in your invoice to create open line records.

You can create the same from QODBC by using the queries below. Please note you need to create one line of sufficient data. For the rest line, you can add a description & keep other fields blank.

When the line record is null, there will be no record for the newly created invoice, which is not allowed by QuickBooks.

INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('670000-1071517519', '71099', '320000-1071525597', 'POWERTRAK-2000', 200.00000, 200.00, '20000-999022286',1)

INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache) VALUES ('POWERTRAK-3000', 1)

INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache) VALUES ('POWERTRAK-4000', 0)

Problem Description:

I am trying to get our ODBC extension module interface working with QODBC. Everything seems to work fine except when trying to read SQL_BIT type fields. It causes the Script BASIC interface to error out.

I searched on Google about the issue, and others (Oracle, ...) have a similar problem. Do you have any way to translate the SQL_BIT type to a numeric or a CHAR(1)?

Solutions:

You can convert the Bit field to an integer using the query below.

For Example:

In the Customer table, the IsActive field is of the Bit datatype. We will convert it to a numeric/integer using the following query:

SELECT ListID, {fn CONVERT("IsActive", SQL_Integer)} AS "IsActive" FROM Customer

[Script BASIC Code]

IMPORT odbc. by

dbh = odbc::RealConnect("QuickBooks Data","","")

odbc::query(dbh,"SELECT FullName, AccountNumber, {fn CONVERT(\"IsActive \", SQL_Integer)} AS \"IsActive\", Balance FROM Account")

FOR x = 1 to 5

odbc::FetchHash(dbh,dbcol)

PRINT Left(dbcol{"FullName"} & STRING(50," "),50),"\t",dbcol{"AccountNumber"},"\t",dbcol{"IsActive"},"\t",FORMAT("% ~-###,###.00~",dbcol{"Balance"}),"\n" NEXT

odbc::Close(dbh)

Problem Description:

I had inserted it into the table ReceivePaymentLine, but it does not change the status to "Paid." How do I do it?

I use InvoiceLine to insert Item sales and ReceivePaymentLine for payment.

Solutions:

You can change the Invoice status to paid by receiving payment for that Invoice. Please refer to the sample query for receiving payment.

Insert into ReceivePaymentLine (CustomerRefListID, CustomerRefFullName, TxnDate, RefNumber, TotalAmount, PaymentMethodRefFullName, memo, DepositToAccountRefFullName, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) values ('YourCustomerRefListID,' 'YourCustomerRefFullName,' 'YourTxnDate,'' RefNumber which you want to use, YourTotalAmount, 'YourPaymentMethodRefFullName,' 'Yourmemo,' 'YourDepositToAccountRefFullName,' 'TxnID of Invoice,' 'Invoice Amount')

AppliedToTxnTxnID = TxnID of Invoice

AppliedToTxnPaymentAmount = Invoice Amount

For Example:

Insert into ReceivePaymentLine (CustomerRefListID, CustomerRefFullName, TxnDate, TotalAmount, PaymentMethodRefFullName, memo, DepositToAccountRefFullName, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) values ('1951', 'CSS Online,' {d'2013-02-05'}, '1044',40.00,' Cash,' 'Payment 2914', 'Undeposited Funds,' '3994',40.00)

Conversion ratio information is stored in a table called UnitOfMeasureSetRelatedUnit.

Problem:

How to get Conversion Ratio information from the UnitOfMeasureSetRelatedUnit Table in QODBC.

Solution:

We can get conversion ratio information by using an INNER JOIN on the SalesOrderLine Table.

We can get conversion ratio information by comparing UnitOfMeasureSetRelatedUnit.ListID and SalesOrderLine.ListID

Please refer to the below-mentioned query for conversion ratio information:

Select S.RefNumber, S.TxnDate, S.SalesOrderLineItemRefFullName, S.SalesOrderLineQuantity,
S.SalesOrderLineRate, U.UnitOfMeasureType, U.Name, U.BaseUnitName, U.RelatedUnitName,
U.RelatedUnitConversionRatio From UnitOfMeasureSetRelatedUnit U
INNER JOIN SalesOrderLine S ON S.SalesOrderLineOVerrideUOMSetRefListID = U.ListID

Problem Description:

We seem to have an issue with an insert into the InvoiceLines table, dropping a few transactions.

We need to create about 1000 invoices per month, with an average of about 6 - 7 lines per invoice. As QuickBooks cannot handle a set-based insert, we have used a cursor to insert the invoice lines and generate an invoice customer by customer (using the FQSaveToCache flag as the trigger. The code is as follows:

SET COUNT ON

DECLARE curOutput CURSOR FOR SELECT CustomerRefListID, TemplateRefListID, TxnDate, TermsRefListID, CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC
DECLARE @CustomerID VARCHAR(100)
DECLARE @TemplateID VARCHAR(100)
DECLARE @TransactionDate DATETIME
DECLARE @CustomerTermsID VARCHAR(100)
DECLARE @CustomerMsgID VARCHAR(100)
DECLARE @ItemID VARCHAR(100)
DECLARE @Quantity DECIMAL(18,2)
DECLARE @Rate DECIMAL(18,2)
DECLARE @Amount DECIMAL(18,2)
DECLARE @QBFlag INT
DECLARE @i INT
SET @i = 0

OPEN curOutput
FETCH NEXT
FROM curOutput

INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag; --Subroutine for QB insert
WHILE @@FETCH_STATUS = 0

BEGIN
INSERT INTO QREMOTE...INVOICELINE (CustomerRefListID, TemplateRefListID, TxnDate, TermsRefListID, CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache)
VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag )
SET @i = @i + 1
IF @QBFlag = 0 and @i > 500
BEGIN
WAITFOR DELAY '00:05:00.'
set @i = 0
END

PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.'
FETCH NEXT
FROM curOutput
INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ; END
--End subroutine for QB insert

CLOSE curOutput
DEALLOCATE curOutput

Our original code was identical to the above, except that it did not include the WAITFOR DELAY code triggered every time the FQSavetoCache flag is set to zero. The code works beautifully and has successfully created invoices in QuickBooks for hundreds of customers.

Adding the 10-second delay when the FQSavetoCache flag was set to zero would resolve the issue, but it has not. If the 10-second delay doesn't seem to work when the flag is zero, I could set the delay to wait for a second or two after each row of data. Unfortunately, we are only at about a 97 - 98% success rate in the accuracy of the invoices being generated, and the issue seems to be the inability of QuickBooks (or the QODBC driver) to keep up with the data being fed from our SQL Server table (which we have verified is 100% accurate).A

OLE DB provider "MSDASQL" for linked server "qremote" returned message "[QODBC] Not supported - At least one Line record is required. Please insert Child/Detail record(s) before inserting Parent/Header record. For more details, please visit:qodbc.com/links/2953".
Msg 7343, Level 16, State 2, Line 74
The OLE DB provider "MSDASQL" for linked server "qremote" could not INSERT INTO table "[MSDASQL]".

Do you have any suggestions? Please advise.

Solutions:

We did troubleshooting and found that SQL Server sends the Disconnect command every 5 minutes. The disconnect command is not expected from SQL Server when there is activity on the linked server. This is not a QODBC or QRemote issue.

[forums.sqlteam.com]SQL Server Linked Server to ODBC Connection Drops connection every 4-5 minutes

[social.technet.microsoft.com]SQL Server Linked Server to ODBC Connection Drops connection every 4-5 minutes

[forums.sqlteam.com]Is there a way to Disconnect the Linked Server connection - Not delete and re-add

[social.technet.microsoft.com]Is there a way to Disconnect the Linked Server connection - Not delete and re-add

There seems to be no option in SQL Server, which is why SQL Server is sending the Disconnect command.

We can't use MS Access as middleware because MS Access as a middleware has the same issue.

Workaround 1:

As a workaround, you can add some timing logic to the looping, like if the header insert is completed, if it is 4 minutes, it will sleep for 1 minute & 10 seconds. The sleep of 1 minute & 10 seconds will automatically send a disconnect command and resume after 1 minute. By doing so, you noticed no loss of invoice line. But yes, inserting time increased due to sleep.

Please use the script below:

SET NOCOUNT ON

DECLARE curOutput CURSOR FOR SELECT CustomerRefListID, TemplateRefListID, TxnDate, TermsRefListID, CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC
DECLARE @CustomerID VARCHAR(100)
DECLARE @TemplateID VARCHAR(100)
DECLARE @TransactionDate DATETIME
DECLARE @CustomerTermsID VARCHAR(100)
DECLARE @CustomerMsgID VARCHAR(100)
DECLARE @ItemID VARCHAR(100)
DECLARE @Quantity DECIMAL(18,2)
DECLARE @Rate DECIMAL(18,2)
DECLARE @Amount DECIMAL(18,2)
DECLARE @QBFlag INT
DECLARE @i INT
DECLARE @firstTime datetime
DECLARE @currentTime datetime
SET @i = 0
set @firstTime=GETDATE();
set @currentTime=GETDATE();

OPEN curOutput
FETCH NEXT
FROM curOutput
INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag;
--Subroutine for QB insert
WHILE @@FETCH_STATUS = 0

BEGIN
INSERT INTO QRemote...INVOICELINE (CustomerRefListID, TemplateRefListID,TxnDate, TermsRefListID,CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate,InvoiceLineAmount, FQSaveToCache)
VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag )

PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.'
set @currentTime=GETDATE();

IF @QBFlag = 0 and ((SELECT DATEDIFF(ss,@firstTime,@currentTime))> 240) --240=4 minutes. 270=4:30minutes

BEGIN
PRINT 'It is Sleep time ' + CAST(@QBFlag AS VARCHAR(12)) + ' for @firstTime : ' + CONVERT(nvarchar(90), @firstTime, 21) + ' @currentTime :' + CONVERT(nvarchar(90), @currentTime, 21) + ' ------. '
WAITFOR DELAY '00:01:10.'
set @firstTime=GETDATE();
set @currentTime=GETDATE();
PRINT 'Wake up time ' + CAST(@QBFlag AS VARCHAR(12)) + ' for @firstTime : ' + CONVERT(nvarchar(90), @firstTime, 21) + ' @currentTime :' + CONVERT(nvarchar(90), @currentTime, 21) + ' ------. '
END

FETCH NEXT
FROM curOutput
INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ;
END

--End subroutine for QB insert
CLOSE curOutput
DEALLOCATE curOutput

Workaround 2:

You can use another workaround as follows:

1) Insert an Invoice Line along with Invoice header fields with FQSaveToCache as 0. This will create an invoice in QuickBooks.

2) Get the Last Insert ID of the Invoice.

3) Using a loop for the rest of the invoice line, insert each invoice line to the existing Invoice by passing TxnID & with FQSaveToCache as 0 in the insert statement. This will update the current invoice and add a new line item.

As the above solution does not use FQSaveToCache, the SQL Server Disconnect issue will not affect it.

Please use the script below:

SET NOCOUNT ON

DECLARE curOutput CURSOR FOR SELECT CustomerRefListID, TemplateRefListID, TxnDate, TermsRefListID, CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC DECLARE @CustomerID VARCHAR(100)
DECLARE @TemplateID VARCHAR(100)
DECLARE @TransactionDate DATETIME
DECLARE @CustomerTermsID VARCHAR(100)
DECLARE @CustomerMsgID VARCHAR(100)
DECLARE @ItemID VARCHAR(100)
DECLARE @Quantity DECIMAL(18,2)
DECLARE @Rate DECIMAL(18,2)
DECLARE @Amount DECIMAL(18,2)
DECLARE @QBFlag INT
DECLARE @i INT
DECLARE @txnID VARCHAR(100)
SET @i = 0
OPEN curOutput
set @txnID =''
FETCH NEXT
FROM curOutput
INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag;
--Subroutine for QB insert
WHILE @@FETCH_STATUS = 0
BEGIN

if @txnID =''
begin
INSERT INTO QRemote...INVOICELINE (CustomerRefListID, TemplateRefListID,TxnDate, TermsRefListID,CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate,InvoiceLineAmount, FQSaveToCache) VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, 0 )
--Get the Last inserted item

Set @txnid= (select LastInsertId from openquery(QRemote,'sp_lastinsertid InvoiceLine'))

end

else

begin
INSERT INTO QRemote...INVOICELINE (txnID, CustomerRefListID,TemplateRefListID, TxnDate,TermsRefListID, CustomerMsgRefListID,InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate,InvoiceLineAmount, FQSaveToCache)
VALUES( @txnID,@CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, 0 )
end

if @QBFlag=0
begin
set @txnID =''
end

PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.' FETCH NEXT FROM curOutput INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ; END
--End subroutine for QB insert

CLOSE curOutput
DEALLOCATE curOutput

Note: The JDBC-ODBC bridge driver was removed in Java 8, preventing ODBC drivers from being usable in newer versions of Java or other Java-based applications.

Refer: Oracle JDBC-ODBC Bridge

How to Use QODBC with NetBeans

Problem Description:

QuickBooks Enterprise comes with a tool called QODBC that enables connecting to a QuickBooks database to read and write data. I have installed it and can successfully see QuickBooks data by connecting with Excel.

I am attempting to be able to read data in Talend Open Studio 5.4.1 by using the Generic ODBC connector. I can get a successful connection message when setting up the database connection, but retrieving the schema to see the tables shows no tables. There are no errors or any other indication - just no table listing.

Solutions:

Please query the table directly instead of loading tables. Use a tDBInput component, use a SELECT query, & Click on "Guess Schema."

For Example:

Select * from the customer

Problem Description:

We use a Visual Basic app with an SQL Server database to write data to QuickBooks through QODBC. We are executing two SQL statements as described in your help. We are getting an error saying either:

1) The transactions are not in balance - meaning it seems that both transactions are not being executed at the same time, or

2) If we try to combine the two text strings into one SQL server statement, that "missing semicolon at the end of SQL statement."

Solutions:

You are getting a transaction not in balance error because only one statement is executed. You should execute both statements one by one.

You cannot run both statements at the same time. You have to run them one after the other.

Also, the connection should not be closed between the executions of two queries. If the connection is closed, then you will face the error.

Please refer to the below-mentioned sample VBA code for executing two statements one after the other:

Public Sub exampleInsert()

Const adOpenStatic = 3

Const adLockOptimistic = 3

Dim oConnection

Dim oRecordset

Dim sMsg

DoCmd.RunSQL " INSERT INTO JournalEntryCreditLine(refnumber,journalcreditlineaccountreffullname,JournalCreditLineAmount,JournalCreditLineMemo,FQSaveToCache) Values ('1234','operating1',555.00,'test memo transfer cred',1)”

DoCmd.RunSQL " INSERT INTO JournalEntryDebitLine(ref number,JournalDebitLineAccountReffullname, JournalDebitLineAmount, JournalDebitLineMemo, FQSaveToCache) VALUES ('1234', 'operating2',555.00, 'Test Memo transfer deb,' 0)."

sMsg = sMsg & "Record Added!!!"

MsgBox sMsg

Set oRecordset = Nothing

Set oConnection = Nothing

End Sub

Instructions

      The most difficult part of using QRemote in programming is the connection string. The simplest form includes a reference to a DSN. A system DSN called "QuickBooks Data QRemote" for 32-bit applications & "QuickBooks Data 64-bit QRemote" for 64-bit applications is automatically created when QODBC is installed. "QuickBooks Data QRemote" will be used in all examples, but can be substituted for any different DSN name you make.

      Normally, ADO pools connections. QRemote does not support connection pooling. It is recommended to tell ADO not to do connection pooling on any QRemote connection. This is done with OLE DB Services=-2.

Parameters available on the connection string

IP Address = IP address of QRemote Server.

Port = IP address of QRemote Server.

EncryptionKey = Encryption Key settled in QRemote Server. (If you have set the Encryption Key in QRemote Server.) By default, it is blank.

RemoteDSN = Remote DSN which you want to use. By default, it is "QuickBooks Data."

 OLE DB Services=-2

Example of a simple DSN
32-bit:
sConnectString = "DSN=QuickBooks Data QRemote;OLE DB Services=-2;"

64-bit:
sConnectString = "DSN=QuickBooks Data 64-bit QRemote;OLE DB Services=-2;"

Example of a DSNless connection string
Without Encryption Key:
sConnectString = "Driver={QRemote for QuickBooks};OLE DB Services=-2;IPAddress=127.0.0.1;Port=4500;RemoteDSN=QuickBooks Data;"

With Encryption Key:(Replace 123456 with your Encryption Key value)
sConnectString = "Driver={QRemote for QuickBooks};OLE DB Services=-2;IPAddress=127.0.0.1;Port=4500;EncryptionKey=123456;RemoteDSN=QuickBooks Data;"

Standard syntax
"Driver={QRemote for QuickBooks};OLE DB Services=-2;IPAddress=127.0.0.1;Port=4500;"

Problem Description:

How to record the issue I am facing and show/share the screen.

Solutions:

Please follow the steps below for recording the issue you are facing.

1) Download screen recorder "TinyTake."

Click Here to Download TinyTake.

Download the software & install it.

2) Run TinyTake from the Start menu.



3) Click on Capture Video.



3) Please select the region you want to show & click on Start Recording.





4) Click on the stop to finish recording. You can click on pause to pause recording.



4) Click on the save icon to save the video locally. Click on Save & Share to upload the video to TinyTake's video sharing service.

5) Copy the TinyTake video link by clicking on the "Copy Link" button and share it with us in a support ticket.

Problem Description:

I have a question about how to locate data found in QuickBooks by table and field name inside the QODBC system.

Solutions:

The fastest way to find data in QuickBooks via the QODBC Driver is to use the batch and sync method. Inside QuickBooks, place a unique "bait" value in the field in question, such as find me for text fields or 123321 for numeric fields, and save the data. Then start the QODBC Test Tool and connect to the QuickBooks company file. Refer to Connecting QODBC Driver to QuickBooks for the First Time (Video), then do a global search for the "bait" value. The file and field name will be shown if the data is available via QODBC.

Please follow the steps below to locate QuickBooks data by table and field name inside the QODBC system.

1) Add a sample find me to Invoice or bill records (i.e., RefNumber).

For Example, we are creating an Invoice with RefNumber (i.e., Invoice#) findme.


2) Add a sample 123321 in the CreditMemo rate.

For Example, we are creating a CreditMemo with a rate of 123321.


3) Use the SP_GLOBALSEARCH command to search data from the QuickBooks company file. (Requires v16.0.0.317 or above)

sp_globalsearch SearchValue

For Example:

sp_globalsearch 'find me.'


sp_globalsearch '123321'

Problem Description:

How can I apply a refund check to an existing credit memo using QODBC?

In QuickBooks, I can refund by opening the Create Credit Memos/Refunds dialogue box using the QuickBooks UI and finding the credit memo for that customer. In the dialogue box icon bar, click on "Use the credit to give a refund."

Solutions:

You can use the CheckApplyCheckToTxn table and the Insert Only table to create a check to pay off a credit memo. You can give refunds to an existing credit memo by using the queries below:

INSERT INTO CheckExpenseLine (AccountRefListID, ExpenseLineAccountRefListID, ExpenseLineAmount, ExpenseLineMemo, ExpenseLineCustomerRefListID, ExpenseLineBillableStatus, FQSaveToCache) Values ('20000-933270541', '40000-933270541',80, 'Test Memo,' '800000E2-1513333899', null,1)



Insert into CheckApplyCheckToTxn ( AccountRefListID, PayeeEntityRefListID, TxnDate, ApplyCheckToTxnTxnID, ApplyCheckToTxnAmount) values ('20000-933270541', '800000E2-1513333899',{d'2017-12-18'},'1B3C0-1513338090',80)



After executing the above queries, you can see CreditMemo as Refunded in QuickBooks.



You can get ApplyCheckToTxnTxnID from the TxnID field of the CreditMemo table for that particular Transaction by using the below query.

SELECT TxnID, RefNumber FROM CreditMemo where RefNumber='1268'

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)

Note: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final  INSERT for the Invoice Header.

Create an Invoice with 3 Lines

     Here we create an invoice with three lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

First Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-2000',200.00000,200.00,'20000-999022286',1)

Note: QRemote is a linked server name. It would be best if you changed it to your linked server name.

Suppose you don't set up a linked server in SQL Server. Please refer to the link below for creating the linked server.

For 32-bit SQL Server: How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote

For 64-bit SQL Server: How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote

Second Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-3000',200.00000,200.00,'20000-999022286',1)

Third Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-4000',200.00000,200.00,'20000-999022286',1)

Header Information

INSERT INTO QRemote...InvoiceLine(CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES('160000-933272658', '40000-933270541', {d'2014-10-01'}, '1', 'Brad Lamb,' '1921 AppleseedLane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '20000-933272658', {d'2014-10-31'}, {d'2014-10-01'}, '2E0000-933272656', 'Memo Test,' 0,'10000-999022286')

Results in QuickBooks

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '240000-933272656' and found like this:

     Select ListId, and FullName from QRemote...An item where FullName is like 'F%.'

InvoiceLineSalesTaxCodeRefListID

InvoiceLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

     Select ListId, Name from QRemote...SalesTaxCode where Name like 'N%'

CustomerRefListID

CustomerRefListID is '160000-933272658' and found like this:

     Select ListId, and FullName from QRemote...Customers where FullName like 'Aber%'

ARAccountRefListID

ARAccountRefListID is '40000-933270541' and found like this:

     Select ListId, and FullName from QRemote...An account where a Name like 'Account%.'

ItemSalesTaxRefListID

ItemSalesTaxRefListID is '2E0000-933272656' and found like this:

     Select ListId, Name from QRemote...ItemSalesTax where Name like 'San%'

CustomerSalesTaxCodeRefListID

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

     Select ListId, Name from QRemote...SalesTaxCode where Name like 'T%'

Example of adding InvoiceLine items to an existing Invoice

You can add lines to existing Invoices using SQL Linked Server without any issue. Please refer to the sample query for the same:

Insert into QRemote.InvoiceLine (TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount) VALUES ('1B308-1513325705', '320000-1071525597', '11440DECA', 5,5)

OR

Insert openquery(QRemote, 'select TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount from InvoiceLine where TxnID=''0''') values ('1B308-1513325705','320000-1071525597', '88880DECA', 5,5)

Note: In the INSERT statement, a where should be TxnID=''0''. The TxnID=''0'' predicate is used to avoid retrieving data from the remote server, which can result in slower performance.

Problem Description:

How to get Bank Reconciliation details using QODBC

I've not successfully identified any field/flag identifying the bank reconciliation status. Is this information available through QODBC? Or has it not been made available?

I'm building a supplemental (to QuickBooks) bank account management/forecasting application. I want to be able to pull bank reconciliation data from QuickBooks (using QODBC) and be able to identify cleared/not cleared bank account transactions. This would be very useful for a client of mine. It's easy to remove all bank account transactions (debits and credits) from the Transaction table, which is very much like a bank statement if you order by date. But I've not been able to determine the CLEARED status of each transaction. Is there any way for me to determine this through QODBC?

Solutions:

The Bank Reconciliation details are unavailable through the Intuit SDK, so they are unavailable 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.

You can refer to the ClearedStatus table, which is used to clear checks using QODBC.

The "ClearedStatus" table is an insert-only table. It is used for insertion purposes only.

You can clear check using QODBC by inserting TxnId & check the status in the "ClearedStatus" table. If you want to clear check using QODBC, please refer to the steps below:

Please open the Check register in QuickBooks & locate the transaction in which you want to update the check status.

For Example, I want to update the check status of the Bill Payment:

To locate the TxnID of the "BILLPMT" check, I ran the following:

SELECT TxnID, AppliedToTxnSeqNo, TxnDate, PayeeEntityRefFullName, Amount FROM BillPaymentCheckLine where RefNumber='294'

Using this TxnID, I can mark the check as cleared by running the following using the INSERT only ClearedStatus table:

INSERT INTO ClearedStatus ("TxnID," "ClearedStatus") VALUES ('39D7-1071529831', 'Cleared')

BTW: The valid values are: |Cleared|NotCleared|Pending|

And the check is now marked as "Cleared" in QuickBooks.

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);

Problem Description:

Trying to insert into SalesOrderLine and getting a 'Multiple-step' error.

INSERT INTO QBREMOTE...SalesOrderLine (CustomerRefListID, TemplateRefListID, SalesOrderLineItemRefFullName, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineTaxCodeRefListID, ClassRefListID, FQSaveToCache) VALUES ('8000343B-1393961058', '80000008-1377787801 ', 'R1', 1.0000, 500.00, '80000001-1377787801 ', '80000003-1393947522', 0)

OLE DB provider "MSDASQL" for linked server "QBREMOTE" returned the message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "MSDASQL" for linked server "QBREMOTE" could not INSERT INTO table "[QBREMOTE]...[SalesOrderLine]".

Solutions:

You need to change the "Maximum Field Name Length" value from QODBC Setup Screen>> Advanced tab to resolve "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." at the time of insertion.

Please follow the instructions below to change the "Maximum Field Name Length" value.

Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" (If you are using System DSN) or "User DSN" (If you are using User DSN) Tab >> select your DSN>> click "configure">> Advanced >> "Maximum Field Name Length" change to 100.  

Also, you can use openquery, which can insert records in the SalesOrderLine table:

Insert openquery(QBREMOTE,' select CustomerRefListID, TemplateRefListID, SalesOrderLineItemRefFullName, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineTaxCodeRefListID, FQSaveToCache from SalesOrderLine where 1=0') values ('160000-933272658', '120000-1071512690', 'Wood Door: Exterior', 1.0000, 555.00, '10000-999022286', 0)

Please refer:

How to do INSERTs, UPDATEs, and DELETEs using OPENQUERY with Linked MS SQL Servers

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:

I have the invoice number (RefNumber) from my table outside QuickBooks. But if a QuickBooks user creates invoices directly in QuickBooks, then QuickBooks keeps new incrementing invoices automatically, and the next invoice I create using my outside table will be incorrect.

I need to replicate what QuickBooks does in determining the following QuickBooks invoice number. What do you recommend?

Solutions:

While inserting a new invoice from QODBC, if you do not pass RefNumber, QuickBooks will automatically manage and assign a RefNumber to the newly inserted invoice.

If you would like to specify the RefNumber, and the RefNumber is numeric, it should be easy to sync up. Just get the max RefNumber from the Invoice table( of the current year), add 1 to it, and specify it in the insert statement.

Problem Description:

How do I auto-increment the Ref Number for the Invoice?

Solutions:

While inserting a new invoice from QODBC, if you do not pass RefNumber, QuickBooks will automatically increment and assign a RefNumber to the newly inserted invoice.

For example, if the RefNumber of the last Invoice is 1111, then QuickBooks will automatically assign 1112 as a RefNumber for the newly created invoice.

Problem Description:

How do I get the max and insert the RefNumber for the Invoice?

Solutions:

You can get the max RefNumber for Invoice by using the below query & add 1 to the result, and can create a new invoice:

SELECT Max(RefNumber) FROM InvoiceLine  

We got 2581 as the max RefNumber. Now we are creating the new invoice with RefNumber 2582.

INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('620000-1071526513', '2582', '240000-933272656', 'POWER TRAK-2000', 200.00000, 200.00, '20000-999022286',0)

Problem Description:

What happens if I use the same RefNumber for Invoice creation?

Solutions:

QuickBooks is allowing duplicate RefNumber. So when you use the same RefNumber for Invoice creation. QuickBooks will create an invoice with a duplicate RefNumber/

For example, if the RefNumber of the last Invoice is 1111, then QuickBooks will automatically assign 1112 as a RefNumber for the newly created invoice.

Problem Description:

How do I Auto Increment Alphanumeric RefNumber for Invoice?

Solutions:

First, create an invoice from QuickBooks UI & assign the alphanumeric RefNumber "R0001T". Now create a new Invoice from QODBC & do not pass RefNumber. QuickBooks will automatically increment and assign a RefNumber (i.e., "R0002T") to the newly inserted invoice.

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 trying to modify the TxnDeleted table from QuickBooks in QODBC, but getting the "[QODBC] Not Supported" error. 

Solutions:

The TxnDeleted table is the read-only table. You can't modify/delete its content.

The insert/Update/delete feature in the TxnDeleted table is unavailable through the Intuit SDK, so they are unavailable 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:

I've linked all the QODBC tables into an Access 2010 DB. I have created table subset queries on the original tables and would like to do selects based on date ranges - other things, too, like JOINs - but I'm stuck with this problem. The minute I give Access a date constraint, like receive payment.TxnDate >=#11/1/2014# as stated in your "How Dates Are Formatted.." documentation for Access Date formats, Access immediately crashes. No msgs in the brief QODBC log files about the crashes, as best I can tell:

Solutions:

Please try executing the following command using QODBC Test Tool to see if it can solve the issue: sp_optimizefullsync ALL.

This command will bring some data to a local cache to increase query retrieval performance. I want to suggest that you please let me run the above command entirely. The above command may take some time, depending on your records in QuickBooks. It may take 2-24 hours or more, depending on the number of documents in the QuickBooks company file. Please let this command run entirely. Do not kill this command. It is not advisable to kill/close the application while building the Optimizer file.

After successfully executing the above command, please create a new MS Access Database file & link all tables & execute the query again. It should work without any issues.

Please refer to: How to Use QODBC with Microsoft Access 2010

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 1:

1. I'm attempting to utilize your ODBC driver for reporting through Excel from QuickBooks Enterprise. The tables in your report help guide aren't accessible to me and appear as part of the QBAdvancedReportGroup. An example is a v_account view. I couldn't find any other place to get a list of the chart of accounts. Is it possible to obtain permission to be part of that group? I have added every privilege to the ODBC setup in the application but did not see that group as an option.

Also, when opening QODBC Test Tool, I cannot use the sp_report procedure - I get the message "procedure: sp_report not found." Is that something related to the privileges? How can I get access to that procedure as outlined in your documentation?

2. I have a customer using QuickBooks Enterprise, and they are using the built-in ODBC in conjunction with MS Query and Excel to create a refreshable report.

Whenever they close & reopen QuickBooks, the query no longer works. This is because the DATABASE NAME in the "Connect to SQL Anywhere" screen changes each time QuickBooks is closed & reopened.

3. I have set up a connection from Access to QuickBooks. However, I can only open tables in the QBReportAdminGroup, not those in the QBAdminGroup. I get the following error:

ODBC--call failed

[Sybase][ODBC Driver][SQL Anywhere]Permission denied: you do not have permission to select from "v_sales_receipt_sn_list" (#-121).

The ODBC user I created has all groups assigned to it.

Also, I do not have permission to update the tables I do have access to, i.e., "QBReportAdminGroup_v_lst_item," view data.

Please advise how to resolve these problems.

Problem Description 2:

I am connected to QODC. When I run the QODBC Test Tool, it gives me an error [Sybase][ODBC driver]SQL Anywhere] Invalid user ID or password.

I'm entering the QuickBooks administrator user ID and administrator password. Other users and passwords also don't work.

Solutions:

You're using Intuit Custom Reporting for QuickBooks Enterprise and Not QODBC. I kindly request you remove any connection you use with Sybase SQL Anywhere and use QODBC.

It would be best if you connected to "QuickBooks Data" DSN (from system/machine DSN), not to any file DSN ( Do not try connecting to File DSN, which is next to your company file with the name COMPANYFILENAME.dsn). Please refer to the below articles for how to use QODBC:

Connecting QODBC Driver to QuickBooks for the First Time (Video)

Please note: you need to select "QuickBooks Data" (if the application is 32-bit) or "QuickBooks Data-64-bit QRemote" (if the application is 64-bit) when you choose a data source instead of selecting another data source.

Tags:

QBReportAdminGroup_v_txn_bill_line, v_lst_time_activity,v_lst,v_cf, v_txn_sales_receipt_line.transaction_date, v_lst_item.description, v_lst_item.name, v_cf_item.name, v_cf_item.field, v_txn_sales_receipt_line.item_id, v_txn_sales_receipt_line.unit_price_amt, v_txn_sales_receipt_line.amount_amt, v_txn_sales_receipt_line.quantity_qnty, v_txn_sales_receipt_line.doc_num_h, v_txn_sales_receipt_line.customer_id, v_lst_ship_to.state, v_lst_ship_to.postal_code,QBReportAdminGroup.v_cf_item v_cf_item, QBReportAdminGroup.v_lst_item v_lst_item, QBReportAdminGroup.v_lst_ship_to v_lst_ship_to, QBReportAdminGroup.v_txn_sales_receipt_line v_txn_sales_receipt_line, qbreportadmingroup_v_lst_account

We modified an invoice in QuickBooks and are trying to get the data out in Excel, but QODBC gives us the old data.

Why?

Solutions:

QODBC failure to return full records is usually caused by corruption of the QODBC optimizer file. I want to inform you that the Optimizer file may be corrupted if you are killing the process or query while the optimizer file is building. It is not advisable to kill a query while it is in execution.

Please reset the optimizer to solve the problem.

1) Close all other applications that use QODBC.

2) Turn off the QODBC optimizer (QODBC Setup Screen->" Optimizer" tab->Uncheck "Use optimizer" option)

3) Find the file with the .opt extension at the path you had assigned to the QODBC optimizer and delete the optimizer file.

(The default path is %AppData%\QODBC Driver for QuickBooks\Optimizer)

You can find the optimizer folder path from QODBC Setup Screen->" Optimizer" tab. Please copy this path & navigate to this path & delete the .opt file.

Or

You can directly delete the .opt file from:

Start>>All Programs>> QODBC Driver for use with QuickBooks>>QODBC Setup Screen>>" Optimizer" tab>>Click "Reset Optimizer File(s)."

4) Turn on the QODBC optimizer (QODBC Setup Screen->" Optimizer" tab->Check "Use optimizer" option)

5) After turning on the optimizer, please run the below command in QODBC Test Tool:

sp_optimizefullsync All

This command will bring some data to a local cache to increase query retrieval performance. I want to suggest you please let run the above command completely. The above command may take some time, depending on your QuickBooks records. It may take 2-24 hours or more, depending on the number of records in the QuickBooks company file. Please let this command run entirely. Do not kill this command. It is not advisable to kill/close the application while it's building the Optimizer file.

6) Please test again to see if the problem is still there.

7) After completing the above steps, please test data from QODBC Test Tool. If you can get updated data, then please refresh the Excel sheet so you can get updated data.

When inserting an invoice via a looping SQL procedure, I have invoice lines (InvoiceLine table) items that can potentially be assigned to varying classes. Using the ClassRefListID field, I class the lines accordingly and do not address the ClassRefListID in the main invoice (header/parent) table. When I examine the invoice in QuickBooks, it seems that ALL invoice line items have been forced to the SAME class (it looks like the class I last defined in the last child InvoiceLine item). I need to insert invoices with lines pointing to DIFFERENT courses, as I can manually do so in QuickBooks. Is this possible using QODBC????

Solution:

You are correct. When you create or add InvoiceLine, QODBC will always use the last class referenced in the invoice (if applicable). For example, this three-line invoice:

First Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "ClassRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', '60001-1019495266', 'QODBCClass1', '670004-1044572237', 'Building permit Additional Room,' 100.00000, 100.00, '10000-1011136881', 1)

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Second Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "ClassRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', '80001-1026828072', 'QODBCClass1', '670004-1044572237', 'Bin Permit Renovations,' 200.00000, 200.00, '10000-1011136881', 1)

Third Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', 'QODBCClass1', '670004-1044572237', 'Less Council Rebate,' -50.00000, -50.00, '10000-1011136881', 0)

Incorrectly appears in QuickBooks with all three invoice lines showing the "Manual Pool Systems" Class: 

So, you will need to use Plan B:

To get around this, you can first create the invoice and then add invoice lines and specify the class to use or not to use, like this:

INSERT INTO "InvoiceLine" ("CustomerRefListID," "ClassRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', '60001-1019495266', 'QODBCClass2', '670004-1044572237', 'Building permit Additional Room,' 100.00000, 100.00, '10000-1011136881', 0)

Once the invoice is created, we can locate the TxnID using the QODBC stored procedure (as long as we haven't broken our QODBC connection):

SP_LASTINSERTID InvoiceLine

The TxnID for the new invoice is 8D0E-1481798988, so we can now add additional lines to the invoice by doing the following:

INSERT INTO "InvoiceLine" ("TxnID," "ClassRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID") VALUES ('8D0E-1481798988', '80001-1026828072', '670004-1044572237', 'Bin Permit Renovations,' 200.00000, 200.00, '10000-1011136881')

INSERT INTO "InvoiceLine" ("TxnID," "ClassRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID") VALUES ('8D0E-1481798988', '', '670004-1044572237', 'Less Council Rebate,' -50.00000, -50.00, '10000-1011136881')

The invoice now appears in QuickBooks with different Class values for each line:

Problem Description 1:

I am running ProfitAndLossBudgetPerformance through MS Query, & It is returning no rows. But using QuickBooks UI, I can get records from the ProfitAndLossBudgetPerformance report. I am executing the following query:

Sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d'2013-01-01'}, DateTo={d'2013-02-20'}, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'

There is no error pop-up occurring. From the QODBC logs, I noticed the following error:

2014-06-18 19:39:03 QODBC Ver: 14.00.00.302 *********************************************************************************************************************
IsAService: False
SQL Statement: Sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d'2013-01-01'}, DateTo={d'2013-02-20'}, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'
Error Getting XML in BuildAndLoadXMLForNextRecord
Input XML:

<?xml version="1.0" encoding="ISO-8859-1"?>

<?qbxml version="11.0"?>

<QBXML>

<QBXMLMsgsRq onError=" continue on error">

<BudgetSummaryReportQueryRq requestID="1">

<BudgetSummaryReportType>ProfitAndLossBudgetPerformance</BudgetSummaryReportType>

<BudgetCriterion>AccountsAndClasses</BudgetCriterion>

<ReportPeriod>

<FromReportDate>2013-01-01</FromReportDate>

<ToReportDate>2013-02-20</ToReportDate>

</ReportPeriod>

<SummarizeBudgetColumnsBy>Date</SummarizeBudgetColumnsBy>

<SummarizeBudgetRowsBy>Class</SummarizeBudgetRowsBy>

</BudgetSummaryReportQueryRq>

</QBXMLMsgsRq>

</QBXML>

Problem Description 2:

Is there any way to have a date prompt in this stored procedure instead of a fixed fiscal year? I am running the query in Excel using Microsoft Query. It appears to give me information regarding the current month (i.e., February info since this is the month of February) for the fiscal year identified in the parameters. Is there any way I can replace FiscalYear with a FromDate and ToDate parameter?

Solutions:

As per QuickBooks SDK, the parameter "fiscal year" is required in the ProfitAndLossBudgetPerformance report. So, the parameter "fiscal year" is missing in the SQL statement. After including the required parameter, you can get all records from the ProfitAndLossBudgetPerformance report. Please refer to the query below:

sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d '2013-01-01'}, DateTo={d '2013-02-20'},FiscalYear=2013, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'

Please refer to the article mentioned. You can get detailed information about how to execute sp_report in QODBC:

How to use the QuickBooks Reporting Engine with QODBC

Problem Description 1:

Is there a way to see the SQL QuickBooks uses to generate its reports?
Also, which tool do they use to create reports?

Problem Description 2:

I need to access information used in the QuickBooks audit trail report. Do you have the table name, the field names, and where the table is located?

Problem Description 3:

Do we have access to see the code in the QODBC Stored Procedures?

Problem Description 4:

In the future, what reference can I use to connect the QuickBooks List and Transaction tables to the standard SQL SELECT and FROM statements? How can I know the table and field names used in the report, Trial Balance? For example, sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth'. I want to run my SQL in MS Access, not just sp_reports.For the future 

Solutions:

QuickBooks SDK does provide output of the report, but does not provide information on how that data is derived or which tables are used in those reports.
QuickBooks SDK does not expose information regarding how the report is built, the tables used, or the logic. Thus, it is not available in QODBC.
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.

For 64-bit, please refer to: How to Configure QODBC to Work With 64-bit MS Access.

Problem Description:

Everything worked perfectly with the installation and linking to MS Access 2010 and QuickBooks Pro 11. The tables I needed were related to my database in Access. However, how do I update (or can I update) QuickBooks using a form (linked to my tables downloaded to Access Form using QODBC )in MS Access?

Solution:

You can insert/update/delete records by accessing the local table. You need to write a VBA script to perform the SQL operation, or can execute existing queries, or can link the form to the existing table.

Please refer to the steps below steps which contain a sample tutorial on how QODBC can work with VBA. The tutorial is for sample reference only. For further requirements, you need to develop a form or write scripts that will fetch data from a local access table and perform the insert operation in QuickBooks using QODBC.

Creating a Form in Microsoft Access:

Note: Microsoft Access is a product with many uses and will allow you to use your QuickBooks Data files in the same fashion as Access databases.

Open MS Access. You can find it via Windows Start->All Programs->Microsoft Office:

A default database file, nnamedDatabase1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. acid. Save the file as type "Microsoft Office Access Databases."

Download Sample

Then, click "Create" to create a blank database:

After  the new database is created, create a New Form via the "Form Design" link in the Create Tab:

After  the new form is created, insert a button in the form:

Rename the button as per your requirement (In this example, the button name is "Select Customer"):

Now go to the button property window and set the property for the "On Click" event. Select "Event Procedure" from the dropdown list, then click on the "..." button:

The new window is open, which contains VBA code (In this example, it contains a code button click event):

Now, insert the module in the form by right-clicking on the form:

Write code in the module (In this example, I am writing code for the selection of the customer name):

Link the newly created module with the button click event:

Now click on the "Select Customer" button, and you will get the names of all customers:

Repeating the above steps can add functionality to your VBA code as per your requirement. In this example, I have added functionality for Insert, Update & Delete of customer records:

By clicking on the "Insert Customer" button, the New customer is added with the name "Testing VB":

By clicking on the "Update Customer" button, the newly added customer name is updated with the name "Updated Testing VB":

By clicking on the "Delete Customer" button, the newly added customer is deleted:

Please refer to the code below code which is used in this example:

Select Customer:

Public Sub example select()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.CursorLocation = adUseClient
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(100)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(100)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************"
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Insert Customer:

Public Sub exampleInsert()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "Insert into customer (Name) values ('Testing VB')"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.CursorLocation = adUseClient
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg

Set oRecordset = Nothing
Set oConnection = Nothing
End Sub

Update Customer:

Public Sub exampleUpdate()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "Update customer set Name='Updated Testing VB' where name='Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.CursorLocation = adUseClient
oConnection.Open sConnectString
oConnection.Execute (sSQL)

sMsg = sMsg & "Record Updated!!!"
MsgBox sMsg

Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Delete Customer:

Public Sub exampleDelete()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "delete from customer where name='Updated Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.CursorLocation = adUseClient
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Deleted!!!"
MsgBox sMsg

Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Download Sample

Also, Refer:
How to Use QODBC with Microsoft Access 2007
Using QuickBooks Data with Access 2010 (Import Tables) 32-bit
Using QuickBooks Data with Access 2010(Linked Tables) 32-bit
How to Configure QODBC to Work With 64-bit MS Access
Microsoft Access 2003 and QODBC

How can I use the Case When statement query in QODBC

Solution:

You can use the Case When statement query in QODBC as follows:

The Simple CASE Statement is only used in the SELECT Statement to check whether the value is equal.

Below is an example:

SELECT ListID, Active = CASE IsActive WHEN 1 THEN 'Active' WHEN 0 THEN 'NotActive' ELSE 'Unknown' END FROM Vendor

An example of a Case When with an IN Statement:

You can use an IN Statement in Case When as below.

For Example:

SELECT ListID, Balance, CASE WHEN Balance IN (11026.45,57938.60) THEN 'HIGH' WHEN Balance IN (2770.50,7125.50) THEN 'MED' WHEN Balance IN (0.45) THEN 'LOW' ELSE '--NA--' END FROM Vendor

When inserting multiple records into a line item table, you can use column FQSaveToCache.

Set column FQSaveToCache to 1 in all insert queries except the last line, set to 0. But the table TimeTracking is not a line item table; there is no FQSaveToCache column in that table.

So you need to use the alternate method. This article will show you how to use the sp_batch command to insert multiple records into TimeTracking.

If you are unfamiliar with the sp_batch statement, please refer to This article for more information.

First, please run the following:

SP_BATCHCLEAR Time tracking

This command will remove all cached data from the TimeTracking table. It will make sure only the queries below will be executed.

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Then run this query to start the batch:

SP_BATCHSTART Time tracking

When this query is executed, all queries later will be stored in the cache instead of being sent directly to QuickBooks.

Then run your insert queries. Here we use our test company file as an example:

INSERT INTO TimeTracking (EntityRefListID, DurationMinutes, TxnDate, CustomerRefFullName, ItemServiceRefFullName, PayrollItemWageRefFullName) Values ('60001-1039046236',540, {d'2022-01-06'},‘' Kim: Garland'’, 'Installation Labor,' 'Sick -Salary')

INSERT INTO TimeTracking (EntityRefListID, DurationMinutes, TxnDate, CustomerRefFullName, ItemServiceRefFullName, PayrollItemWageRefFullName) Values ('F0002-1039046343',600, {d'2022-01-06'}, ‘'West: Test'’, 'Installation Labor,' 'Sick -Salary')

After all insert queries are executed, upload all cached queries to QuickBooks using this command:

SP_BATCHUPDATE Time tracking

This query will upload all queries in the cache to QuickBooks.

How to insert an InvoiceLine item into an existing Invoice.

I am trying to run an INSERT statement that I have run before on version 10, but I am now getting the following error.

SQL Statement: INSERT INTO InvoiceLine (TxnID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc) VALUES ('56989-1362006634', '6529', '80000271-1266353613', 'Shipped On: 28/55/2013 USPS Tracking#: LC123456789012345678US')
System.Data.Odbc.OdbcException: ERROR [00000] [QODBC] Error parsing complete XML return string. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean need reader, Object[] method arguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()

Solution:

You have added the field TxnID to the insert query. The field "TxnID" is available in the insert query only when the TxnID value points to an existing entity in QuickBooks.

If you want to add a line item record to an existing invoice, then please remove header information fields like "RefNumber" from your query.

Header information fields (CustomerRefListID, RefNumber, ShipAddress, ClassRefListID, and so on) cannot be added to the query when you try to add a line record to an existing entity. This will cause an XML error.

Here is an example of adding one line item to an existing invoice, "18E8A-1481798951," in our test company file:

INSERT INTO InvoiceLine (TxnID, InvoiceLineItemRefFullname, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount) VALUES ('18E8A-1481798951', 'test item 2', '11440DECA', 0.01,0.01)

Please notice that all values in the query come from our test company file. Please replace them using your value when running it on your end. And you can also add other line item columns to this query according to your request.

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

Initially, I received the error "[QODBC] Field not allowed in insert" when I tried to insert data into the Customer table through QODBC. I was trying to insert customer data (Name, AccountNumber, Balance, credit limit). But QODBC does not allow me to insert data. When I attempted to insert received the following error:

Solutions

     Use the "OpenBalance" field instead of the "Balance" field, as QuickBooks SDK does not allow inserting a value in the "Balance" field but uses "OpenBalance." After inserting the value in the "OpenBalance" field, the value is moved to the "Balance" field.

     Sample Query:

Insert into Customer
(AccountNumber,Name,BillAddressAddr1,BillAddressAddr2,BillAddressCity,BillAddressState,BillAddressPostalCode,BillAddressCountry,Phone,Fax,Email,Contact,Notes,SalesRepRefFullName,CustomerTypeRefFullName,TermsRefFullName,CreditLimit,OpenBalance,IsActive)
Values ('ABC-111', 'XYZ', 'DEF St. ', '', 'Minneapolis', 'MN', '55401', '', '999 999-9999', '', 'temp@xyz.com', 'XYZ', 'great guy', 'XYZ', '', 'COD', 0, 0, 1) 

     Note: Sample query contains dummy data. You can add/remove fields from the question as per your requirements. Ref fields are subject to change as per your company file.

I found this, but I am still getting an error "Invalid operand for operator <assignment>

Solution 

Please try this query in the QODBC Support Wizard to see if it can get your jobs done:

UPDATE Invoice SET DueDate = {fn CONVERT( {fn TIMESTAMPADD(SQL_TSI_DAY,30, DueDate )}, SQL_DATE)} WHERE CustomerRefListID = '80000005-XXXXXXXXX'

And if you are going to do this in an MS Access linked table, please create a query and use a SQL Statement like this:

UPDATE Invoice SET DueDate = DateAdd("d", 30, TxnDate) WHERE CustomerRefListID = '80000005-XXXXXXXXX'

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. 

For a Single Execution, follow the steps below.

Click on "Start" and "Programs" and locate QODBC Driver for the QuickBooks program group. Click on the QODBC Test Tool icon to launch the program supplied with QODBC.

To establish a connection, select "QuickBooks Data" -> DSN from the dropdown list and click on "Connect."