Duplicate Checking

[aska07]

I got a dataset which contains the following data:

id code description eff_date end_date
---------------------------------------------------------
1 ADV ADVANCE 2007-12-01 2007-12-31
2 ADV ADVANCE 2008-01-01
3 BUS BUS TICKET 2005-03-09 2007-04-05
4 BUS BUS TICKET 2007-04-06
5 PET PETROL FEES 2008-01-02 2009-10-11
6 PET PETROL FEES 2009-10-12
7 BOK BOOK 2010-01-01

Now if I want to display all records in a ListView but I don't want to display two records with the same code, that means the output should be like this:

ADV ADVANCE
BUS BUS TICKET
PET PETROL FEES
BOK BOOK

how??

Thanks first...

[debasisdas]

what is the underlying database ?

What is the query that you are using ?

[Knvn]

If you are using framework 3.5 or above you can write LINQ query & bind it to listview.

[aska07]

what is the underlying database ?

What is the query that you are using ?

i am trying to use typed dataset to get the records from database actually...

dim i as integer

For i = 0 to ds.EmpCosts.rows.count - 1
If ds.EmpCosts.rows(i).item("code").ToString <> ds.empCosts.rows(i+1).item("code").ToString Then
Dim lvitem As New ListViewItem(ds.EmpCosts.rows(i).item("code").ToString)
lvitem.subitems.add(ds.EmpCosts.rows(i).item("description").ToString)
lvw.items.add(lvitem)
Next

[Narue]

In my opinion, typed datasets cause more trouble than they're worth.

[debasisdas]

i was asking about database not dataset.

[aska07]

i was asking about database not dataset.

Sorry, MS SQL

[debasisdas]

try this

select code, description, count(code) as total
group by code

[Mitja Bonca]

Here is the working code:

Public Sub New()
	InitializeComponent()

	Dim ds As New DataSet()
	Dim table As New DataTable("MyTable")
	ds.Tables.Add(table)
	'my example columns creating and population:
	table.Columns.Add(New DataColumn("id", GetType(Integer)))
	table.Columns.Add(New DataColumn("code", GetType(String)))
	table.Columns.Add(New DataColumn("desc", GetType(String)))
	table.Rows.Add(1, "ADV", "ADVANCE")
	table.Rows.Add(2, "ADV", "ADVANCE")
	table.Rows.Add(3, "BUS", "BUS TICKET")
	table.Rows.Add(4, "BUS", "BUS TICKET")

	'create ListView:
	listView1.Columns.Add("Id", -2, HorizontalAlignment.Left)
	listView1.Columns.Add("Code", -2, HorizontalAlignment.Left)
	listView1.Columns.Add("Descriprion", -2, HorizontalAlignment.Left)
	listView1.View = View.Details
	listView1.FullRowSelect = True

	'do the selection of the dataTable:
	For Each dr As DataRow In ds.Tables("MyTable").Rows
		Dim bNewInsertion As Boolean = True
		For i As Integer = 0 To listView1.Items.Count - 1
			If dr(1).ToString() = listView1.Items(i).SubItems(1).Text Then
				bNewInsertion = False
				Exit For
			End If
		Next
		If bNewInsertion Then
			Dim lvi As New ListViewItem(dr(0).ToString())
			lvi.SubItems.Add(dr(1).ToString())
			lvi.SubItems.Add(dr(2).ToString())
			listView1.Items.Add(lvi)
		End If
	Next
End Sub

[Mitja Bonca]

Forgot to say: You have to do an sql query to fill dataTable, but becuase I dont have this kind of dataBase, I filled it up by code (just example).

But what you really need it the 2nd part of the upper code.
bye

[aska07]

Forgot to say: You have to do an sql query to fill dataTable, but becuase I dont have this kind of dataBase, I filled it up by code (just example).

But what you really need it the 2nd part of the upper code.
bye

Really thanks you so much, i think tat's what i need...

[aska07]

try this

select code, description, count(code) as total
group by code

Thanks for your help also..

[aska07]

If you are using framework 3.5 or above you can write LINQ query & bind it to listview.

Thanks for your information also..