Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
TABLE 1 : FeeMaster1
Class FeeType
I JAN
I FEB
I MAR
II JAN
II FEB
I APR
II BUSFEE

THIS CODE FOR DGV HEADER TEXT PURPOSE


Dim strSQL7 As String = "SELECT * FROM FeeMaster1 WHERE Class='" & cboClass.Text & "'"
Dim DaAp7 As New SqlDataAdapter(strSQL7, con)
Dim Dset7 As New DataTable
DaAp7.Fill(Dset7)


With Me.DataGridView2
.Columns.Add("Regno", "RegNo")
For i As Integer = 0 To Dset7.Rows.Count - 1
.Columns.Add(i, Dset7.Rows(i).Item("FeeType"))
Next

End With

Above Code Working Good



After That I Connect this Table2 For Data Diplay Purpose

TABLE2 (FeeTrans)

REGNO FEETYPE AMOUNT PAID CLASS SECTION
A100 JAN 500 I A
A200 JAN 750 II B
A100 FEB 150 I A
A300 JAN 222 I A
A100 MAR 120 I A
A100 APR 54 I A
A200 BUSFEE 120 II B


I WANT DGV LIKE THIS (SUPPOSE I SELECT CLASS I MEans)


'----------------
Regno JAN FEB MAR APR
'----------------
A100 500 150 120 54
A300 222 0 0 0


I Am USING THIS CODE FOR DISPLAY PURPOSE BUT IT NOT WOEKING .. Please Help me.. Correct COde..


Dim strSQL10 As String = "SELECT Regno, FeeType,AmountPaid FROM FeeTrans WHERE Class='" & cboClass.Text & "'"
Dim DaAp10 As New SqlDataAdapter(strSQL10, con)
Dim DSet10 As New DataTable
DaAp10.Fill(DSet10)

For Each dr As DataRow In DSet10.Rows
Me.DataGridView2.Rows.Add()
With Me.DataGridView2.Rows(Me.DataGridView2.Rows.Count - 1)
.Cells("Regno").Value = dr("Regno")
For i As Integer = 0 To DSet10.Rows.Count - 1
.Cells(Dset7.Rows(0).Item("FeeType")).Value = dr("AmountPaid")
Next

End With
Next
Posted

1 solution

Again, you need to read up on JOIN queries, and now you need to read up on PIVOT too.
 
Share this answer
 
Comments
Navas Khanj 30-Jan-13 13:06pm    
Dim strSQL8 As String = "select * from (select Regno, FeeType, FeeAmount, SName, Class from FeesMaster) FeesMaster PIVOT(Sum(FeeAmount) for [FeeType] in ([JAN],[FEB],[marr]))as FeeAmount where Class='" & cboClass.Text & "'"

here i use PIVOT Command it's working.. and more help
how can i change ([JAN],[FEB],[marr])) i don't want manual code that check database i use this code but not working (' + FeeType + ') please tell me u advise..

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