c# textbox autocomplete from sql table

Question

0.00/5 (No votes)

See more:

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.

C#

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

Posted 23-Oct-15 8:37am

Ahmed Zoeil

Add a Solution

Comments

phil.o 23-Oct-15 15:30pm

We would need to see the part where cm is declared and assigned. Is the code block you showed in a loop?

CHill60 23-Oct-15 20:38pm

I may be wrong but I think if you are using .Parameters you would need to have two separate ones e.g.

cm.CommandText = @"SELECT  distinct(itmcode+''+itmname) AS name FROM " + tableName + " WHERE itmcode Like @name1 OR itmname LIKE @name2";
 
            cm.Parameters.AddWithValue("@name1", searchFor);
            cm.Parameters.AddWithValue("@name2", searchFor);

phil.o 1-Nov-15 2:49am

I checked: nothing prevents from using the same parameter at several places in a SQL query. The exception which is thrown signals that an already present parameter was attempted to be added to a parameter collection.

ZurdoDev 23-Oct-15 22:31pm

I believe either phil.o is right or CHill60 is right. Let us know.

phil.o 1-Nov-15 2:47am

Given the error (SQL parameter already present), I suspect this code is in a loop, and the parameter should be declared outside the loop, and only modified inside. But I didn't get any answer, so... I guess we'll never know ^^

CHill60 30-Oct-15 11:22am

Has your problem been resolved?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ink0gnit0 · Answer 1 · 2015-10-31T20:08:00

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%'";
...