Autocomplete causes a crash

Question

I have a Windows Form app with a handful of fields, including a 'Company' Field and a 'Contact' Field. When you type an item in the company field and hit a button, it makes a query to a SQL database to fill in the contact information for that company in the 'Contact' field. I included really basic autocomplete in the 'Company' field, mostly for convenience.

Problem is that when I load up the form, as soon as I type anything into the 'Company' field, the program crashes. There are no other calls being made on a keystroke and I narrowed it down to autocomplete causing the problems.

The code that manages it all is as follows:

    public void GetRowCount()
    {
        try
        {
            _DbRows = db.CountRows();
            tContact.Text = _DbRows.ToString();
        }
        catch (Exception tEx)
        {
            MessageBox.Show("Exception in GetRowCount. Exception: " + tEx.Message);
        }
    }
    private void GetCustomerList()
    {
        String customerQuery = "SELECT DISTINCT Name FROM Customers";

        try
        {
            _CustomerList = db.ReturnCustomers(customerQuery, _DbRows);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }
    public void PopulateAutofillList()
    {
        try
        {
            tCompany.AutoCompleteSource = AutoCompleteSource.CustomSource;
            tCompany.AutoCompleteCustomSource.AddRange(_CustomerList);
            MessageBox.Show(_CustomerList.Length.ToString());
            tCompany.AutoCompleteMode = AutoCompleteMode.Append;
        }
        catch (Exception tEx)
        {
            MessageBox.Show("Exception On Autocomplete. Exception: " + tEx.Message);
        }
    }

These are all called separately in a OnLoad Method, like so:

    private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            GetRowCount();
            GetCustomerList();
            PopulateAutofillList();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Initial Connection to the Database Failed.");
        } 
    }

And the DB queries themselves:

    public String[] ReturnCustomers(string sqlQuery, int size)
    {
        createConnectionString();
        StreamWriter file = new StreamWriter("dbCustomerList");
        int i = 0;
        String[] results = new String[size];

        SqlConnection myConnection = new SqlConnection(_ConnectionString); 
        {

            myConnection.Open();

            SqlCommand cmd = new SqlCommand(sqlQuery, myConnection); 
            {
                SqlDataReader reader;
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader.GetString(0));
                    results[i] = reader.GetString(0);
                    //file.WriteLine(i ": " + results[i]);
                    i++;
                }     
                return results;
            }
        }
    }
    public int CountRows()
    {
        createConnectionString();
        int rows;

        SqlConnection myConnection = new SqlConnection(_ConnectionString); 
        {

            myConnection.Open();

            SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Customers;", myConnection); 

            rows = Convert.ToInt32(cmd.ExecuteScalar());

            Console.Write("Row Count: " + rows);
        }

        return rows;
    }

I'm not totally sure what's broken. All my little checks that show up along the way indicate that things are right. For testing, I had all this running on SQLite and it was fine. It broke when I moved it to SQL.

--

Edit:

The full exception that Windows Small Business Server 2011 gives:

Problem signature: Problem Event Name: APPCRASH

Application Name: SSLP.exe

Application Version: 1.0.0.0

Application Timestamp: 5213d1b8

Fault Module Name: shell32.dll

Fault Module Version: 6.1.7600.17038

Fault Module Timestamp: 4fd2d370

Exception Code: c0000005

Exception Offset: 000ac2c5

OS Version: 6.1.7600.2.0.0.305.9

Locale ID: 1033

Additional Information 1: a7aa

Additional Information 2: a7aa91f17ea749d42a4de3b390fa5b3d

Additional Information 3: a7aa

Additional Information 4: a7aa91f17ea749d42a4de3b390fa5b3d

Answer 1

I ran into a similar issue with this - grabbing a list of numbers from a database to use as an autocomplete source. In particular, I was using Linq to SQL and returning a distinct list.

One of the values in that distinct list was null, and that was causing my program to crash when I would enter a value into the text box with the autocomplete source. Couldn't find a way to catch any exception, though, so debugging it was a bit of a hassle.

Simply adding a "where number != null" to my Linq to SQL query fixed the issue for me.

Answer 2

This code is more than a little funky. The biggest issue is not using a dynamic list. Then you won't need two DB calls. You won't need the count, etc. etc. You should also use using for these objects. Like this:

public String[] ReturnCustomers(string sqlQuery, int size)
{
    createConnectionString();
    StreamWriter file = new StreamWriter("dbCustomerList");
    List<string> results = new List<string>();

    using (SqlConnection myConnection = new SqlConnection(_ConnectionString))
    {
        myConnection.Open();

        using(SqlCommand cmd = new SqlCommand(sqlQuery, myConnection))
        {
            using(SqlDataReader reader = cmd.ExecuteReader())
            {
              while (reader.Read())
              {
                Console.WriteLine(reader.GetString(0));
                results.Add(reader.GetString(0));
              }     
            }
        }
        myConnection.Close();
    }
    return results.ToArray();
}

Answer 3

Alright, so this is the silliest of things:

In desperation, I went through the entire database looking for something that would throw it off. There was a duplicate entry in the DB and I'm guessing it broke when Total Rows (as returned by CountRows) didn't match up with the Distinct number of rows.

Really dumb, but after removing the duplicate entries, it does work.