c＃textbox从sql表自动完成 [英] c# textbox autocomplete from sql table

问题描述

我需要在combobox1中搜索表格，用户将在autoCompleteTextbox1中输入文字，它可以是itmcode或itmname



但是我收到错误说：附加信息：变量名'@name'已经声明。变量名在查询批处理或存储过程中必须是唯一的。

I need to search tables in combobox1 with the text user will enter in autoCompleteTextbox1 and it can be itmcode or itmname

but I get error says : Additional information: The variable name '@name' has already been declared. Variable names must be unique within a query batch or stored procedure.

if (cn.State == ConnectionState.Closed)
        {
            cn.Open();
        }
        cm.Connection = cn;
        if (autoCompleteTextbox1.Text == "")
        {
        }
        else
        {
            AutoCompleteStringCollection namecollection = new AutoCompleteStringCollection();
            string searchFor = "%" + autoCompleteTextbox1.Text + "%"; //the string the user entered.
            string tableName = comboBox1.Text;
            cm.CommandText = @"SELECT  distinct(itmcode+''+itmname) AS name FROM " + tableName + " WHERE itmcode Like @name OR itmname LIKE @name";

            cm.Parameters.AddWithValue("@name", searchFor);
            SqlDataReader rea = cm.ExecuteReader();
            if (rea.HasRows == true)
            {
                while (rea.Read())
                    namecollection.Add(rea["name"].ToString());
            }
            rea.Close();

            autoCompleteTextbox1.AutoCompleteMode = AutoCompleteMode.Suggest;
            autoCompleteTextbox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
            autoCompleteTextbox1.AutoCompleteCustomSource = namecollection;

我的代码中的错误是什么以及如何修复它plz

what is the error in my code and how to fix it plz

推荐答案

在SQL语句中认为错误是引用缺席：

Think error is in quotes absence in SQL statement:

...
string searchFor = "'%" + autoCompleteTextbox1.Text + "%'"; //the string the user entered.
...
cm.CommandText = @"SELECT distinct(itmcode+' '+itmname) AS name FROM " + tableName + " WHERE itmcode Like @name OR itmname LIKE @name";
...

或

or

...
string searchFor = "%" + autoCompleteTextbox1.Text + "%"; //the string the user entered.
...
cm.CommandText = @"SELECT distinct(itmcode+' '+itmname) AS name FROM " + tableName + " WHERE itmcode Like '@name' OR itmname LIKE '@name'";
...

或

or

...
string searchFor = autoCompleteTextbox1.Text.Trim(); //the string the user entered.
...
cm.CommandText = @"SELECT distinct(itmcode+' '+itmname) AS name FROM " + tableName + " WHERE itmcode Like '%@name%' OR itmname LIKE '%@name%'";
...

这篇关于c＃textbox从sql表自动完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！