Solved

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

Posted on 2016-10-29
7
52 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 68

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
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.

 
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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now