Solved

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

Posted on 2016-10-29
7
110 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 51

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 70

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 51

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 51

Expert Comment

by:Rgonzo1971
ID: 41865489
@Qlemo
Many Thanks
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

724 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