Can not Update Columns by Sum, SQL Server, ASP.NET C#

zqry0prt 于 2024-01-05 发布在 SQL Server

关注(0)|答案(2)|浏览(297)

I can not UPDATE my SQL Server column by SUM and selecting the two different IDs in to tables?

Here is my code:

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

EDIT This is my new Code but still does not work if I try it at SQL Query of the MS SQL Server it works?

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

This is at aspx Site:

<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>

I need help!

sql-server

来源：https://stackoverflow.com/questions/77600732/can-not-update-columns-by-sum-sql-server-asp-net-c-sharp

2条答案

按热度按时间

lstz6jyr1#

This is, in my opinion, an XY Problem ; you should not be storing aggregated data in our tables. Doing so will only cause you to endlessly chase your tail as every time the table Item has a DML statement run against it you need to perform one against Objekt too.

Instead use a VIEW to calculate the aggregate values, and then if you need them, query that view. In it's simplest form, the VIEW will likely look something like this:

CREATE OR ALTER VIEW dbo.YourView AS
    
    SELECT I.ItemObjektID,
           SUM(CONVERT(bigint,I.Summe)) AS SummeXY
    FROM dbo.Item I
    GROUP BY I.ItemObjektID;

Of course, you may well want to JOIN to your table Objekt so that you can expose other columns from that table as well.

赞(0）回复(0）举报 2024-01-05

r6l8ljro2#

Consider performing your update as a single statement:

/* Some demo DDL/DML */
DECLARE @Objekt TABLE (ObjektID INT, Summe BIGINT);
INSERT INTO @Objekt (ObjektID) VALUES (1),(2),(3);

DECLARE @Item TABLE (ItemObjektID INT, Value INT);
INSERT INTO @Item (ItemObjektID, Value) VALUES (1,1),(2,1),(1,9),(3,1),(3,5);

DECLARE @ID INT = 2; /* This is not required when passing the param in from a Parameterized SqlCommand */

UPDATE o
   SET Summe = v.SummeXY
  FROM (
        SELECT i.ItemObjektID, SUM(CAST(i.Value AS BIGINT)) AS SummeXY
          FROM @Item i
         GROUP BY i.ItemObjektID 
       ) v
    INNER JOIN @Objekt o
      ON v.ItemObjektID = o.ObjektID
      AND o.ObjektID = @ID;

If we then select from the table:

SELECT *
  FROM @Objekt;

We can see it has been updated with the total for the 2 ID.

ObjektID	Summe
1
2	1
3

As Thom explains in the other answer, this is possibly a sub-optimal strategy and it may be more effective to create a view with the totals, or perhaps a computed column , instead (depending on the source and complexity of the actual calculation).

赞(0）回复(0）举报 2024-01-05

我来回答

Can not Update Columns by Sum, SQL Server, ASP.NET C#

2条答案

相关问题

热门标签

最新问答