DataView RowFilter - How to select distinct

[VBCrazyCoder]

Is it possible to select distinct in the rowfilter method?

I have something like:

'Create a data view so we can get each items name
        Dim dvwItemNames As New DataView(dtlItems)
        dvwItemNames.RowFilter() = "SELECT DISTINCT Name"

This gives me a syntax error exception. I tried just "Distinct Name" but this had the same result. "Name" is the column name.

Any ideas? I would rather do it this way then looping through the entire table and putting the name in an array list or something.

Thanks.

[DevGrp]

You are getting a syntax error because of the parentesis on rowfilter.

Use this dvwItemNames.RowFilter = "SELECT DISTINCT Name"

[VBCrazyCoder]

That didn't do it. I get "Syntax Error: Missing operand after 'DISTINCT' operator".

[DevGrp]

You are missing the table name from the Distinct statement. You might have to make another trip to the DB to get the Distinct names.

[VBCrazyCoder]

That's what I was trying to avoid, as I already have all of the data in the datatable.

[DevGrp]

Why not just get the distinct names on the first call?

[VBCrazyCoder]

I need all the records, and I also need to get at distinct column x. I didn't want to do 2 queries if I already had the data already. What I am doing now is just looping through the returned results and adding new values for column x into an array (of course I have to loop through the array to make sure it isn't already added). I don't think this is the most efficient way, but it avoids another hit to the database and another datatable in the dataset (which actually might be a better solution).

[TheWingedLion]

Hi,

one thing to mention about the dataview, it doesn't support selecting distinct values, that's why you get syntax error, the only thing it supports is selecting rows based on a certain criteria, like "id = 334" or "name like 'MA%'"

if you want to select the distinct values, you must make a customized function that returns a new table with distinct values only, then you make the dataview on the new table.

You can find full converage for this topic in the following link

http://www.dotnet247.com/247referenc...m/?kbid=325684

[jmcilhinney]

The RowFilter property is equivalent to a WHERE clause from an SQL query, just like the Sort property is equivalent to an ORDER BY clause. There's no DISTINCT in a WHERE clause, only conditions. Also, RowFilter does not support the entire SQL specification. See the help/MSDN topic for DataColumn.Expression for details on what is supported.

[saurintravadi]

use

.defaultview.totable(distinct as boolean, paramarray()) version to get distinct rows from table

[stanav]

use

.defaultview.totable(distinct as boolean, paramarray()) version to get distinct rows from table

Very cool
I've never thought of doing it this route before.

[damoncombs]

use

.defaultview.totable(distinct as boolean, paramarray()) version to get distinct rows from table

How do you use this code snippet? Where do you insert this?

Thanks,
Damon