missing operator in query expression problem

Question

1.00/5 (1 vote)

See more:

Hi all,
i just faced a problem with my sql statement in my vb.net project.can you check my statement?

VB

cmd = New OleDbCommand("SELECT [sub_pay_item_quantity.quantity],[sub_pay_item_unit_rate.rate] FROM sub_pay_item_quantity,sub_pay_item_unit_rate WHERE sub_pay_item_quantity.sub item=sub_pay_item_unit_rate.sub item and                        
[sub_pay_item_quantity.sub item]='" & subItem & "' and [sub_pay_item_quantity.bridge type]='" & bridgeType & "' and [sub_pay_item_quantity.span]='" & span & "'", conn)

Posted 30-Oct-13 4:40am

Nebilo

Add a Solution

Comments

Richard C Bishop 30-Oct-13 10:55am

You cannot do a Select statement like that. If you want to query multiple tables, you must join them in some fashion.

Kschuler 30-Oct-13 17:13pm

It's possible to do this with some DBs...just not good practice. If you separate the file names with a comma in the FROM I believe it assumes an inner join. You'll need to put the conditions that would have gone in the "ON" in the WHERE clause instead. It's a much better practice to specifically state the JOIN. The code will be much easier to understand and easier to maintain and/or change or later.

Richard C Bishop 30-Oct-13 17:16pm

Very well, thank you for that information.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2013-10-30T05:26:00

VB

cmd = New OleDbCommand("SELECT [sub_pay_item_quantity].[quantity], [sub_pay_item_unit_rate].[rate] FROM [sub_pay_item_quantity], [sub_pay_item_unit_rate] WHERE [sub_pay_item_quantity].[sub item]=[sub_pay_item_unit_rate].[sub item] AND [sub_pay_item_quantity].[sub item]='" & subItem & "' AND [sub_pay_item_quantity].[bridge type]='" & bridgeType & "' AND [sub_pay_item_quantity].[span]='" & span & "'", conn)

The problem obviously is in the way you encapsulate table and column names between brackets ; as you can see, the right syntax is [table].[column], not [table.column] (at least with SQL Server, but you did not tag which database system you are using).

Note: constructing a SQL statement by concatenating text fields like you do is a very bad habit, because this leaves your code opened for SQL injection attacks. You should use parameterized queries instead (see corrected example prepared for parameterized query):

VB

cmd = New OleDbCommand("SELECT [sub_pay_item_quantity].[quantity], [sub_pay_item_unit_rate].[rate] FROM [sub_pay_item_quantity], [sub_pay_item_unit_rate] WHERE [sub_pay_item_quantity].[sub item]=[sub_pay_item_unit_rate].[sub item] AND [sub_pay_item_quantity].[sub item]=@subItem AND [sub_pay_item_quantity].[bridge type]=@bridgeType AND [sub_pay_item_quantity].[span]=@span", conn)