如何使用带前缀值的变量从sql表中选择行 [英] how to select rows from sql table using a variable with prefix values

问题描述

我在textbox1中输入2个字符并将其传递给变量'v_prefix'。使用'v_prefix'我想从表'member'中检索与'mem_name'数据列匹配的相应行。我使用以下代码从sql表中检索行。



protected void TextBox1_TextChanged（object sender，EventArgs e）

{

v_prefix = TextBox1.Text;

List< string> adds1 = new List< string>（）;

SqlConnection con = new SqlConnection（ConfigurationManager.ConnectionStrings [tnrestConnectionString]。ConnectionString）;

SqlDataAdapter da1 = new SqlDataAdapter（ 从成员中选择mem_id，mem_name substring（mem_name，1,2），其中mem_name类似于+ v_prefix +'，con）;

SqlCommandBuilder cb21 = new SqlCommandBuilder（da1）;

DataSet ds1 = new DataSet（member1）;

da1.Fill（ds1，member1）;

for（int i = 0; i < ds1.Tables [0] .Rows.Count; i ++）

{

adds1.Add（ds1.Tables [0] .Rows [i] .ItemArray [ 0] .ToString（））;

}



}





我得到的错误如下：'mem_name'附近的语法不正确。

是否可以将'％'与变量v_prefix一起使用？

任何人都可以帮助我？

I enter 2 characters in textbox1 and pass this to variable 'v_prefix'. Using 'v_prefix' I want to retrieve corresponding rows from table 'member' that matches with 'mem_name' data column.I use the following code to retrieve rows from sql table.

protected void TextBox1_TextChanged(object sender, EventArgs e)
{
v_prefix =TextBox1.Text;
List<string> adds1 = new List<string>();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["tnrestConnectionString"].ConnectionString);
SqlDataAdapter da1 = new SqlDataAdapter("select mem_id,mem_name substring(mem_name,1,2) from member where mem_name like '"+v_prefix+"'", con);
SqlCommandBuilder cb21 = new SqlCommandBuilder(da1);
DataSet ds1 = new DataSet("member1");
da1.Fill(ds1, "member1");
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
adds1.Add(ds1.Tables[0].Rows[i].ItemArray[0].ToString());
}

}


I get error like: Incorrect syntax near 'mem_name'.
Is it possible to use '%' with variable v_prefix ?
Can anyone help me out?

推荐答案

T SQL LIKE子句需要至少一个'％'或'_'字符，或者它与完全相同的测试相同。

尝试：

The SQL LIKE clause needs either at least one '%' or '_' character, or it is identical to a equality test.
Try:

SqlDataAdapter da1 = new SqlDataAdapter("select mem_id,mem_name substring(mem_name,1,2) from member where mem_name like '"+v_prefix+"%'", con);

或者更好地使用参数化查询来避免SQL注入攻击，这可能会破坏或破坏您的数据库......

Or better do it with a parameterised query to avoid SQL Injection attacks, which can damage or destroy your database...

这篇关于如何使用带前缀值的变量从sql表中选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！