How To Avoid Duplicate Data From DataGridView

Question

0.00/5 (No votes)

See more:

Connect DataGridView to the 2 Tables. Using INNER JOIN and GROUP..
But.. GROUP is NoT Work...
Please Help ME

My Code is

VB

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

Navas Khanj

Updated 15-Jan-13 7:45am

Sandeep Mewara

v3

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bert Mitton · Accepted Answer · 2013-01-15T03:35:00

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

Posted 15-Jan-13 3:35am

Bert Mitton

Comments

Navas Khanj 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 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.

Bert Mitton · Accepted Answer · 2013-01-15T06:18:00

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

Posted 15-Jan-13 6:18am

Bert Mitton

Comments

Navas Khanj 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 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 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 16-Jan-13 8:58am

Thanks

ACE_ · Accepted Answer · 2013-01-15T20:37:00

"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

Racer nick · Accepted Answer · 2013-01-15T22:30:00

Solution 4

in the data table before load to the DGV .. set a primery key for it

VB

dt_adtendence.PrimaryKey = New DataColumn() {dt_adtendence.Columns("ID")}

Posted 15-Jan-13 22:30pm

Racer nick

Updated 15-Jan-13 22:31pm

v2

Comments

Navas Khanj 16-Jan-13 8:58am

Thanks

Norris Chappell 1-May-15 17:57pm

Hi,
Can you please explain your suggestion?
dt_adtendence.PrimaryKey = New DataColumn() {dt_adtendence.Columns("ID")}
Would you put this code before the while clause?

How To Avoid Duplicate Data From DataGridView

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us