Question 298201 in ASP.NET Question posted in
The official documentation can be found here.

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

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

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search