VB6 SQL query problem

I am using VB to get data from a Microsoft SQL database. I used Microsoft Query to generate a SQL statement. The query ran perfectly in Microsoft Query. But when I used it in my application it returned this error message.
Run-time error '-2147217900 (80040e14)': [Microsoft][ODBC SQL Server] Invalid column name 'C'.

I have tried other queries which work correctly. It seems to be the last bit that causes the problem
AND ((bmwohm.status <> ""C""))"
---Microsoft Query---
SELECT rl_lot.works_order, rl_lot.line_no, rl_lot.lot, rl_lot.expiry, bmwohm.status
FROM cs3live.scheme.bmwohm bmwohm, cs3live.scheme.rl_lot rl_lot
WHERE bmwohm.works_order = rl_lot.works_order AND ((bmwohm.status<>"C"))
 
---Code used in VB---
SQL = "SELECT rl_lot.works_order, rl_lot.line_no, rl_lot.lot, rl_lot.expiry " & _
                "FROM cs3live.scheme.bmwohm bmwohm, cs3live.scheme.rl_lot rl_lot " & _
                "WHERE bmwohm.works_order = rl_lot.works_order AND ((bmwohm.status <> ""C""))"

Open in new window

LVL 1
Randox_LaboratoriesAsked:
Who is Participating?
 
TimCotteeHead of Software ServicesCommented:
Hello Randox_Laboratories,

Because you either need to include "Set QUOTED_IDENTIFIER ON" in your statement or use single quotes instead (the latter is easier!)

   SQL = "SELECT rl_lot.works_order, rl_lot.line_no, rl_lot.lot, rl_lot.expiry " & _
                   "FROM cs3live.scheme.bmwohm bmwohm, cs3live.scheme.rl_lot rl_lot " & _
                   "WHERE bmwohm.works_order = rl_lot.works_order AND ((bmwohm.status <> 'C'))"

Regards,

TimCottee
0
 
Randox_LaboratoriesAuthor Commented:
Thanks, that worked great.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.