CODEWITHSUNDEEP
c#datagridviewduplicates
eaponz
eaponz

Reputation: 604

Clear Duplicates in Datagridview

I searched Stack Overflow for this kind of problem. But I found several similar problems regarding this one, but show different solution.

I would like to display data in my datagridview without duplicates. I don't know how did it duplicated.

conn.Open();
            query = "SELECT Books.BookID,Books.Title,Books.AuthorID,Authors.AuthorName,Books.ISBN from Books,Authors";
            cmd = new SqlCommand(query, conn);
            dr = cmd.ExecuteReader();
            
            while (dr.Read())               
            {
                dataGridView1.Rows.Clear();
               dataGridView1.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString());
            }
            cmd.Dispose();
            conn.Close();

What I want to happen:

BookID | BookTitle | etc..

100001 | Sociology | etc..
100002 | History | etc..

And this is the datagrid that shows everytime

BookID | BookTitle | etc..

100001 | Sociology | etc..
100002 | History | etc..
100001 | Sociology | etc..
100002 | History | etc..

Upvotes: 1

Views: 2242

Answers (3)

user2230191
user2230191

Reputation: 21

You need to change your query to use DISTINCT, as this will remove duplicates:

SELECT DISTINCT Books.BookID,Books.Title,Books.AuthorID,Authors.AuthorName,Books.ISBN from Books,Authors

Upvotes: 1

Sachin
Sachin

Reputation: 40970

Change your query to fetch DISTINCT records from database and then bind them to datagridview

 query = "SELECT DISTINCT Books.BookID,Books.Title,Books.AuthorID,Authors.AuthorName,
          Books.ISBN from Books,Authors";

Note: This query will make row distinct if there is any column has different value. So it's upto you that which column's value you want to be distinct and would like to display on grid.

one more thing, Use JOIN instead of Cartesian product if possible like this

query = "SELECT DISTINCT Books.BookID,Books.Title,Books.AuthorID,Authors.AuthorName,
         Books.ISBN from Books 
         LEFT OUTER JOIN Authors on Books.AuthorId=Author.AuthorId";

EDIT: Try this for binding

using(SqlReader reader = cmd.ExecuteReader() )
     {
        if (reader.HasRows)
        {  
            DataTable dt = new DataTable();
            dt.Load(reader);
            dataGridView1.DataSource = dt;
        }
     }

Upvotes: 2

DeMama
DeMama

Reputation: 1202

try this

conn.Open();
query = "SELECT Books.BookID,Books.Title,Books.AuthorID,Authors.AuthorName,Books.ISBN from Books,Authors";
cmd = new SqlCommand(query, conn);

SqlDataAdapter da = new SqlDataAdapter(cmd);

dataGridView1.Clear();

da.Fill(dataGridView1);

cmd.Dispose();
conn.Close();

Not sure if this is entirely correct but it may help.

Upvotes: 2

Related Questions