Click here to Skip to main content
15,899,754 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
my table is like this

FeeAssigned

===============================================================
FEE HEAD APR MAY JUN JUL AUG SEP OCT NOV DEC JAN FEB MAR
===============================================================
TUITION FEE 120 120 120 120 120 120 120 120 120 120 120 120
TRAN FEE 50 50 50 50 50 50 50 50 50 50 50 50
===============================================================


FeeExemption

===============================================================
FEE HEAD APR MAY JUN JUL AUG SEP OCT NOV DEC JAN FEB MAR
===============================================================
TUITION FEE 10 10 10 10 10 10 10 10 10 10 10 10
TRAN FEE 10 10 10 10 10 10 10 10 10 10 10 10
===============================================================

After subtract I want like this in gridview
FEEPayable

===============================================================
FEE HEAD APR MAY JUN JUL AUG SEP OCT NOV DEC JAN FEB MAR
===============================================================
TUITION FEE 110 110 110 110 110 110 110 110 110 110 110 110
TRAN FEE 40 40 40 40 40 40 40 40 40 40 40 40
===============================================================

Please help any body it is very urget
Posted
Comments
BillWoodruff 24-Jan-16 12:59pm    
What have you tried so far. Show the code.
Member 12245539 25-Jan-16 3:10am    
Actually I don't get any idea for this please help...Only I have inserted values into SQLDatabase as I mentioned above.
Sergey Alexandrovich Kryukov 24-Jan-16 13:49pm    
Help with what? This is not even a question.
—SA
F-ES Sitecore 29-Jan-16 4:27am    
If you're struggling with your coursework you should ask your tutor, we're not here to do your work for you.

1 solution

Here is your solution -

Create one window form in C# - 

1) One combo box (Name - cmbStudentId)
2) One Button (Name - button1)
3) one DataGridView (Name -dtgrdvStudFee)

Create below two tables in SQL SERVER and insert data

SQL
CREATE TABLE FeeAssigned ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))

CREATE TABLE FeeExemption ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))

--INSERT DATA
INSERT INTO FeeAssigned 
SELECT 'TUITION FEE',120,120,120,120,120,120,120,120,120,120,120,120,'STUD01'
UNION ALL
SELECT 'TRAN FEE',50,50,50,50,50,50,50,50,50,50,50,50,'STUD01'

INSERT INTO FeeExemption
SELECT 'TUITION FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'
UNION ALL
SELECT 'TRAN FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'


Then Create this procedure which will return the Fee calculation for student
SQL
CREATE PROC GetFeeStructureForStud(@StudentId NVARCHAR(50))
AS
BEGIN
SELECT FA.StudentId,FA.[FEE HEAD],
FA.APR - ISNULL(FET.APR,0) AS APR,
FA.MAY - ISNULL(FET.MAY,0) AS MAY,
FA.JUN - ISNULL(FET.JUN,0) AS JUN,
FA.JUL - ISNULL(FET.JUL,0) AS JUL,
FA.AUG - ISNULL(FET.AUG,0) AS AUG,
FA.SEP - ISNULL(FET.SEP,0) AS SEP,
FA.OCT - ISNULL(FET.OCT,0) AS OCT,
FA.NOV - ISNULL(FET.NOV,0) AS NOV,
FA.DEC - ISNULL(FET.DEC,0) AS DEC,
FA.JAN - ISNULL(FET.JAN,0) AS JAN,
FA.FEB - ISNULL(FET.FEB,0) AS FEB,
FA.MAR - ISNULL(FET.MAR,0) AS MAR
FROM FeeAssigned FA LEFT JOIN  
							 (SELECT FE.StudentId,FE.[FEE HEAD],
									 SUM(FE.APR) AS APR,
									 SUM(FE.MAY) AS MAY,
									 SUM(FE.JUN) AS JUN,
									 SUM(FE.JUL) AS JUL,
									 SUM(FE.AUG) AS AUG,
									 SUM(FE.SEP) AS SEP,
									 SUM(FE.OCT) AS OCT,
									 SUM(FE.NOV) AS NOV,
									 SUM(FE.DEC) AS DEC,
									 SUM(FE.JAN) AS JAN,
									 SUM(FE.FEB) AS FEB,
									 SUM(FE.MAR) AS MAR 
							   FROM FeeExemption FE 
							GROUP BY  FE.StudentId,FE.[FEE HEAD]) AS FET ON (FA.StudentId=FET.StudentId) AND (FA.[FEE HEAD]=FET.[FEE HEAD])
