locked
Datatable Select method - blank column only RRS feed

  • Question

  • How do i use Datatable.select method to select only those rows where a specific column value is blank?
    Friday, September 9, 2011 7:47 AM

Answers

  • IS NULL does not exist, but there's an ISNULL function:

    For Each dr In dv.Table.Select("ISNULL(MyColumn, '') = ''")


    Armin
    • Marked as answer by Amey0072 Saturday, September 10, 2011 5:07 AM
    Friday, September 9, 2011 4:05 PM

All replies

  • If it is really a blank column than it is '' (double single quote), however not seldom those columns are null allowed so you have to use also null.

    http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx


    Success
    Cor
    • Proposed as answer by Heslacher Friday, September 9, 2011 8:36 AM
    Friday, September 9, 2011 7:56 AM
  • If you mean blank like NULL then the following will do the job:


    'Visual Basic 2008 - .net 3.5 - Any CPU
            Dim dt As New DataTable
            Dim dtr As DataRow()
            Dim oRow As DataRow
            dt.Columns.Add("Test")
    
            oRow = dt.NewRow
            oRow.Item(0) = 1
            dt.Rows.Add(oRow)
    
            oRow = dt.NewRow
            oRow.Item(0) = 2
            dt.Rows.Add(oRow)
    
            oRow = dt.NewRow
            oRow.Item(0) = Nothing
            dt.Rows.Add(oRow)
    
            dtr = dt.Select("Test IS NULL")
    

     


    Hannes

    If you have got questions about this, just ask.

    In a perfect world,
    users would never enter data in the wrong form,
    files they choose to open would always exist
    and code would never have bugs.

    C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Friday, September 9, 2011 8:10 AM
  • Hi All

    I checked the value in that column it is "".

    but when i use Datatable.Select("Column_Name =""""") i get the following error

     

    Cannot interpret token '"' at position 15.

     

    is there a different way to handle ""? 


    • Edited by Amey0072 Friday, September 9, 2011 8:31 AM
    Friday, September 9, 2011 8:31 AM
  • Just use single quotes
    Hannes

    If you have got questions about this, just ask.

    In a perfect world,
    users would never enter data in the wrong form,
    files they choose to open would always exist
    and code would never have bugs.

    C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Friday, September 9, 2011 8:34 AM
  • Hi Hannes

    I tried using single quotes,but it does not work

     

    here is what i am trying to do,I have a grid-view which needs to be converted to data-table containing only those rows where MyColumn is blank.

     

     

    If MyGridView.RowCount > 0 Then

    Dim dv As DataView = DirectCast(MyGridView.DataSource, DataView)

    Dim dt As DataTable = dv.Table.Clone()

    dt.Clear()

       For Each dr In dv.Table.Select("MyColumn =''")

                    dt.ImportRow(dr)

                Next

    End If

     

     

     

    Friday, September 9, 2011 9:23 AM
  • Amey,

    did you try my code ?


    Hannes

    If you have got questions about this, just ask.

    In a perfect world,
    users would never enter data in the wrong form,
    files they choose to open would always exist
    and code would never have bugs.

    C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Friday, September 9, 2011 9:33 AM
  • Hi Hannes,

    Yes i tried your code with  dtr = dt.Select("Test IS NULL") ,but it did not work.

    I guess it is because the column contains ""(double quotes) and is not NULL.

    Friday, September 9, 2011 9:37 AM
  • did you look at the link I gave you for the expression as first reply on your question. 

    Than you would have seen that it would be 

    dt.Select"Test Null or Test = ''"

    And not something you think yourself it should be. 

    Be aware the expression has nothing to do with SQL trajnsact code. 

    Take a look at the link I showed you in my first reply where everything is described around the DataTable Select Expression.

    Take next time a look to all replies you got on your question and not only the last one.


    Success
    Cor
    Friday, September 9, 2011 9:49 AM
  • Just use:


    'Visual Basic 2008 - .net 3.5 - Any CPU
    dtr = dt.Select("Test='""'")
    

     


    Hannes

    If you have got questions about this, just ask.

    In a perfect world,
    users would never enter data in the wrong form,
    files they choose to open would always exist
    and code would never have bugs.

    C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Friday, September 9, 2011 10:00 AM
  • Hi Hannes,

    No it did not work.

    Friday, September 9, 2011 10:29 AM
  • I din`t take a look at the gridview, so using the code below will fix your problem:


    'Visual Basic 2008 - .net 3.5 - Any CPU
    dtr = dt.Select("Test='""""'")
    

     


    Hannes

    If you have got questions about this, just ask.

    In a perfect world,
    users would never enter data in the wrong form,
    files they choose to open would always exist
    and code would never have bugs.

    C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Friday, September 9, 2011 10:42 AM
  • IS NULL does not exist, but there's an ISNULL function:

    For Each dr In dv.Table.Select("ISNULL(MyColumn, '') = ''")


    Armin
    • Marked as answer by Amey0072 Saturday, September 10, 2011 5:07 AM
    Friday, September 9, 2011 4:05 PM
  • Armin,

    regarding the "IS NULL", did you try it by yourself ? I did and it works like a charme.


    Hannes

    If you have got questions about this, just ask.

    In a perfect world,
    users would never enter data in the wrong form,
    files they choose to open would always exist
    and code would never have bugs.

    C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Friday, September 9, 2011 5:54 PM
  • I know it from the past that this does not work. People tried until being told that this is not SQL. Therefore always the ISNULL function has been proposed. Also, IS NULL is not documented.
    Armin
    Friday, September 9, 2011 5:59 PM
  • Hi Armin

    thanks a lot for the answer.

     

     

    Saturday, September 10, 2011 5:10 AM
  • Hi Hannes,

    maybe "IS NULL" has been introduced in FW 4.0? I'm using 3.5 only. Possible that it's not there yet.


    Armin
    Saturday, September 10, 2011 10:36 AM