Delete rows that contains null value from dataTable

Question

I have a DataTable that contains 7800 rows each row contains 3 column that may have Null values if the second column is null i am deleting the entire row the problem is that even when i am deleting the row the DataTable still contain the same amount of row number wish is 7800

the code :

 string query = "Select STORE_NAME, STORE_LATITUDE, STORE_LONGTITUDE "
  +" From stores inner join contact_information on stores.STORE_ID=contact_information.CONTACT_ID "
    +" where CONTACT_TYPE_ID=1 "
      +" AND CONTACT_COUNTRY_ID="+Country
         +" AND CONTACT_CASA_ID="+Casa
            +" AND CONTACT_TOWN_ID="+Town;
    DataTable dt = new SQLHelper(SQLHelper.ConnectionStrings.KernelConnectionString).getQueryResult(query);
    dt = removeNullColumnFromDataTable(dt);

the function :

    public static DataTable removeNullColumnFromDataTable(DataTable dt)
   {
    for (int i = dt.Rows.Count - 1; i >= 0; i--)
      {
       if (dt.Rows[i][1].ToString() == null)
           dt.Rows[i].Delete();
      }
     return dt;
   }

dt.Rows[i][1].ToString() == "") won't work. "" is not the same as null. — Tim
– Tim, Commented Jul 4, 2013 at 7:50

Ahmed KRAIEM · Accepted Answer · 2013-07-04 08:03:35Z

8

public static void RemoveNullColumnFromDataTable(DataTable dt)
{
    for (int i = dt.Rows.Count - 1; i >= 0; i--)
    {
        if (dt.Rows[i][1] == DBNull.Value)
            dt.Rows[i].Delete();
    }
    dt.AcceptChanges();
}

edited Jul 4, 2013 at 8:03

answered Jul 4, 2013 at 7:52

Ahmed KRAIEM

10.4k4 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sora Over a year ago

it delete the row but the DataTable count still the same

Ahmed KRAIEM Over a year ago

I added dt.AcceptChanges(); :)

sqladmin · Accepted Answer · 2013-07-04 08:06:43Z

1

you can change first row to this (for sql server):

string query = "Select STORE_NAME, isnull(STORE_LATITUDE,'') as STORE_LATITUDE, STORE_LONGTITUDE "

and just check for empty string (if field type is char, varchar ... etc..)

dt.Rows[i][1].ToString() == ""

or use isnull(STORE_LATITUDE,0) and check for zero dt.Rows[i][1].ToString() == "0" if field type is numeric

edited Jul 4, 2013 at 8:06

answered Jul 4, 2013 at 7:53

sqladmin

2,2092 gold badges17 silver badges12 bronze badges

3 Comments

Sora Over a year ago

the sql server change the null value to zero so i am kind of stuck with the same issue

sqladmin Over a year ago

dt.Rows[i][1].ToString() == ""

sqladmin Over a year ago

wait - what type of STORE_LATITUDE field ?

meliweb · Accepted Answer · 2023-03-31 16:35:21Z

0

List<DataRow> changedNotNull = (from r1 in changed
                                where r1.ItemArray[0] != DBNull.Value
                                select r1).ToList();

did the trick for me !

answered Mar 31, 2023 at 16:35

meliweb

12 bronze badges

Collectives™ on Stack Overflow

Delete rows that contains null value from dataTable

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related