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

Author: Mohan01 Mar 2004 Member Level: Silver   Points : 2


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




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

Author: Cuckoo John Joseph05 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.


object[] distinctRoomType = GetDistinctValues(dt,"Roomtype");


Here is the method definition.



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;
}




Cheers !
John

Author: geekman15 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;
}

Author: suresh24 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;
}

Author: Ananda Babu Karthikeyan11 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.



Author: chinnu14 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.

Author: Mr.Rajesh21 Feb 2008 Member Level: Gold   Points : 2

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

Author: Ananda Babu Karthikeyan21 Feb 2008 Member Level: Silver   Points : 2

hello rajesh ur code will not help. for this

Author: Sujit Kumar28 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.

Author: Gopikrishna16 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"});


Sign In to post your comments