Sorting DataTable string column, but with null/empty at the bottom

Stefan Steiger picture Stefan Steiger · Feb 7, 2011 · Viewed 12.6k times · Source

I need to sort a DataTable or DataGridView by a column that is a string value, but with null/empty values at the BOTTOM when sorting ASCENDING.

The DataTable is NOT populated by a SQL statement, so no order by.

If I do

DataGridView1.Sort(New RowComparer(System.ComponentModel.ListSortDirection.Ascending))

then it throws an exception, saying that the DataGridView is DataBound, which is correct, but doesn't help me, and I want to keep it databound.

It's .NET 2.0, which means no LINQ available!

Answer

Davide Piras picture Davide Piras · Feb 7, 2011

in some cases you could do this if you have another extra column in your table:

SELECT completed, completed IS NULL AS isnull
FROM TABLE
ORDER BY isnull DESC, completed DESC



Edit:
Like this in VB.NET

        For Each srSearchResult In srcSearchResultCollection

            Try
                dr = dt.NewRow()
                dr("cn") = srSearchResult.GetDirectoryEntry().Properties("cn").Value
                dr("Account") = srSearchResult.GetDirectoryEntry().Properties("sAMAccountName").Value
                dr("Nachname") = srSearchResult.GetDirectoryEntry().Properties("sn").Value
                dr("Vorname") = srSearchResult.GetDirectoryEntry().Properties("givenname").Value
                dr("Mail") = srSearchResult.GetDirectoryEntry().Properties("mail").Value
                dr("HomeDirectory") = srSearchResult.GetDirectoryEntry().Properties("homedirectory").Value
                dr("LogonScript") = srSearchResult.GetDirectoryEntry().Properties("scriptPath").Value

                dr("IsNull") = String.IsNullOrEmpty(dr("Nachname").ToString())

                dt.Rows.Add(dr)
            Catch ex As Exception

            End Try

        Next srSearchResult
dt.DefaultView.Sort = "IsNull ASC, Nachname ASC"