Solved

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

Posted on 2016-10-29
7
71 Views
Last Modified: 2016-10-29
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.
0
Comment
Question by:Member_2_7966101
  • 3
  • 2
7 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 250 total points (awarded by participants)
ID: 41865478
HI,

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

Open in new window

Regards
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 250 total points (awarded by participants)
ID: 41865479
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

0
 

Author Comment

by:Member_2_7966101
ID: 41865480
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.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41865485
Hi,

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

Regards
0
 

Author Comment

by:Member_2_7966101
ID: 41865488
For some reason, when I logged in, I only saw Qlemo's response. Sorry for the error.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41865489
@Qlemo
Many Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question