2

I've checked this question here as well as a number of other links all proposing similar solutions, but when I go distinctTable = dt.DefaultView.ToTable(true, "FILENAME"); I get an error message saying:

A field or property with the name 'LOCATION' was not found on the selected data source.

Now, I have four columns altogether: Location, Folder, Filename, and Status. The values for Folder and Filename I select from the database, but the values for location and status are determined thru C# code. I have no idea why it's saying that the Location column isn't found because it works fine when I omit the above line.

I don't think I can use SQL because some of the records are being generated by user input (i.e. they aren't coming from the database).

I've also tried 

view = new DataView(dt); 
distinctTable = view.ToTable(true, "LOCATION", "FOLDER", "FILENAME", "STATUS");

or just distinctTable = view.ToTable(true, "FILENAME"); for the latter statement but this doesn't seem to do anything - it doesn't throw an exception but it doesn't eliminate duplicate records either.

What am I doing wrong?

Community
  • 1
  • 1
user1985189
  • 669
  • 5
  • 16
  • 25

2 Answers2

3

It reads as if there are two issues:

  1. You get an error when you databind to (I assume) your derived table;
  2. When you try something else, you don't get an error, but you don't get the desired distinct records, either.

The code you post at the top:

distinctTable = dt.DefaultView.ToTable(true, "FILENAME");

... is going to give you a data table with exactly one column: FILENAME. So when you bind to it, you'll get an error if what you bind to is looking for a LOCATION column as well.

The code you post at the bottom:

view = new DataView(dt); 
distinctTable = view.ToTable(true, "LOCATION", "FOLDER", "FILENAME", "STATUS");

... doesn't throw an error because it has LOCATION (and other columns) as part of the table, so the control you bind to is able to find all the columns.

But you say it doesn't remove duplicates. I'm wondering, when you say that you want to remove duplicates, but show code where you're only specifying one column for output, whether what you want is not to filter out exact duplicates of the entire record, but filter out records that have the same FILENAME value but different values of the other columns.

You can't (as far as I know) do that with DataView.ToTable. But you can do it with LINQ:

DataTable distinctTable = dt.AsEnumerable()
                            .GroupBy(r=> r.Field<string>("FILENAME"))
                            .Select(g=>g.First())
                            .CopyToDataTable();
Ann L.
  • 13,760
  • 5
  • 35
  • 66
  • Yes that's right I want to filter based on filename only. It doesn't matter if the other columns are different. I tried your LINQ code but it's giving me an error on the first part: dt.AsEnumerable() - says it does not contain definition for AsEnumerable – user1985189 Feb 25 '13 at 16:47
  • 1
    Try adding the assembly `System.Data.DataSetExtensions` to your project. – Ann L. Feb 25 '13 at 16:51
  • I tried but looks like I don't have that assembly? It's telling me there is no DataSetExtensions under System.Data... – user1985189 Feb 25 '13 at 16:54
  • 1
    Maybe I was unclear? I mean, add a reference to the assembly (System.Data.DataSetExtensions.dll) to the project. ETA: what version of .NET are you on? I was assuming 4.0. – Ann L. Feb 25 '13 at 16:57
  • yeah it's 4.0. Not sure how to do that tbh, I'm very new to VS – user1985189 Feb 25 '13 at 17:00
  • 2
    Ah, OK. In the Solution Explorer window, in your project, right-click on the References folder. Pick "Add Reference". In the .NET tab of the dialog box that opens up, wait a few minutes for everything to load, click on the Component Name column to sort, and then look for an assembly called `System.Data.DataSetExtensions.dll`. Click on it and then click OK. – Ann L. Feb 25 '13 at 17:03
  • Ok thanks. I did though and it says the web site is alrdy referencing that assembly – user1985189 Feb 25 '13 at 17:07
  • Interesting. Do you have a `using System.Data;` statement at the top of your code file? – Ann L. Feb 25 '13 at 17:08
  • WHOOPS! I'm terribly sorry, I gave you the wrong method signature. It would be just `dt.AsEnumerable()`. Nothing about `DataRow`. I'm terribly sorry! I'm adjusting the answer to reflect that. – Ann L. Feb 25 '13 at 17:31
  • I've been assuming `dt` is a `DataTable`. Is that not the case? And you're getting a message saying "'System.Data.DataTable' does not contain a definition for 'AsEnumerable' and no extension method 'AsEnumerable' accepting a first argument of type 'System.Data.DataTable' could be found (are you missing a using directive or an assembly reference?)"? – Ann L. Feb 25 '13 at 18:49
  • yup dt is the datatable. No sorry your linq statement compiles now, but it's giving me the same error as before: "A field or property with the name 'LOCATION' was not found on the selected data source." – user1985189 Feb 25 '13 at 18:51
  • OK, and `dt` is the ORIGINAL table, that had all the columns? And you're binding the result, `rows`, to your GridView? – Ann L. Feb 25 '13 at 19:21
  • I set up my own experiment and found there was one last method call I left out. I'm adding it to the answer now. Hope this helps. – Ann L. Feb 25 '13 at 20:04
0

How about using LINQ?

var items = yourdatatable.AsEnumerable().Distinct();

user826840
  • 1,233
  • 2
  • 13
  • 28
  • well I've tried distinctTable = dt.AsEnumerable().Distinct().CopyToDataTable(); - didn't work. Again, it won't throw an error but it doesn't eliminate duplicates – user1985189 Feb 25 '13 at 16:38
  • Maybe DataRow doesn't implement IEquitable<>. Have you tried this http://stackoverflow.com/questions/14165335/linq-select-distinct-on-datatable-not-working – user826840 Feb 25 '13 at 18:35