从数据库中搜索与用户输入的一个或多个字母相对应的一行 [英] to search a row from db corresponding to one or more alphabets entered by user

问题描述

我有一个查询，当我输入信息时会获取数据，但是我希望当我输入一部分数据(如1个或多个字母)时该查询能够获取数据...我尝试使用LIKE％，但显示错误. .i在下面发布我的查询... anybodu可以帮助我进行优化吗?

Hi i have a query which gets data when i enter an information but i want that query to get data when i enter a part of data like 1 or more alphabets...i tried using LIKE % but it showed error...i am posting my query below...can anybodu help me refine it?

select * from Agent where AgentId= ''" + txtA_ID.Text.ToString() + "'' or RegistrationDate= ''" + txtReg_Date.Text.ToString() + "'' or Email = ''" + txtEmail.Text.ToString() + "'' or  Phone = ''" + txtPhone.Text.ToString() + "'' or AccountType = ''" + txtAct_Type.Text.ToString() + "'' or CreditCardNum = ''" + txtCredit_Card.Text.ToString() + "'' or CardExpiration = ''" + txtCardExp.Text.ToString() + "'' or Domain = ''" + txtDomain.Text.ToString() + "''"

推荐答案

以下几点:
1)您写的查询将返回其中任一条件通过的行. IE.如果用户输入了ID，或者用户输入了Reg_Date，或者...您需要多考虑一下.
2)您不需要在Text属性上使用ToString方法:它们已经是字符串了！
3)您不应该通过连接字符串来创建查询:它使您容易遭受意外或故意的SQL Injection攻击，这可能会破坏数据库.相反，请使用参数化查询:

A couple of things:
1) Your query as written will returns rows where any one of the conditions passes. I.e. if the user has entered the ID, or the user has entered the Reg_Date, or... You need to think about this a bit more.
2) You do not need to use the ToString method on Text properties: they are already strings!
3) You should not create queries by concatenating strings: it leave you wide open to an accidental or deliberate SQL Injection attack, which could destroy your database. Instead, use parametrized queries:

SqlCommand com = new SqlCommand("SELECT * FROM Agent WHERE AgentID=@ID OR RegistrationDate=@RD", con);
com.Parameters.AddWithValue("@ID", txtA_ID.Text); 
com.Parameters.AddWithValue("@ID", txtReg_Date.Text);

(我缩短了，但您明白了).

我建议您从小处着手:在单个字段上使用LIKE，然后根据需要展开

(I shortened it, but you get the idea).

I would suggest you start small: use LIKE on a single field, and then expand as needed

使用字符串模式来获取所需的字母...

Use string pattern to get you desire alphabets...

这篇关于从数据库中搜索与用户输入的一个或多个字母相对应的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！