You must Sign In to post a response.
  • Category: .NET

    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.

    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

  • #49
    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)
    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;EN-US;326176

  • #79
    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>

    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)
    object[] objArray = new object[hTable.Keys.Count];
    return objArray;


    Cheers !

  • #34222
    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)
    hTable.Add(drow[colName], string.Empty);
    object[] objArray = new object[hTable.Keys.Count];
    hTable.Keys.CopyTo(objArray, 0);
    return objArray;

  • #146635
    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);
    objArray = new DataRow[distinctRows.Count];
    return objArray;

  • #188572
    hello Martin,
    Try this code. u'll get distinct table from ur original table(dtTemp).
     dim dt as datatable
    Dim dtTemp As DataTable

    Calippus Tech (India) Pvt. Ltd.

  • #190197
    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"",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
    "Select Distinct from Table";
    this query will give u distinct table;

    Software Engg.

  • #192660
    hello rajesh ur code will not help. for this

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

    DataTable dt=new DataTable();
    //here true means you want distict result.

    Sujit Kumar

  • #347483
    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.