Passing DropDownList value into SQL command in ASP.net

I have a DropDownList which gets it values from SQL table
I want to get the Average of the selected item (course in this case) from the dropDownList and to show it in a label :
This section works –

SqlConnection sqlConnection1;
    sqlConnection1 = new SqlConnection(@"Data Source=HASQLEXPRESS; Initial Catalog=Grades1; Integrated Security=True");
    SqlCommand Command = null;
  
        Command = new SqlCommand("SELECT Course FROM GradesTable1", sqlConnection1);
        Command.Connection.Open();
        SqlDataAdapter dataAdapter = new SqlDataAdapter(Command);
        DataTable dataTble1 = new DataTable();
        dataAdapter.Fill(dataTble1);

        if (dataTble1.Rows.Count > 0)
        {
            foreach (DataRow row in dataTble1.Rows)
            {
                ListItem course1 = new ListItem(row["Course"].ToString());
                if (!DropDownList1.Items.Contains(course1))
                {
                    DropDownList1.Items.Add(course1); // showing the 2 courses
                }
            }
        }
        Command.Connection.Close();

    }
}

Here is the problem – (I get nothing, no data )
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
    SqlConnection sqlConnection1;
    sqlConnection1 = new SqlConnection(@"Data Source=HASQLEXPRESS; Initial Catalog=Grades1; Integrated Security=True");
    SqlCommand Command = null;
  
        Command = new SqlCommand($"SELECT AVG(Grade) FROM GradesTable1 WHERE Course = @course", sqlConnection1);
        Command.Parameters.AddWithValue("@course", DropDownList1.SelectedItem);
        Command.Connection.Open();
        SqlDataReader sqlDataReader1 = Command.ExecuteReader();

        if (sqlDataReader1.Read())
        {
            LabelAverage.Text = sqlDataReader1[0].ToString();
        }
        else
        {
            LabelAverage.Text = "No Data"; // doesn't get into here anyhow
        }
   }

EDIT
I tried several variations as $"SELECT AVG(Grade) AS "ClassAVG" FROM GradesTable1 WHERE Course = @course" and Command.Parameters.AddWithValue("@course", DropDownList1.SelectedItem.Text),
or DropDownList1.SelectedValue

I believe the problem is with the DropDownlist values which being received from the SQL and are not hard coded.

Is there a correct way to this? is it possible without knowing what are the “Courses” in advanced?

Thanks for the answers, feel free to give your opinion.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I found out what was missing in the DropDownList in aspx page (not the aspx.cs page) -the AutoPostBack="true"
Adding that to DropDownList solved the problem.

Method 2

// query = Sql query

query.Select(s => new MusteriNoktaComboItemDTO
            {
                Label = s.Adi,
                Value = s.ID.ToString()
            }).ToList();


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x