WHERE FA.StudentId=@StudentId
END 


And Finally our C# code to display data in datagridview

C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace _1073993_I_have_two_table_FeeAssigned_FeeExemption
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            string connetionString = null;
            SqlConnection cnn;
            connetionString = @"Data Source=RDBurmonPC\SQLEXPRESS;Initial Catalog=Test;User ID=Test;Password=Test@123";

            cnn = new SqlConnection(connetionString);
            cnn.Open();

            string query = "select DISTINCT StudentId from FeeAssigned";
            SqlCommand cmd = new SqlCommand(query, cnn);
            cmd.CommandText = query;
            
            SqlDataReader drd = cmd.ExecuteReader();
            while (drd.Read())
            {
                cmbStudentId.Items.Add(drd["StudentId"].ToString());
            }

            drd.Close();
            cnn.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string connetionString = null;
            SqlConnection cnn;
            connetionString = @"Data Source=RDBurmonPC\SQLEXPRESS;Initial Catalog=Test;User ID=Test;Password=Test@123";

            cnn = new SqlConnection(connetionString);
            cnn.Open();
            string query;

            query = string.Concat("EXEC GetFeeStructureForStud '" , cmbStudentId.Text.ToString() , "'");
            SqlDataAdapter dataAdapter = new SqlDataAdapter(query, cnn); //c.con is the connection string
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);
            dtgrdvStudFee.ReadOnly = true;
            dtgrdvStudFee.DataSource = ds.Tables[0];
            cnn.Close();
        }

    }
}


I hope this will work for you and if yes then vote and accept the answer
Thanks - RDBurmon
 
Share this answer
 
v6
Comments
Member 12245539 27-Jan-16 23:15pm    
There is something error in this if I exempt 100 Rs in two times as 50 & 50 then it shows like
'TUITION FEE',50,50,50,50,50,50,50,50,50,50,50,50,'STUD01'
'TUITION FEE',50,50,50,50,50,50,50,50,50,50,50,50,'STUD01'
where as it should be
'TUITION FEE',100,100,100,100,100,100,100,100,100,100,100,100,'STUD01'
in sum
RDBurmon 28-Jan-16 0:15am    
Give me your data in FeeAssigned and FeeExemption tables
Member 12245539 29-Jan-16 4:21am    
FeeAssigned

====================================================================================
Student Id | FEE_HEAD |APR |MAY |JUN |JUL |AUG |SEP |OCT |NOV |DEC |JAN |FEB |MAR
====================================================================================
Stud_01 | 1 |1500 |0 |0 |0 |0 |0 |0 |0 |0 |0 |0 |0
Stud_01 | 2 |250 |250 |250 |250 |250 |250 |250 |250 |250 |250 |250 |250
Stud_01 | 3 |350 |350 |350 |350 |350 |350 |350 |350 |350 |350 |350 |350
Stud_01 | 4 |150 |150 |150 |150 |150 |150 |150 |150 |150 |150 |150 |150
====================================================================================

FeeExemption



Student Id | FEE_HEAD |APR |MAY |JUN |JUL |AUG |SEP |OCT |NOV |DEC |JAN |FEB |MAR
====================================================================================
Stud_01 | 1 |700 |0 |0 |0 |0 |0 |0 |0 |0 |0 |0 |0
Stud_01 | 1 |100 |0 |0 |0 |0 |0 |0 |0 |0 |0 |0 |0
Stud_01 | 3 |50 |50 |50 |50 |50 |50 |50 |50 |50 |50 |50 |50
Stud_01 | 3 |50 |50 |50 |50 |50 |50 |50 |50 |50 |50 |50 |50
====================================================================================
RDBurmon 29-Jan-16 7:57am    
See the updated answer above
Member 12245539 29-Jan-16 23:13pm    
I got few updation in Store procedure but while altering or creating store proc occurs an error like
--------------------------------------------------------------------------
Msg 1087, Level 15, State 2, Procedure __GetFeeStructureForStud, Line 17
Must declare the table variable "@FeeAssigned".
Msg 1087, Level 15, State 2, Procedure __GetFeeStructureForStud, Line 17
Must declare the table variable "@FeeExemption".

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