具有多个条件和Like值的SQL语句 [英] SQL Statement with multiple conditions and Like values

问题描述

问候！



我正在编写一个VB.NET程序，它将以编程方式连接到SQL数据库。



我有几个SQL语句可以正常工作，但是，一个SQL语句似乎不起作用....



我有2个表一个数据库。



表1

- employeeID

- employeName



表2

- employeeID

- employeeBatchNumber

- employeezipcode

-employeephonenumber

-employeedeleted



我需要做的是根据组合框（员工姓名）中的文本值进行搜索，但是，我需要匹配employeeID''一起得到Table2数据..





我现在拥有的是：



（选择Table2.employeeBatchNumber，Table2.employeezipcode，Table2.employeephonenumber，Table1.employeeName，Table1.employeeID来自Table1，Table2，其中Table2.employeedeleted =''false''和Table2.employeeName like％&ComboBox1。文本&％''和Table1.employeeID = Table2.employee），con）



这将填充带有结果的数据网格视图，但是，它不会....



任何人都可以查看我的SQL语句并告诉我是否你看错了什么???



感谢先进



daveofgv

Greetings!

I am writing a VB.NET program that will be connecting to a SQL database programmically.

I have several SQL Statements that work just fine, however, one SQL Statements does not seem to work....

I have 2 tables in a database.

Table1
- employeeID
- employeName

Table2
- employeeID
- employeeBatchNumber
- employeezipcode
-employeephonenumber
-employeedeleted

What I need to do is search based on a text value from a combobox (employee name), however, I need to match employeeID''s together to get the Table2 data..


What I have now is:

("Select Table2.employeeBatchNumber, Table2.employeezipcode, Table2.employeephonenumber, Table1.employeeName, Table1.employeeID from Table1, Table2 where Table2.employeedeleted = ''false'' and Table2.employeeName like %" & ComboBox1.Text & "%'' and Table1.employeeID = Table2.employee)", con)

this will fill a datagrid view with the results, however, it does not....

Can anyone look over my SQL Statement and let me know if you see anything wrong???

thanks in advanced

daveofgv

推荐答案

您需要使用加入。



试试这个：

You need to use a join.

Try this:

SELECT t2.employeeBatchNumber, t2.employeezipcode, t2.employeephonenumber, t1.employeeName,    t1.employeeID
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.employeeID = t2.employeeID
WHERE t2.employeedeleted = 'false' AND t2.employeeName LIKE @textBox 

然后使用SqlCommand对象并添加参数。您需要查看SQL注入，因为现在您的代码，有人知道SQL可以消灭所有表。这是非常不安全的。

Then use a SqlCommand object and add the parameter. You need to look into SQL injections because as your code is now, someone how knows SQL could wipe out all your tables. It is very insecure.

string EmpName = ComboBox1.Text;

SqlCommand cmd = new SqlCommand("Select T2.employeeBatchNumber, T2.employeezipcode, T2.employeephonenumber, T1.employeeName, T1.employeeID FROM Table1 T1 INNER JOIN Table2 T2 ON T2.employeeID = T1.employeedID WHERE T2.employeedeleted = 0 AND T2.employeeName LIKE '"+EmpName+"'",con);

这就是你试图做的非常不安全的样子。这在任何现实世界的环境中都是不可接受的，尽管正如ryan所说，但只是为了给你一个解决你所提供的代码的解决方案。

This is what it looks like with the very insecure way you''re trying to do it. This is unacceptable in any real world environment though as ryan said, but just to give you a solution on the code you''ve provided this would be it.

谢谢你的回复。 ....有了你的回答 - 我得到了它的工作...... :)



我接受了你的答案，这可以关闭。

Thank you for the reply..... With your answer - I got it to work... :)

I have accepted your answer and this can be closed.

这篇关于具有多个条件和Like值的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！