Solved

VB6 SQL query problem

Posted on 2008-06-11
2
1,127 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:Randox_Laboratories
2 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 21758211
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
 
LVL 1

Author Closing Comment

by:Randox_Laboratories
ID: 31466077
Thanks, that worked great.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now