查询表达式问题中缺少运算符 [英] missing operator in query expression problem

问题描述

大家好，

i刚刚在我的vb.net项目中遇到我的sql语句问题。你检查我的陈述吗？

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

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)

推荐答案

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)

问题显然是在括号中封装表名和列名的方式;正如您所看到的，正确的语法是[table]。[column]，而不是[table.column]（至少在SQL Server中，但您没有标记您正在使用的数据库系统）。



注意：通过像你一样连接文本字段来构造SQL语句是一个非常糟糕的习惯，因为这会使你的代码为SQL注入攻击而打开。您应该使用参数化查询（请参阅为参数化查询准备的更正示例）：

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):

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)

这篇关于查询表达式问题中缺少运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！