DataView RowFilter - How to select distinct

Is it possible to select distinct in the rowfilter method?

I have something like:

VB Code:

'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.

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

Use this dvwItemNames.RowFilter = "SELECT DISTINCT Name"

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

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.

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

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

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).

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

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.

use

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

Quote:

---

Originally Posted by saurintravadi

use

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

---

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

Quote:

---

Originally Posted by saurintravadi

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