Click here to Skip to main content
15,901,426 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I WANT TO DISPLAY MY VALUES FROM SQLTABLE
SQLTABLE HAVING 4 COLUMNS (ID, ITEMS_REF_NO, CR, DR).

ID | ITEMS_REF_NO | CR  | DR |
1  | MAHESH       | 100 | 0  |
2  | SURESH       | 500 | 0  |
3  | KAPOOR       | 400 | 0  |
4  | GULAM        | 0   |200 |
5  | SINGH SHA    | 0   |100 |
6  | RANVIR       | 0   |050 |


NOW I WANT TO DISPLAY IN DATAGRIDVIEW LIKE
ID | ITEMS_REF_NO | CR  | DR | BAL |
1  | MAHESH       | 100 | 0  | 100 |
2  | SURESH       | 500 | 0  | 600 |
3  | KAPOOR       | 400 | 0  | 1000|
4  | GULAM        | 0   |200 | 800 |
5  | SINGH SHA    | 0   |100 | 700 |
6  | RANVIR       | 0   |050 | 650 |


SOMEONE PLEASE HELP

What I have tried:

.........................................................
Posted
Updated 24-Sep-16 0:37am
Comments
OriginalGriff 24-Sep-16 5:49am    
DON'T SHOUT. Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalization if you want to be taken seriously.
[no name] 24-Sep-16 7:30am    
Screaming at us doesn't help. And, it customary to at least attempt to do your homework assignment yourself first before trying to get others to do it for you.

try this in c#

C#
DataTable dtSql = new DataTable();
           dtSql.Columns.Add("ID");
           dtSql.Columns.Add("ITEMS_REF_NO");
           dtSql.Columns.Add("CR");
           dtSql.Columns.Add("DR");
           dtSql.Rows.Add(1, "Mahesh", 100, 0);
           dtSql.Rows.Add(1, "SURESH", 500, 0);
           dtSql.Rows.Add(1, "KAPOOR", 400, 0);
           dtSql.Rows.Add(1, "GULAM", 0, 200);
           dtSql.Rows.Add(1, "SINGH SHA", 0, 100);
           dtSql.Rows.Add(1, "RANVIR", 0, 050);

           dtSql.Columns.Add("BAL");
           for (int i = 0; i < dtSql.Rows.Count; i++)
           {
               double cr, dr, bal;
               double.TryParse(dtSql.Rows[i]["CR"].ToString(), out cr);
               double.TryParse(dtSql.Rows[i]["DR"].ToString(), out dr);
               double.TryParse(dtSql.Rows[i - 1 == -1 ? 0 : i - 1]["BAL"].ToString(), out bal);
               bal = bal + cr - dr;
               dtSql.Rows[i]["BAL"] = bal.ToString();
           }

           dataGridView1.DataSource = dtSql;
 
Share this answer
 
If you are using SQL Server 2012 or higher, do it in the query:
SQL
SELECT ID, ITEMS_REF_NO, CR, DR,
       SUM(CR - DR) OVER(ORDER BY ID
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
                    AS BAL
FROM MyTable2
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900