C# WinForm - Compare DataReader w/ Listbox Contents and Display the Matches

Question

I am attempting to tackle a project where I am pulling a column of data from an SQL database and comparing the data with values within a listbox. So far, it is finding a comparison, but is only returning one value, even with multiple matches in the listbox.

What am I doing wrong here? Thanks for any help anyone can offer.

    private void btnDoAudit_Click(object sender, EventArgs e)
    {
        string respName = "something";
        SqlDataReader reader = null;

        using (SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=XXXX;Integrated Security=True;;User Instance=True"))
        {
            using (SqlCommand command = new SqlCommand("SELECT [Responsibility_Name] FROM [tblResponsibility] WHERE [Sensitive_Transaction]='TRUE'", conn))
            {
                conn.Open();

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    respName = (string)reader["Responsibility_Name"];



                        if (lstResponsibilities.Items.Contains(respName) == true)
                        {
                          txtResults.Text = respName;
                        }

                }
                reader.Close();
                conn.Close();
            }
        }
    }

score 1 · Accepted Answer · answered May 16 '13 at 01:00

1

You're overwriting your txtResults.Text every time you find a match. You can append instead:

txtResults.Text += respName;

Or maybe you'd rather just keep the list of matches in a List and then join them into something more legible. You could put this near the top of your method where you're declaring your other variables:

List<string> matches = new List<string>();

Then instead of setting txtResults.Text, you'd just do:

matches.Add(respName);

And once you're done with the SqlConnection, at the end of your method, you can join them into a nice-looking string:

txtResults.Text = string.Join(", ", matches);

answered May 16 '13 at 01:00

itsme86

19,266
4
41
57

Great, that works! I knew I was overlooking something so simple. – InnerMB May 16 '13 at 01:51
If I throw a else if (lstResponsibilities.Items.Contains(respName) == false) { txtResults.Text = "There is no conflict."; } ... and then add a matching value to the listbox and rerun the method, the "There is no conflict" message stays and is not overwritten by the new matching value. – InnerMB May 16 '13 at 02:05
@InnerMB If you're using += to append text, make sure you clear out the text first. At the top of the method you'll want to add `txtResults.Text = "";` or `txtResults.Text = string.Empty();` or `txtResults.Clear();` – itsme86 May 16 '13 at 02:35
Thanks for the pointer, but for some reason , the "There is no conflict" string seems to overpower the matching if statement, still. – InnerMB May 16 '13 at 02:45

score 0 · Answer 2 · answered May 16 '13 at 01:02

Looks like in your while statement, every time you find a match, you overwrite the previous matches:

txtResults.Text = respName;

Maybe you want to create a comma separated list of them:

txtResults.Text += respName + ", ";

Then trim the last comma afterwards

C# WinForm - Compare DataReader w/ Listbox Contents and Display the Matches

2 Answers2