Click here to Skip to main content
12,248,339 members (53,042 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB VB.NET
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 30-Jan-13 7:26am

1 solution

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

Solution 1

Again, you need to read up on JOIN queries, and now you need to read up on PIVOT too.
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 30 Jan 2013
Copyright © CodeProject, 1999-2016
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