[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2016-10-29
7
Medium Priority
?
126 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 1000 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 71

Assisted Solution

by:Qlemo
Qlemo earned 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 53

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 53

Expert Comment

by:Rgonzo1971
ID: 41865489
@Qlemo
Many Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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‚Ķ

650 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