Distinc Keyword Does not works

Question

0.00/5 (No votes)

See more:

Hi friends ..,

I need to remove all the duplicate values from an database table i done the following problem with the help of distinct keyword but it not works pls help...

C#

da = new SqlDataAdapter("select DISTINCT Date,Humidity,Temprature,light,Voltage,outp from Dataset", cn.con);
           dt = new DataTable();
         da.Fill(dt);
           dataGridView1.DataSource = dt;

Thanks You...,
Have a Grt Day :-)

Posted 10-Jul-13 18:51pm

usha C

Add a Solution

Comments

Naz_Firdouse 11-Jul-13 0:55am

does not work means...
are you not getting unique values???

usha C 11-Jul-13 0:58am

Yes Yes...

praks_1 11-Jul-13 1:19am

U want it through procedure or in c#

usha C 11-Jul-13 1:58am

anythng else if it on code its most well and good...

ArunRajendra 11-Jul-13 3:12am

Post some sample data which you are using and the expeted output.

3 solutions

Solution 2

distinct means unique all the rows must be same for distinct for example

if you have values
Date Humidity Temprature light Voltage outp
1 aa 25 36 1 1
2 aa 25 36 1 1

so if you select Date,Humidity,Temprature,light,Voltage,outp then it will return two rows

if you select Humidity,Temprature,light,Voltage,outp it will return one row

Posted 10-Jul-13 20:23pm

Dholakiya Ankit

Comments

usha C 11-Jul-13 2:28am

Ya Fyn i have understood that but an not getting the result from ur example am geting 2 rows instead of getting 1 row...

Dholakiya Ankit 11-Jul-13 2:32am

give me your two rows.......

usha C 11-Jul-13 2:49am

Its fr example but actually i have 1000 records in my table with duplicate values

2 48 Private 344415 Bachelors 13 Married-spouse-absent
3 48 Private 241928 HS-grad 9 Separated

from dis i required the result to display as only 1 row..........

Dholakiya Ankit 11-Jul-13 2:52am

you have wrong require ment if you select 2,3 row only then one column will be shown if you take different then you will not able to get whole result into one you have to use union either

usha C 11-Jul-13 2:58am

Am Selecting 2 and 3 only i need the result from tat table as
2 48 Private 344415 Bachelors 13 Married-spouse-absent

it shuld b my result but am geting both the rows

Solution 3

It will not work becouse it will return you the rows with atleast having a record. You need to count no of repeatation for a row then delete the rows that are more than once, You can use Identity column to do this.

SELECT id, COUNT(*) TotalCount
FROM items
GROUP BY id
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Posted 10-Jul-13 20:34pm

Ravi Kumar Shakya

Comments

usha C 11-Jul-13 2:47am

It works for removing value from only 1 column from an table but i have 6 columns from all that 6 column the duplicate values must remove and the result value should display in gridview help me pls..

Ravi Kumar Shakya 11-Jul-13 3:42am

Here you need to use a Column having identity or primary key so that with the help of that column value you can delete complete row of that table if the count of that value is more than on.

Can you please show me the table with the [Column Name] and [Data] of 10-20 rows or some sample table like that so that i can give you exact query.

Add a Solution

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

Priyanka7777 · Accepted Answer · 2013-07-10T20:08:00

Solution 1

Try this:

C#

da = new SqlDataAdapter("select DISTINCT Date,Humidity,Temprature,light,Voltage,outp from Dataset", cn.con);
dt = new DataTable();
         da.Fill(dt);

  DataView dv= dt(/*default*/true).DefaultView;

This will remove duplicate entries from your Datatable dt.

Posted 10-Jul-13 20:08pm

Priyanka7777

Comments

usha C 11-Jul-13 2:15am

No mam still not getting the result am geting this error
"'dt' is a 'field' but is used like a 'method'"

Priyanka7777 11-Jul-13 2:28am

My mistake.

Instead of using a DataTable dt in this line use a DataView

DataView dv= dt(/*default*/true).DefaultView;

Correct this line to :

DataView dv1 = new DataView();
DataView dv= dv1.ToTable(/*default*/true).DefaultView;

usha C 11-Jul-13 2:35am

still not getting an prob with

Object reference not set to an instance of an object.

Ravi Kumar Shakya 11-Jul-13 2:28am

It will not work becouse it will return you the rows with atleast having a record. You need to count no of repeatation for a row then delete the rows that are more than once, You can use Identity column to do this.

SELECT id, COUNT(*) TotalCount
FROM items
GROUP BY id
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Priyanka7777 11-Jul-13 3:11am

The solution does work.I have checked it.

Try this piece of code and check what result you get in 'distinctTable' DatatTable:

DataTable table2 = new DataTable("articletable");
table2.Columns.Add("articleID");
table2.Columns.Add("title");
table2.Columns.Add("content");

DataRow row2 = table2.NewRow();
row2[0] = "1";
row2[1] = "article name";
row2[2] = "article contents go here";
table2.Rows.Add(row2);

DataRow row1 = table2.NewRow();
row1[0] = "1";
row1[1] = "article name";
row1[2] = "article contents go here";
table2.Rows.Add(row1);

DataTable distinctTable = table2.DefaultView.ToTable( /*distinct*/ true);

usha C 11-Jul-13 3:16am

can u pls explain me dis code what does 'articletable','articleID' contents

Priyanka7777 11-Jul-13 3:22am

This was just a sample code to let you know how you get distinct records.

As per your code provided above you can change it to:

da = new SqlDataAdapter("select DISTINCT Date,Humidity,Temprature,light,Voltage,outp from Dataset", cn.con);
dt = new DataTable();
da.Fill(dt);

//dt will contain the data from your query.

DataTable distinctTable = dt.DefaultView.ToTable( /*distinct*/ true);

See if it works.

usha C 11-Jul-13 3:21am

Thnks lot the code doesnt contents any error bt hw to display dis in my gridview...

Priyanka7777 11-Jul-13 3:30am

You can now bind the result that you got in 'distinctTable' to your gridview's DataSource event.

usha C 11-Jul-13 3:33am

Thank u mam....thnks lot

Priyanka7777 11-Jul-13 3:35am

Glad that it helped you... :)

usha C 11-Jul-13 3:36am

Thnks fr ur excellent work

Distinc Keyword Does not works

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Distinc Keyword Does not works

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us