none
How to do addition calculation in SQL server database table column using C#

    Question

  • Hello everyone,

    I use Visual Studio 2005 and SQL server express 2005.

    I need help on how to add a numerical value to an existing value in a sql server database table column and then updating the column with new value.

    For instance, let us assume that the intial value of the variable contained in a database column named POINTS is 0. A VS C# application Checkedlistbox with a list of 3 items named X, Y and Z. The checkedlistbox items have the following numerical values assinged to them: X = 3, Y = 2, Z = 4 etc.

    Scenerios:

    (1) If Checkedlistbox item X for instance is checked, I want '3' added to column POINTS' variable value '0' and the answer i.e '3' inserted into column POINTS.

    (2) If Checkedlistbox items Y and Z are checked, I want '2' + '4' i.e '6' added to column POINTS' variable value '0' and the answer i.e '6' inserted into column POINTS. 

    I am able to do all the database, stored procedure and connection between VS and SQL. All I required is code to perform the above.

    Thank you all in anticipation.

    Friday, November 02, 2012 10:53 PM

Answers

  • http://www.ezineasp.net/post/ASPNet-20-GridView-Compute-Column-Sum-using-C-sharp.aspx

    or may be following can help you...

    SELECT SUM(amount) FROM (
      SELECT AmountReceived [Amount] FROM A WHERE Date BETWEEN (@param1) AND (@param2)
      UNION ALL
      SELECT AmountCharged FROM B WHERE DateOfEntry BETWEEN (@param1) AND (@param2)
      UNION ALL
      SELECT Amount FROM C WHERE Date BETWEEN (@param1) AND (@param2)
      UNION ALL
      SELECT AmountCharged FROM D WHERE DateOfRequest BETWEEN (@param1) AND (@param2)            
      UNION ALL
      SELECT -AmountDue FROM E WHERE Date BETWEEN (@param1) AND (@param2)
    ) [subquery]

    you put this between @" and " then you don't need all the string concatenation syntax.

    hth


    Aarsh Talati


    • Edited by Aarsh (MCTS) Friday, November 02, 2012 11:32 PM
    • Marked as answer by Topa 1 Wednesday, November 21, 2012 2:28 AM
    Friday, November 02, 2012 11:29 PM

All replies

  • Hello everyone,

    I use Visual Studio 2005 and SQL server express 2005.

    I need help on how to add a numerical value to an existing value in a sql server database table column and then updating the column with new value.

    For instance, let us assume that the intial value of the variable contained in a database column named POINTS is 0. A VS C# application Checkedlistbox with a list of 3 items named X, Y and Z. The checkedlistbox items have the following numerical values assinged to them: X = 3, Y = 2, Z = 4 etc.

    Scenerios:

    (1) If Checkedlistbox item X for instance is checked, I want '3' added to column POINTS' variable value '0' and the answer i.e '3' inserted into column POINTS.

    (2) If Checkedlistbox items Y and Z are checked, I want '2' + '4' i.e '6' added to column POINTS' variable value '0' and the answer i.e '6' inserted into column POINTS. 

    I am able to do all the database, stored procedure and connection between VS and SQL. All I required is code to perform the above.

    Thank you all in anticipation.

    Friday, November 02, 2012 10:55 PM
  • Hello everyone,

    I use Visual Studio 2005 and SQL server express 2005.

    I need help on how to add a numerical value to an existing value in a sql server database table column and then updating the column with new value.

    For instance, let us assume that the intial value of the variable contained in a database column named POINTS is 0. A VS C# application Checkedlistbox with a list of 3 items named X, Y and Z. The checkedlistbox items have the following numerical values assinged to them: X = 3, Y = 2, Z = 4 etc.

    Scenerios:

    (1) If Checkedlistbox item X for instance is checked, I want '3' added to column POINTS' variable value '0' and the answer i.e '3' inserted into column POINTS.

    (2) If Checkedlistbox items Y and Z are checked, I want '2' + '4' i.e '6' added to column POINTS' variable value '0' and the answer i.e '6' inserted into column POINTS. 

    I am able to do all the database, stored procedure and connection between VS and SQL. All I required is code to perform the above.

    Thank you all in anticipation.

    Friday, November 02, 2012 10:58 PM
  • Hello everyone,

    I use Visual Studio 2005 and SQL server express 2005.

    I need help on how to add a numerical value to an existing value in a sql server database table column and then updating the column with new value.

    For instance, let us assume that the intial value of the variable contained in a database column named POINTS is 0. A VS C# application Checkedlistbox with a list of 3 items named X, Y and Z. The checkedlistbox items have the following numerical values assinged to them: X = 3, Y = 2, Z = 4 etc.

    Scenerios:

    (1) If Checkedlistbox item X for instance is checked, I want '3' added to column POINTS' variable value '0' and the answer i.e '3' inserted into column POINTS.

    (2) If Checkedlistbox items Y and Z are checked, I want '2' + '4' i.e '6' added to column POINTS' variable value '0' and the answer i.e '6' inserted into column POINTS. 

    I am able to do all the database, stored procedure and connection between VS and SQL. All I required is code to perform the above.

    Thank you all in anticipation.

    Friday, November 02, 2012 11:10 PM
  • http://www.ezineasp.net/post/ASPNet-20-GridView-Compute-Column-Sum-using-C-sharp.aspx

    or may be following can help you...

    SELECT SUM(amount) FROM (
      SELECT AmountReceived [Amount] FROM A WHERE Date BETWEEN (@param1) AND (@param2)
      UNION ALL
      SELECT AmountCharged FROM B WHERE DateOfEntry BETWEEN (@param1) AND (@param2)
      UNION ALL
      SELECT Amount FROM C WHERE Date BETWEEN (@param1) AND (@param2)
      UNION ALL
      SELECT AmountCharged FROM D WHERE DateOfRequest BETWEEN (@param1) AND (@param2)            
      UNION ALL
      SELECT -AmountDue FROM E WHERE Date BETWEEN (@param1) AND (@param2)
    ) [subquery]

    you put this between @" and " then you don't need all the string concatenation syntax.

    hth


    Aarsh Talati


    • Edited by Aarsh (MCTS) Friday, November 02, 2012 11:32 PM
    • Marked as answer by Topa 1 Wednesday, November 21, 2012 2:28 AM
    Friday, November 02, 2012 11:29 PM
  • DUPLICATE POST.  The post with replies is here.
    • Edited by Wyck Saturday, November 03, 2012 4:28 AM
    Saturday, November 03, 2012 4:27 AM
  • DUPLICATE POST.  The post with replies is here.
    • Edited by Wyck Saturday, November 03, 2012 4:28 AM
    Saturday, November 03, 2012 4:27 AM
  • DUPLICATE POST.  The post with replies is here.
    • Edited by Wyck Saturday, November 03, 2012 4:28 AM
    Saturday, November 03, 2012 4:27 AM