error converting data type varchar to int - Excel VBA SQL

Question

I am trying to pull zipcodes from a Microsoft SQL Database and everything works good with US Zipcodes, but once I start to use Canadian Zip Codes, I get the following message:

conversion failed when converting the varchar value 'L0S1J0' to data type int.

I know that L0S1J0 is not a integer, but what I don't understand is why Excel VBA wants to pull the data as an integer. I tried to search for pulling data in ways other than int, but I am not having any luck with the right keywords. I have tried things like Cast and Convert, but I cant get anything to work, other than removing the Canadian Zip Codes out of the database.

Here is my query that I am currently running and the Zip Codes are stored under the LookupID field.

Select Distance1, Distance2, LookupID FROM excel.dbo.miles WHERE LookupID =" & Worksheets("sheet1").Range("C2").Value

Any help in this is greatly appreciated!

This still gave me the error message. Thanks for your assistance however :-) — Douglas Sussan Campbell
– Douglas Sussan Campbell, Commented Dec 17, 2015 at 18:01
I had noticed the missing quote marks but I expected you to see a different error though. Glad you got it fixed. — shawnt00
– shawnt00, Commented Dec 17, 2015 at 18:03
Just for reference when I try a similar query I get: Invalid column name 'L0S1J0'. Who knows. — shawnt00
– shawnt00, Commented Dec 17, 2015 at 18:40

Brian Pressler · Accepted Answer · 2015-12-17 16:25:40Z

4

Your problem is in your where clause. Change to:

WHERE LookupID ='" & Worksheets("sheet1").Range("C2").Value & "'"

You need to put single quotes around string literals.

You should consider doing a parameterized query to protect from SQL Injection issues. For example with ADO use the CreateParameter method.

answered Dec 17, 2015 at 16:25

Brian Pressler

6,7232 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Douglas Sussan Campbell Over a year ago

Pefect! Thank you for your time. Works like a charm now :-D

Collectives™ on Stack Overflow

error converting data type varchar to int - Excel VBA SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related