Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Fees_Transaction Table Data Show Here
 
DGV
Regno	Class	Jan	Feb	Mar
A100	I	50	650	400
A200	I	100	250	0

FeeMaster Table Data Show Here
DGV				
Regno	Class	Jan	Feb	Mar
A100	I	650	750	1000
A200	I	650	750	1000

I Need OUTPUT LIKE THIS
Regno	Class	Jan	Feb	Mar
A100	I	600	100	600
A200	I	550	500	1000
 
Please Tell me How Can I Do this
 
I am using CODE
 
Dim strSQL11
        strSQL11 = "SELECT STUFF((SELECT DISTINCT ',['+ FeesType +']' FROM FeeMaster WHERE Class='" & cboClass.Text & "' For XMl Path('')), 1, 1, '')"
        Dim DaAp11 As New SqlDataAdapter(strSQL11, con)
        Dim Dset11 As New DataTable
        DaAp11.Fill(Dset11)
 

        Dim strSQL10
        strSQL10 = "SELECT * FROM (SELECT stdAdmission.Regno, stdAdmission.Class,  Fees_Transaction.FeeType," & _
        " Fees_Transaction.Amount_Paid FROM Fees_Transaction INNER JOIN stdAdmission ON Fees_Transaction.Regno=stdAdmission.Regno) FeeMaster" & _
        " PIVOT (Sum(Amount_Paid) for FeeType in (" + Dset11.Rows(0).Item("Column1") + "))as PVT where Class='" & cboClass.Text & "'"
        Dim DaAp10 As New SqlDataAdapter(strSQL10, con)
        Dim Dset10 As New DataTable
        DaAp10.Fill(Dset10)
        DGV1.DataSource = Dset10
 

        Dim strSQL12
        strSQL12 = "SELECT * FROM (SELECT stdAdmission.Regno, stdAdmission.Class,  FeesMaster.FeeType," & _
        " FeesMaster.FeeAmount FROM FeesMaster INNER JOIN stdAdmission ON FeesMaster.Regno=stdAdmission.Regno) FeeMaster" & _
        " PIVOT (Sum(FeeAmount) for FeeType in (" + Dset11.Rows(0).Item("Column1") + "))as PVT where Class='" & cboClass.Text & "'"
        Dim DaAp12 As New SqlDataAdapter(strSQL12, con)
        Dim Dset12 As New DataTable
        DaAp12.Fill(Dset12)
        DataGridView2.DataSource = Dset12
Posted 22-Feb-13 4:32am
Edited 22-Feb-13 8:29am
Maciej Los134.8K
v3

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Add this code after your code block
Dim strSQL13
strSQL13 = "select * from (" & strSQL10  & ") as t1   Except    select * from (" & strSQL12  & ") as t2"
Dim DaAp13 As New SqlDataAdapter(strSQL13, con)
        Dim Dset13 As New DataTable
        DaAp13.Fill(Dset13)
        DataGridView3.DataSource = Dset13
Happy coding!
Smile | :)
  Permalink  
v3
Comments
Navas Khanj at 23-Feb-13 1:54am
   
It' s Show ERROR : Incorrect syntax near 'Substract'.
Aarti Meswania at 23-Feb-13 12:01pm
   
instead of subtract use keyword 'Except' or 'Minus' :)
Navas Khanj at 23-Feb-13 14:41pm
   
Thanks
Aarti Meswania at 24-Feb-13 8:36am
   
welcome! :)
Glad to help you! :)

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 305
1 _Amy 235
2 Peter Leow 205
3 Andreas Gieriet 180
4 Dave Kreskowiak 130
0 OriginalGriff 7,540
1 Sergey Alexandrovich Kryukov 6,412
2 Maciej Los 3,849
3 Peter Leow 3,653
4 CHill60 2,712


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 23 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100