CODEWITHSUNDEEP
sql-servervbaexcel
Douglas Sussan Campbell
Douglas Sussan Campbell

Reputation: 35

error converting data type varchar to int - Excel VBA SQL

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!

Upvotes: 1

Views: 1480

Answers (1)

Brian Pressler
Brian Pressler

Reputation: 6713

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.

Upvotes: 4

Related Questions