Knowledgebase: TroubleShooting
[QODBC-Desktop] Troubleshooting - Update Query Truncating Leading Spaces
Posted by Jack - QODBC Support on 11 January 2016 10:18 AM

Troubleshooting - Update Query Truncating Leading Spaces

Problem Description:

I have to update a field with specific data; for some records, one of the fields will have leading spaces that are required to be there. I have tried numerous ways to get these spaces into the data field, but nothing has worked.

Pick any record: Update Customer Set ResaleNumber = ' 45' WHERE AccountNumber = '98765'

SELECT {fn LEFT(ReslaeNumber,1)} AS FirstChar FROM Customer WHERE AccountNumber = '98765' >>> FirstChar = 4 <

I've tried (that didn't work)

Update Customer Set ResaleNumber = '***45' WHERE AccountNumber = '98765'

Update Customer Set ResaleNumber = {fn REPLACE(ResaleNumber, '*', ' ')} WHERE AccountNumber = '98765' >>> FirstChar = 4

Update Customer Set ResaleNumber = {fn SPACE(3)} + '45' WHERE AccountNumber = '98765' >>> FirstChar = 4

If I do the manual data entry into the field ('45'), the leading spaces remain in the area, as they should. But I have to update 10K+ records. How can I get around this issue? What do you suggest?
 

Solution:

QuickBooks SDK automatically removes leading & trailing whitespace when it performs the update operation. QuickBooks SDK does not allow white space.

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 could not do it either.

For further investigation, you can use the SDKTestPlus3 tool. SDKTestPlus3 is a test tool from Intuit. You can download it from Intuit's website.

SDKTestPlus3 does not use QODBC. SDKTestPlus3 directly uses the QuickBooks SDK to connect to QuickBooks.

If the QuickBooks SDK or QuickBooks is having issues, SDKTestPlus3 will also fail.

Please refer: To how to use SDKTestPlus3 for Advanced Troubleshooting.

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).