You must Sign In to post a response.

How will get distinct values from datatable

The forum thread has not been reviewed by Editors yet. Readers are advised to use their best judgement before accessing this thread.
This thread will be reviewed shortly.
If you think this thread contain inappropriate content, please report to webmaster.
Hi

how i will get disctinct values , max and min from datatable

the code is below

dim dt as datatable
Dim drt() As DataRow
drt = dt.Select("distinct Roomtype")


getting error. says that 'System.Data.SyntaxErrorException: Syntax error: Missing operand after 'Roomtype' operator'

datatable contins values
i want the distinct roomtype from datatable
how i will get these values

thanks
Martin


Comments

#49    Author: Mohan  01 Mar 2004 Member Level: Silver   Points : 2

<code>
use these two functions to select distinct

private bool ColumnEqual(object A, object B)
{

if ( A == DBNull.Value && B == DBNull.Value )
return true;
if ( A == DBNull.Value || B == DBNull.Value )
return false;
return ( A.Equals(B) );

}


public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
{
DataTable dt = new DataTable(TableName);
dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

object LastValue = null;
foreach (DataRow dr in SourceTable.Select("", FieldName))
{
if ( LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])) )
{
LastValue = dr[FieldName];
dt.Rows.Add(new object[]{LastValue});
}
}
if (ds != null)
ds.Tables.Add(dt);
return dt;
}


calling code

// your dataset code

DataTable dt=SelectDistinct("[tbl name]", ds.tables["table name"],"[distinct field name]")

//datatable dt now will contain distinct values


</code>

refer to link http://support.microsoft.com/default.aspx?scid=kb;EN-US;326176

#79    Author: Cuckoo John Joseph    05 Mar 2004 Member Level: Gold   Points : 2

A Simple Method to Get the Distinct Values.

Use this method.

Parameters :

Datatable from which the distinct values has to be fetched
colName is the name of the column for the distinct values.

so u can call like this.

<font style="FONT-SIZE: 12px; FONT-FAMILY: 'Courier New'" >
<B>object[] distinctRoomType = GetDistinctValues(dt,"Roomtype");</B>
</font>

Here is the method definition.


<font style="FONT-SIZE: 12px; FONT-FAMILY: 'Courier New'" >
public object[] GetDistinctValues(DataTable dtable,string colName)
{
Hashtable hTable = new Hashtable();
foreach(DataRow drow in dtable.Rows)
{
try
{
hTable.Add(drow[colName],string.Empty);
}
catch{}
}
object[] objArray = new object[hTable.Keys.Count];
hTable.Keys.CopyTo(objArray,0);
return objArray;
}

</font>


Cheers !
John

#34222    Author: geekman  15 Nov 2005 Member Level: Bronze   Points : 2

I found this posting very helpful but the function supplied is too slow because it is designed to throw errors so I thought I would add my own varaition of the c# code:


public object[] GetDistinctValues(DataTable dtable, string colName)
{
Hashtable hTable = new Hashtable();
foreach (DataRow drow in dtable.Rows)
if(!hTable.ContainsKey(drow[colName]))
hTable.Add(drow[colName], string.Empty);
object[] objArray = new object[hTable.Keys.Count];
hTable.Keys.CopyTo(objArray, 0);
return objArray;
}

#146635    Author: suresh  24 Aug 2007 Member Level: Bronze   Points : 2

Hi friends you may find this useful....

public DataRow[] GetDistinctRows(DataTable drows, string colName)
{
DataRow[] objArray = null;
if (drows != null && colName != null && colName != string.Empty)
{
Hashtable hTable = new Hashtable();
List<DataRow> distinctRows = new List<DataRow>();

foreach (DataRow drow in drows.Rows)
if (!hTable.ContainsKey(drow[colName].ToString()))
{
hTable.Add(drow[colName].ToString(), string.Empty);
distinctRows.Add(drow);
}
objArray = new DataRow[distinctRows.Count];
distinctRows.CopyTo(objArray);
}
return objArray;
}

#188572    Author: Ananda Babu Karthikeyan    11 Feb 2008 Member Level: Silver   Points : 2

hello Martin,
Try this code. u'll get distinct table from ur original table(dtTemp).

 dim dt as datatable
Dim dtTemp As DataTable
dt=dtTemp.DefaultView.ToTable(true,"Roomtype")


Regards,
ANANDA BABU KARTHIKEYAN,
Calippus Tech (India) Pvt. Ltd.

#190197    Author: chinnu  14 Feb 2008 Member Level: Bronze   Points : 2

I think the above answer holds true only for framework 2.0 onwards.

People on earlier frameworks can use the following logic. Loop thru the table using-
for each dr as dataRow in dt.select("",columnName)
Keep adding the row to a dummy table(clone of original) when you see a change in the row(columnName) value.

Simple! I think using a hashtable will only slow up things.

#192580    Author: Mr.Rajesh    21 Feb 2008 Member Level: Gold   Points : 2

Query:
"Select Distinct from Table";
this query will give u distinct table;


Regards
Mr.Rajesh
Software Engg.

#192660    Author: Ananda Babu Karthikeyan    21 Feb 2008 Member Level: Silver   Points : 2

hello rajesh ur code will not help. for this

#288526    Author: Sujit Kumar    28 Aug 2008 Member Level: Gold   Points : 4

you can get distinct rows from dataset/datatable
ex:
DataSet ds=new DataSet();
//code to fill dataset from database.

DataTable dt=new DataTable();
dt=ds.Tables["0"].DefaultView.ToTable(true,”columnName”);
//here true means you want distict result.


Regards,
Sujit Kumar

#347483    Author: Gopikrishna  16 Feb 2009 Member Level: Bronze   Points : 3

The above codes need to be slightly modified. They all specify the column name as a string, where as the 'ToTable' method accepts a array of column names.

The code should be,
DataTable dt1=new DataTable();
DataTable dt2=new DataTable();

dt2=dt1.DefaultView.ToTable(true,new string [1]{"columnName"});


This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.