How to display data from database to panel/label based on drop down list selection?

Question

The items in my drop-down list are Fruits and Tools, so when l select either Fruits or Tools l want the panel or label to populate value from the database but l am getting this error: 'Data is Null. This method or property cannot be called on Null values

Category Table

ID	Category
1	Fruits
2	Tools

Order Table

Item	Category	Amount
Apple	Fruits	200
Banana	Fruits	100
Hammer	Tools	400

Asp.net core Controller

[Route("TotalValue")]
[HttpGet]
public decimal TotalValue()
{
    var query = "Select Sum( Amount ) as Total from Order Where Category = @Category";
    string sqlDataSource = _configuration.GetConnectionString("DefaultConnection");
    decimal TotalAmount = 0;     
    string value = "Category";                 
    using (SqlConnection con = new SqlConnection(sqlDataSource))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand(query, con))
        {   
            cmd.Parameters.AddWithValue("@Category", value);                
            using (SqlDataReader dr = cmd.ExecuteReader())
            {                        
                if (dr.Read())
                {
                    TotalAmount = dr.GetDecimal(0); 
                }
            }
        }
        con.Close();
    }
    return TotalAmount;
}

Expected output: If I select Fruits then it should display 300

Answer 1

try this

   [Route("TotalValue")]
    [HttpGet]
    public decimal TotalValue()
    {
        var query = "SELECT SUM(Amount) AS Total FROM [Order] WHERE Category = @Category";
        string sqlDataSource = _configuration.GetConnectionString("DefaultConnection");
        decimal totalAmount = 0;
        string value = "Category";
        using (SqlConnection con = new SqlConnection(sqlDataSource))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@Category", value);
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.HasRows && dr.Read())
                    {
                        if (!dr.IsDBNull(0))
                        {
                            totalAmount = dr.GetDecimal(0);
                        }
                    }
                }
            }
            con.Close();
        }
        return totalAmount;
    }

it will check dr.HasRows before calling dr.Read(). This ensures that the code only attempts to read data if there are rows returned by the query. also we added a check !dr.IsDBNull(0) to handle null values. if the value is null, it will not assign it to totalAmount.