Data read from database returning as "System.Data.SqlClient.SqlDataReader"

Question

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.

MySQL and SQL Server are completely different products. The classes you use come from the SQL Server ADO.NET Provider. To connect to MySQL you'd need ConnectorNet, a different NuGet package — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 15, 2022 at 9:57
WARNING: Your code is dangerous. It is wide open to SQL injection attacks. Always, always, always parametrise your code. Why do we always prefer using parameters in SQL statements? — Thom A
– Thom A ♦, Commented Dec 15, 2022 at 10:04
@PanagiotisKanavos i just realised sorry, it is because i was using MySql previously and switched to SQL Server. Sorry for any troubles — Charlie Gale
– Charlie Gale, Commented Dec 15, 2022 at 10:15
@Larnu I will definitely look into it thanks. Any recommendations are appreciated — Charlie Gale
– Charlie Gale, Commented Dec 15, 2022 at 10:15
This code wouldn't work with MySQL either. You should probably start with an ADO.NET tutorial. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 15, 2022 at 10:20

Marc Gravell · Accepted Answer · 2022-12-15 09:49:47Z

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 }));

This worked thanks. I will work on the parameters and reader issues

Collectives™ on Stack Overflow

Data read from database returning as "System.Data.SqlClient.SqlDataReader"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related