ASP.NET C#中无法通过SUM函数更新SQL Server列求助

我在ASP.NET C#中尝试通过SUM函数关联两个表的ID来更新SQL Server的列，但始终无法成功。以下是我的相关代码：

初始代码 #

protected void GridView1_SelectedIndexChanged1(object sender, GridViewSelectEventArgs e)
{
    txt_ID.Text = GridView1.Rows[e.NewSelectedIndex].Cells[0].Text;
}

string ObjektID = txt_ID.Text;

SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlDataSource1"].ToString());
SqlCommand cmd2 = new SqlCommand();
SqlCommand cmd3 = new SqlCommand("SELECT SUM(Summe As BigInt) As SummeXY, ItemObjektID FROM [Item] GROUP BY ItemObjektID");

cmd2.CommandText = "UPDATE Objekt SET Summe = @Sum WHERE ObjektID = @ID";

try
{
    conn2.Open();
    cmd3.Connection = conn2;
    object result = cmd3.ExecuteScalar();

    cmd2.Connection = conn2;
    cmd2.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(ObjektID);
    cmd2.Parameters.Add("@Sum", SqlDbType.BigInt).Value = Convert.ToInt64(result);
    cmd2.CommandType = CommandType.Text;
    cmd2.ExecuteNonQuery();
}

修改后的代码（SQL查询器中有效，但代码中无效） #

protected void GridView1_SelectedIndexChanged1(object sender, GridViewSelectEventArgs e)
{
    txt_ID.Text = GridView1.Rows[e.NewSelectedIndex].Cells[0].Text;
}

protected void Button2_Click(object sender, EventArgs e)
{
    string ObjektID = txt_ID.Text;
    SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlDataSource1"].ToString());  
    SqlCommand cmd2 = new SqlCommand(); 
   cmd2.CommandText = "UPDATE Objekt SET Summe = (SELECT SUM(Summe) as SummeXY FROM Item WHERE ItemObjektID = @ID GROUP BY ItemObjektID) WHERE ObjektID = @ID";  
     try
     {
         conn2.Open();
cmd2.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(ObjektID); 
         cmd2.Connection = conn2; 
         cmd2.CommandType = CommandType.Text;
         cmd2.ExecuteNonQuery();
     }

     catch (System.Data.SqlClient.SqlException ex)
     {
         string msg = "Update Error:";  
         msg += ex.Message;
         throw new Exception(msg);
     }

     finally
     {
         conn2.Close();
    }
}

ASPX页面的SqlDataSource配置 #

<asp:sqldatasource id="SqlDataSource1"
    selectcommand="Select [ObjektID], [Name], [Beschreibung], [Summe], [PostenAls], [RangInBilanz] From [Objekt]"
updatecommand="UPDATE [Objekt] SET [Summe] = [SummeXY] (SELECT SUM([Summe]) as [SummeXY] FROM [Item] WHERE [ItemObjektID] = [@ID] GROUP BY [ItemObjektID]) WHERE [ObjektID] = [@ID]"
    ConnectionString="<%$ ConnectionStrings:SqlDataSource1 %>"
    runat="server">
  </asp:sqldatasource>

问题排查与解决方法 #

1. 初始代码的核心问题 #

• SQL语法错误：SUM(Summe As BigInt)写法错误，正确的类型转换应为SUM(CAST(Summe AS BigInt))或SUM(CONVERT(BigInt, Summe))。
• 逻辑错误：cmd3的查询未过滤指定ItemObjektID，返回的是所有分组的第一个SUM值，并非当前选中ObjektID对应的总和，导致更新对象错误。

2. 修改后代码的优化方案 #

• 处理空值场景：当Item表中无对应ID的记录时，子查询会返回NULL，用ISNULL将其转为0，避免Objekt.Summe被置空：

  UPDATE Objekt SET Summe = ISNULL((SELECT SUM(Summe) FROM Item WHERE ItemObjektID = @ID), 0) WHERE ObjektID = @ID
  

• 增加ID有效性校验：避免txt_ID.Text为空或非整数导致转换失败：

  if (!int.TryParse(txt_ID.Text, out int objektId))
  {
      // 可通过ClientScript给用户弹窗提示输入有效ID
      return;
  }
  

• 使用using自动释放资源：替代手动关闭连接，避免连接泄露：

  protected void Button2_Click(object sender, EventArgs e)
  {
      if (!int.TryParse(txt_ID.Text, out int objektId))
      {
          ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('请输入有效的ID');", true);
          return;
      }
  
      string connectionString = ConfigurationManager.ConnectionStrings["SqlDataSource1"].ToString();
      string updateSql = @"UPDATE Objekt 
                          SET Summe = ISNULL((SELECT SUM(Summe) FROM Item WHERE ItemObjektID = @ID), 0) 
                          WHERE ObjektID = @ID";
  
      using (SqlConnection conn2 = new SqlConnection(connectionString))
      using (SqlCommand cmd2 = new SqlCommand(updateSql, conn2))
      {
          cmd2.Parameters.Add("@ID", SqlDbType.Int).Value = objektId;
          try
          {
              conn2.Open();
              int rowsAffected = cmd2.ExecuteNonQuery();
              // 可根据rowsAffected判断是否更新成功，比如给用户反馈
          }
          catch (SqlException ex)
          {
              throw new Exception($"Update Error: {ex.Message}");
          }
      }
  }
  

3. SqlDataSource的UpdateCommand修正 #

原配置的UpdateCommand语法错误，修正后如下：

<asp:sqldatasource id="SqlDataSource1"
    selectcommand="Select [ObjektID], [Name], [Beschreibung], [Summe], [PostenAls], [RangInBilanz] From [Objekt]"
    updatecommand="UPDATE [Objekt] SET [Summe] = ISNULL((SELECT SUM([Summe]) FROM [Item] WHERE [ItemObjektID] = @ObjektID), 0) WHERE [ObjektID] = @ObjektID"
    ConnectionString="<%$ ConnectionStrings:SqlDataSource1 %>"
    runat="server">
    <UpdateParameters>
        <asp:Parameter Name="ObjektID" Type="Int32" />
    </UpdateParameters>
</asp:sqldatasource>

内容的提问来源于stack exchange，提问作者Sarsmas Ahangari