Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Connect DataGridView to the 2 Tables. Using INNER JOIN and GROUP..
But.. GROUP is NoT Work...
Please Help ME
 
My Code is
Dim strSQL6 As String = "SELECT FeesTransaction.Regno,FeesTransaction.ST_Name, SUM( FeesTransaction.Amount_Paid) As TotalAmountPaid, FeesMaster.FeeAmount,  FeesTransaction.FeeType, FeesTransaction.Class, FeesTransaction.Section FROM  FeesTransaction INNER JOIN  FeesMaster ON FeesTransaction.Regno = FeesMaster.Regno WHERE FeesTransaction.class='" & cboClass.Text & "' and FeesTransaction.section='" & cboSection.Text & "' and FeesTransaction.FeeType='" & cboFeeType.Text & "' GROUP BY FeesTransaction.Regno, FeesTransaction.ST_Name, FeesTransaction.FeeType, FeesTransaction.Class, FeesTransaction.Section, FeesMaster.FeeAmount "
 
My DataGridView Show Like This
RegNo	ST_Name	AmountPaid 	Class	Section
A101	AAAA	450	I	A
A101	AAAA	450	I	A
A102	BBBB	300	I	A
A102 	BBBB	300	I	A
 
How To Avoid DUPLICATE DATA.. Please Help Me..
Posted 15-Jan-13 0:48am
Edited 15-Jan-13 7:45am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hello Navas,
 
You have fewer fields in your mock of the datagridview above. Offhand, it looks like you don't have columns for the FeeAmount and FeeType (however you do have regno, st_name, amountpaid, class, and section).
 
Without seeing the data tables, I would be that if you add the missing columns to your datagridview, you'll see that these "duplicate" lines are actually unique lines, you just can't see the differences due to the missing columns.
 
Hope this helps,
 
-Bert
  Permalink  
Comments
Navas Khanj at 15-Jan-13 9:56am
   
Hello Bert.
 
Really I tried..For this Code...
 
Dim strSQL6 As String = "SELECT FeesTransaction.Regno, FeesTransaction.ST_Name, FeesMaster.FeeAmount FROM FeesTransaction INNER JOIN FeesMaster ON FeesTransaction.Regno = FeesMaster.Regno WHERE FeesTransaction.class='" & cboClass.Text & "' and FeesTransaction.section='" & cboSection.Text & "' and FeesTransaction.FeeType='" & cboFeeType.Text & "' GROUP BY FeesTransaction.Regno, FeesTransaction.ST_Name, FeesMaster.FeeAmount"
 
When i Join New Table(FeesMaster) After that DataGridView Come Like this
 
Regno ST_Name FeeAmount
A100 AAA 1500
A100 AAA 1500
 

Please Wht Problem my Code.. Please Tell me..
Bert Mitton at 15-Jan-13 12:09pm
   
I believe you tried :)
 
But it still looks like you're missing some columns.
 
On your datagridview, try setting AutoGenerateColumns = True.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If all you want is the Regno, ST_Name, and FeeAmount columns, and you want the FeeAmount columns totalled, then use this query:
 
Dim strSQL6 As String = "SELECT  FeesTransaction.Regno, FeesTransaction.ST_Name, sum(FeesMaster.FeeAmount) as FeeAmount  FROM  FeesTransaction INNER JOIN  FeesMaster ON FeesTransaction.Regno = FeesMaster.Regno WHERE FeesTransaction.class='" & cboClass.Text & "' and FeesTransaction.section='" & cboSection.Text & "' and FeesTransaction.FeeType='" & cboFeeType.Text & "' GROUP BY  FeesTransaction.Regno, FeesTransaction.ST_Name"
 
This assumes that all 3 columns are already on your datagridview, as per your last statement.
 
Thanks,
 
-Bert
  Permalink  
Comments
Navas Khanj at 15-Jan-13 12:36pm
   
Wht I need :
Regno ST_Name FeeAmount
A100 AAA 1500
A100 AAA 1500 -- This is Duplicate Data Come to DataGridView.. Why This Come to DataGridView.. Please tell me..
Bert Mitton at 15-Jan-13 12:53pm
   
Did you try the solution above?
 
I can only help you so much, since we can't see the actual datatables.
 
In your original question, you were missing a column. Therefore, it may have looked like a duplicate record, however each record actually had a different fee type of fee amount.
 
In your follow up, you had reduced the number of columns. That's fine, however you could have had a duplicate record in this instance because you weren't summing the FeeAmount.
 
Now, you could try SELECT DISTINCT. This will return only a single unique record when there are similar records.
 
This would be useful if you wanted to determine the permutations of Regno, ST_Name, and FeeAmount actually stored in the table.
 
However, you would want to be careful. As an example, if I was looking at a sales order table, and wanted a list of all customer with an open sales order, I could use distinct.
 
But say I want to know the size of the orders. I could query for customer and sales dollars. But there could be two orders with the same dollar value. Using SELECT DISTINCT would hide this data, and only return a single row.
 
In general, if you're looking for data from a table, do not use DISTINCT. DISTINCT should only be used if you are looking to find out what values are stored in a table, without regard to the frequency.
Navas Khanj at 15-Jan-13 14:27pm
   
