Add DataSource to DropDownList: Error DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name

Question

I'm trying to add column data from a SQL Server table to a dropdown list: Text = Name, Value = Id

Year.DataTextField = reader["Name"].ToString();
Year.DataValueField = reader["Id"].ToString();

These years are present: 2021, 2022, etc.

Can you tell me where I made a mistake? And what should I specify in the DataSource?

string query = @"SELECT Id, Name, Status FROM Period";

// Define the connection string to the SQL Server database
string connectionString = "Data Source=TEST;Initial Catalog=Test1;Integrated Security=SSPI";

// Create a SqlConnection object to connect to the database
using (SqlConnection connection = new SqlConnection(connectionString))
{
    // Open the database connection
    connection.Open();

    // Create a SqlCommand object to execute the query
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        // Execute the query and retrieve the results
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Loop through the rows and retrieve the column names
            while (reader.Read())
            {
                Year.DataSource = reader;
                Year.DataTextField = reader["Name"].ToString();
                Year.DataValueField = reader["Id"].ToString();
                Year.DataBind();
            }
        }
    }
}

I get this error:

DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name '2021'.

Answer 1

To form a correct DataSource, it is necessary to use other object SQLDataAdapter, a working version has been published for those who are interested:

SqlDataAdapter adapter = new SqlDataAdapter(query, connectionString);
DataTable years = new DataTable();
adapter.Fill(years);
Year.DataSource = years;
Year.DataTextField = "Name";
Year.DataValueField = "Id";
Year.DataBind();