I have a piece of code that I am trying to take all values from a connected database that fit criteria, stores them into a list, and then inputs them as options to select in a combobox.

The issue is when ran, rather than the values from the database, System.Data.SqlClient.SqlDataReader. Example.

What is shown

What should be displayed

Here is the code being used

    public void fillMakeBox() // used to populate the Make Box of the car builder.
    {
        connection.Open(); // opens connection to the database
        List<String> makes =new List<string>();
        string makesQuery = "SELECT makeName from makes";
        SqlCommand makesSearch = new SqlCommand(makesQuery, connection);
        SqlDataReader myReader = makesSearch.ExecuteReader();
        while (myReader.Read()) // loops to add all values read to a list.
        {
            makes.Add(myReader.ToString());
        }
        
        while (makeSelection.SelectedItem != null)
        {
            List<String> filteredMakes = new List<string>();
            string searchMakesQuery = $"Select makeName from makes WHERE makeName LIKE {makeSelection.SelectedItem} %";
            SqlCommand searchMakeSearch = new SqlCommand(searchMakesQuery, connection);
            SqlDataReader myReaderFilter = makesSearch.ExecuteReader();
            while (myReaderFilter.Read())
            {
                filteredMakes.Add(myReaderFilter.ToString());
            }
            makes = filteredMakes;

        }
        makeSelection.DataSource = makes;

    }

I have also tried using makes.Add(myReader["makeName"].ToString()); which did not work.

CodePudding user response：

makes.Add(myReader.GetString(0));
//...
makes.Add(myReaderFilter.GetString(0));

Additionally, right now it looks like you have two active readers on the same connection; that isn't usually possible (unless MARS is enabled); you should be using each reader (and the commands, and the connection), and make sure that the first reader (myReader) is disposed before you open the second reader (myReaderFilter)

connection.Open(); // opens connection to the database
List<String> makes =new List<string>();
string makesQuery = "SELECT makeName from makes";
using (SqlCommand makesSearch = new SqlCommand(makesQuery, connection))
{
    using SqlDataReader myReader = makesSearch.ExecuteReader();
    while (myReader.Read()) // loops to add all values read to a list.
    {
        makes.Add(myReader.GetString(0));
    }
}

while (makeSelection.SelectedItem != null)
{
    List<String> filteredMakes = new List<string>();
    string searchMakesQuery = $"Select makeName from makes WHERE makeName LIKE {makeSelection.SelectedItem} %";
    using (SqlCommand searchMakeSearch = new SqlCommand(searchMakesQuery, connection))
    {
        using SqlDataReader myReaderFilter = makesSearch.ExecuteReader();
        while (myReader.Read())
        {
            makes.Add(myReaderFilter.GetString(0));
        }
    }
    makes = filteredMakes;

}
makeSelection.DataSource = makes;

Additionally, you should not concatenate data into SQL queries; that's how you get SQL Injection. Please investigate "parameters". Or perhaps just use a tool like Dapper instead of all of this:

makes.AddRange(conn.Query<string>("SELECT makeName from makes"));
makes.AddRange(conn.Query<string>("Select makeName from makes WHERE makeName LIKE @name   '%'",
    new { name = makeSelection.SelectedItem }));