Strings no problem but cannot enter numeric variable into SQL Select statement

Member_2_7966101
Member_2_7966101 used Ask the Experts™
on
Hello,

I can successfully transfer data from Excel to Quickbooks (via QODBC) as long as my data types are strings as per this example:

sSQL = "INSERT INTO Customer (Name, ParentRefListId, AccountNumber) VALUES ('" & CustomerNameString & "', '" & ParentRefString & "', '" & JobNumberString & "')"

Or I can mix strings and physical numbers such as in this example:

sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc,  PurchaseOrderLineQuantity) VALUES ('" & PurchaseDescription & "', 12)"

However, assigning an integer variable to replace the physical number using this technique doesn't work; my variable is interpreted as a column header:

Dim PurchaseDescription as String
Dim PurchaseQty as Integer


sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc,  PurchaseOrderLineQuantity) VALUES ('" & PurchaseDescription & "', PurchaseQty)"

This is the error I get:

Variable interpreted as column header
If I try enclosing my integer variable in single or double quotation marks, I get a data mismatch error

sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc,  PurchaseOrderLineQuantity) VALUES ('" & PurchaseDescription & "', 'PurchaseQty')"

Data Mismatch


I have been at this hours and hours and I'm really frustrated. I'm sure my syntax is the issue. Is there anyone familiar with how to mix strings and integers in this type of a statement? Thank you so much for any help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
HI,

pls try
sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc,  PurchaseOrderLineQuantity) VALUES ('" & PurchaseDescription & "', " & PurchaseQty & ")"

Open in new window

Regards
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Your issue is that you need to insert the numeric value represented by the var as a string element of the SQL, but not as a string literal in SQL. Or in short:
sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc,  PurchaseOrderLineQuantity) VALUES ('" & PurchaseDescription & "', " & PurchaseQty & ")"

Open in new window

Author

Commented:
Good lord, that's depressing... It works perfectly. I did learn a lot of ancillary things as I was trying to figure this out. Thank you so much for your prompt and accurate reply.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Top Expert 2016

Commented:
Hi,

Why does my answer does not get points since they are identical?

Regards

Author

Commented:
For some reason, when I logged in, I only saw Qlemo's response. Sorry for the error.
Top Expert 2016

Commented:
@Qlemo
Many Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial