ListView control showing one value multiple times after search through textbox-CodePudding

I am currently working on WinForms application where on form I have one textbox for searching , and one ListView control where will be populated data from database as result of user's entry.

Here is the following code for implementation of TextChanged event on textbox control

        private void tbSearchGlassOnShell_TextChanged(object sender, EventArgs e)
        {
            Form1 form = new Form1();
            string cmdString = string.Empty;

            try
            {
                string connString = string.Format("Server={0};Username={1};Database={2};Port={3};Password={4};",
                    form.Tbhostname, form.Tbusername, form.Tbdatabase, form.Tbport, form.Tbpassword);
                MySqlConnection conn = new MySqlConnection(connString);
                conn.Open();
                cmdString = "SELECT shell_glass.shell_glass_id,shell.shell_name,glass.glass_name,"  
                    "shell_glass.shell_glass_quantity,shell_glass.shell_glass_ordernumber,"  
                    "shell_glass.shell_glass_datetime,shell_glass.user_id FROM shell_glass "  
                    "JOIN shell using (shell_id) JOIN glass using (glass_id)"  
                    "WHERE LOWER(glass.glass_name) LIKE '%"   tbSearchGlassOnShell.Text.ToLower()   "%'";
                MySqlCommand command = new MySqlCommand(cmdString, conn);
                MySqlDataReader reader = command.ExecuteReader();
                while(reader.Read())
                {
                    ListViewItem item = new ListViewItem(reader["shell_glass_id"].ToString());
                    item.SubItems.Add(reader["shell_name"].ToString());
                    item.SubItems.Add(reader["glass_name"].ToString());
                    item.SubItems.Add(reader["shell_glass_quantity"].ToString());
                    item.SubItems.Add(reader["shell_glass_ordernumber"].ToString());
                    item.SubItems.Add(reader["shell_glass_datetime"].ToString());
                    item.SubItems.Add(reader["user_id"].ToString());
                    lvSearchGlassOnShell.Items.Add(item);
                }
                reader.Close();
            }
            catch (MySqlException ex)
            {
                form.TbSetupLog.AppendText("MySqlExepction: "   ex.Message   Environment.NewLine);
            }
            catch (Exception ex)
            {
                form.TbSetupLog.AppendText("Exception: "   ex.Message   Environment.NewLine);
            }
        }

For some reason, ListView control is displaying same value multiple times after user's entry as you can see on picture below

Question: how can I achieve that I have only one value but not duplicate after user's entry ?

CodePudding user response：

The TextChanged event will be called for each character typed in the TextBox, so while you type the first letter a search is performed, then another one when you type the second letter and so on. You never clear the ListView items collection, so each search add another entry in the listview.

To solve your immediate problem just call

lvSearchGlassOnShell.Items.Clear();

before entering the reader loop.

But there are other problems in the code above. The serious one is the Sql Injection made possible because you concatenate strings to build the sql command.
Instead you need to use parameters in this way

try
{
    lvSearchGlassOnShell.Items.Clear();
    string connString = string.Format("Server={0};Username={1};Database={2};Port={3};Password={4};",
        form.Tbhostname, form.Tbusername, form.Tbdatabase, form.Tbport, form.Tbpassword);
    string cmdString = @"SELECT shell_glass.shell_glass_id, 
                     shell.shell_name,glass.glass_name,
                     shell_glass.shell_glass_quantity, 
                     shell_glass.shell_glass_ordernumber,
                     shell_glass.shell_glass_datetime,
                     shell_glass.user_id 
                 FROM shell_glass 
                 JOIN shell using (shell_id) 
                 JOIN glass using (glass_id)
                 WHERE LOWER(glass.glass_name) LIKE @name";

    using(MySqlConnection conn = new MySqlConnection(connString))
    using(MySqlCommand command = new MySqlCommand(cmdString, conn))
    {
        conn.Open();
        command.Parameters.Add("@name", MySqlDbType.Text).Value = tbSearchGlassOnShell.Text;
        MySqlDataReader reader = command.ExecuteReader();
        ....
   }
}
catch(Exception ex)
   ....

Here I have remove the concatenation of the input text and placed a parameter placeholder to avoid the SqlInjection problem. Also note how the connection being a disposable object needs to be enclosed in a using statement to correctly clean the resources used by that object