c# textbox autocomplete from sql table

Question

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

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;

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

Conrad Frix · Accepted Answer · 2015-10-24 00:41:38Z

1

The cm variable represents a command.

Because cm.Parameters.AddWithValue("@name", searchFor); is failing with the error The variable name '@name' has already been declared. you can conclude that the cm variable is living longer then this block of code.

You can either

1) Reinitialize the command every time (this is something most people do). e.g.

     cm = new SqlCommand(); //Assumes sql server
     cm.Connection = cn;

or

2) Check the cmd.Parameters for the @name parameter and then add if it doesn't exist and then set it .

if (!(cmd.Parameters.Contains("@name")
{
     cmd.Paramters.Add("@name",SqlDbType.Varchar)
}

cmd.Paramters["@name"].Value = serachFor;

Notes on SQL Injection with FROM " + tableName + " WHERE.

comboBox1.Text is what populates tableName. It is only a dangerous string if it's a string that the users can change (for example a web page). If it's a WPF or Window Forms app then it's not dangerous.*

If comboBox1.Text is from a web page then the best thing you can do is use a white list to validate that the string hasn't been altered and if it has then to not return any results. For example

if (!ValidTableNames.Contains(tableName)) 
    return;

What's nice is you already have the white list since you populated the combo box with it.

*Technically they could change the values with debugger tools but at that point they can just change the command text directly anyway.

edited Oct 24, 2015 at 0:41

answered Oct 23, 2015 at 18:38

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user5456980 Over a year ago

thanks for reply and I need to ask u how to Reinitialize the command every time ?

user5456980 Over a year ago

my problem is that I want to use parameters because of sql injection

user5456980 Over a year ago

got it working now but one problem left :) .. the data appears in the autocomplete are the data in the 2 columns combined next to eachother but what I wanted is to show matches of what I entered In the textbox means if I entered a value that was found in itmcode column shows only results in column itmcode and if I enter a value was found in itmname column it show only names

Ahmad Hammoud · Accepted Answer · 2015-10-23 18:39:25Z

0

Replace

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

whith

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

answered Oct 23, 2015 at 18:39

Ahmad Hammoud

7011 gold badge5 silver badges15 bronze badges

2 Comments

LarryBud Over a year ago

This just begs for SQL injection.

Ahmad Hammoud Over a year ago

how about replacing quotes inside the string with \' with the Replace function

Collectives™ on Stack Overflow

c# textbox autocomplete from sql table

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related