FeesMaster : Table Data
Reg no Class Section Sname FeeType FeeAmount ID
A600 I A BBBB Team-1 750 1
A700 I A DSDSDSDSD Team-1 750 2
A900 I A PPPP Team-1 750 3
A600 I A BBBB Jan 650 4
A700 I A DSDSDSDSD Jan 650 5
A900 I A PPPP Jan 650 6

 
Fees_Transaction Table Data
Regno ST_Name Class Section Amount_Paid FeeType ID
A600 BBBB I A 500 Team-1 1
A900 PPPP I A 750 Team-1 2
A700 DSDSDSDSD I A 500 Team-1 3
A700 DSDSDSDSD I A 250 Team-1 4
A700 DSDSDSDSD I A 300 Jan 5
A700 DSDSDSDSD I A 350 Jan 6
A600 BBBB I A 150 Team-1 7
A900 PPPP I A 200 Jan 8

I am Using Code
Class , Section and FeeType Same Means Display I want to DataGridView Like This

Reg no ST_Name Amount_Paid FeeAmount FeeType Class Section

A700 DSDSDSDSD 650 750 Jan I A
A900 PPPP 200 650 Jan I A
 
But Now Display Like This

Reg no ST_Name Amount_Paid FeeAmount FeeType Class Section
A700 DSDSDSDSD 650 750 Jan I A
A700 DSDSDSDSD 650 750 Jan I A
A900 PPPP 200 650 Jan I A
A900 PPPP 200 650 Jan I A
 
My Code is
DataGridView1.DataSource = Nothing
DataGridView1.Rows.Clear()
DataGridView1.Columns.Clear()
Dim strSQL7 As String = "SELECT Fees_Transaction.Regno, Fees_Transaction.ST_Name, Sum(Fees_Transaction.Amount_Paid)as TotalAmountPaid, FeesMaster.FeeAmount, Fees_Transaction.FeeType, Fees_Transaction.Class, Fees_Transaction.Section FROM Fees_Transaction INNER JOIN FeesMaster ON Fees_Transaction.Regno=FeesMaster.Regno WHERE Fees_Transaction.class='" & cboClass.Text & "' and Fees_Transaction.section='" & cboSection.Text & "' and Fees_Transaction.FeeType='" & cboFeeType.Text & "' GROUP BY Fees_Transaction.Regno, Fees_Transaction.ST_Name, Fees_Transaction.FeeType, Fees_Transaction.Class, Fees_Transaction.Section, FeesMaster.FeeAmount"
Dim DaAp7 As New SqlDataAdapter(strSQL7, con)
Dim DSet7 As New DataTable
DaAp7.Fill(DSet7)
 
'========================================
With Me.DataGridView1
.Columns.Add("Regno", "RegNo")
.Columns.Add("ST_Name", "Student Name")
.Columns.Add("Amount_Paid", "Amount Paid")
.Columns.Add("Remaining", "Remaining")
.Columns.Add("TotalAmt", "Total Amount")
.Columns.Add("FeeType", "Fee Type")
.Columns.Add("Class", "Class")
.Columns.Add("Section", "Section")
.Columns(0).Width = 85
.Columns(1).Width = 250
.Columns(2).Width = 150
.Columns(3).Width = 150
.Columns(4).Width = 140
.Columns(5).Width = 150
.Columns(6).Width = 90
.Columns(7).Width = 90
' .AllowUserToAddRows = False
.EditMode = DataGridViewEditMode.EditProgrammatically
.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter
End With
 
For Each dr As DataRow In DSet7.Rows
Me.DataGridView1.Rows.Add()
With Me.DataGridView1.Rows(Me.DataGridView1.Rows.Count - 1)
.Cells("Regno").Value = dr("Regno")
.Cells("ST_Name").Value = dr("ST_Name")
.Cells("Amount_Paid").Value = dr("TotalAmount")
'.Cells("TotalAmt").Value = dr("FeeAmount")
.Cells("FeeType").Value = dr("FeeType")
.Cells("Class").Value = dr("Class")
Navas Khanj at 16-Jan-13 8:58am
   
Thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

"SELECT DISTINCT FeesTransaction.Regno, FeesTransaction.ST_Name, sum(FeesMaster.FeeAmount) as FeeAmount FROM FeesTransaction INNER JOIN FeesMaster ON FeesTransaction.Regno = FeesMaster.Regno WHERE FeesTransaction.class='" & cboClass.Text & "' and FeesTransaction.section='" & cboSection.Text & "' and FeesTransaction.FeeType='" & cboFeeType.Text & "' GROUP BY FeesTransaction.Regno, FeesTransaction.ST_Name"
 
Simple way from me, just add "distinct" in your query
  Permalink  
v2
Comments
Navas Khanj at 16-Jan-13 8:57am
   
Thanks
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

in the data table before load to the DGV .. set a primery key for it
 
dt_adtendence.PrimaryKey = New DataColumn() {dt_adtendence.Columns("ID")}
  Permalink  
v2
Comments
Navas Khanj at 16-Jan-13 8:58am
   
Thanks

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

  Print Answers RSS
0 OriginalGriff 554
1 Gihan Liyanage 338
2 RyanDev 268
3 Sergey Alexandrovich Kryukov 263
4 ChauhanAjay 220
0 Sergey Alexandrovich Kryukov 8,936
1 OriginalGriff 7,891
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 16 Jan 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