Click here to Skip to main content
11,414,670 members (67,693 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Hi Guys, I have problem I want to remove empty rows in my datatable that before binding it to my datatagridview. I get the source from a sql table.

this is what I have:
If String.IsNullOrEmpty(dt.Rows(i)(col.HeaderText).ToString()) = False Then
            isEmpty = False
            'Exit For
          ElseIf Not String.IsNullOrEmpty(dt.Rows(i)(col.HeaderText).ToString()) = False Then
            isEmpty = True
            If isEmpty = True Then
              dt.Rows.RemoveAt(i)
              i -= 1
            End If
          End If

this code removes if a certain column is is empty, I want if the entire row is empty to be removed.

Any help would be appreciated
Posted 25-Aug-11 23:23pm
Comments
digimanus at 26-Aug-11 4:28am
   
prevent retrieving empty rows via your query
digimanus at 26-Aug-11 4:38am
   
simplify your code to:
If String.IsNullOrEmpty(dt.Rows(i)(col.HeaderText).ToString()) Then
dt.Rows.RemoveAt(i)
i -= 1
End If
archanakumari at 26-Aug-11 8:01am
   
Correct It will work
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Rather than deleting rows, it would (as digimanus says) be better to not retrieve empty rows to start with.
However, surely it is just a case of modifying your code to loop through all rows and check for empty, rather than using the simple if that you currently have?

What part of this is giving you problems?
  Permalink  
Comments
Gericke Hoeksema at 26-Aug-11 5:50am
   
The thing is when I create the table I create a PK column that is auto increment, then before binding it to datatagridview I remove the PK column and then I want to remove the empty rows. After that I simply just add PK again.

If there is a easier way to do so please let me know.

the problem is some of the cells is empty then the whole row get deleted and I want the entire row that is empty to be deleted.
OriginalGriff at 26-Aug-11 5:54am
   
Why the heck are you doing that? What does that achieve? Apart from giving SQL server loads of unnecessary work to do?
Gericke Hoeksema at 26-Aug-11 6:01am
   
First of all I import data from a csv file into a sql table and then I use the table to bind it to my datatagrid. all the modifications I am doing is on the datatable not in sql
OriginalGriff at 26-Aug-11 6:09am
   
No, what I meant was "Why the heck are you deleting the PK?"

I think you need to go back a bit, and read up on databases a bit more before you continue, because what you are doing sounds like a very, very Bad Idea...
Gericke Hoeksema at 26-Aug-11 6:48am
   
I know it is not the effiecient way to do so but since PK will always have a value I want it removed so that I can check if the entire row is blank and removed it. I did a google seacrh but none of it is what I am looking for.
What I am doing is I am looping through the data columns and rows to do some modifications.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Please check this piece of code:

        'Dim dt As New DataTable

        Dim valuesarr As String = String.Empty
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim lst As New List(Of Object)(dt.Rows(i).ItemArray)
            For Each s As Object In lst   
                valuesarr &= s.ToString
            Next
            If String.IsNullOrEmpty(valuesarr) Then
                'Remove row here, this row do not have any value 
            End If
        Next

This code will remove all completely blank rows from a datatable.
  Permalink  
v2
Comments
Gericke Hoeksema at 26-Aug-11 9:24am
   
Thank you this is the code what I was looking for but now I have a problem, I get 'Index Out Of Bound Error' cause everytime I remove the empty row the index is no longer the same. Any ideas how to fix this?
I use "dt.Rows.RemoveAt(i)" to remove the balnk row
Shahan Ayyub at 26-Aug-11 9:38am
   
did you try setting:
i=i-1
after deletion?
Dave Kreskowiak at 26-Aug-11 12:59pm
   
Easy. Instead of starting your loop at 0 and counting up to .Count-1, you start at Count-1 and count down to 0.

For i As Integer = dt.Rows.Count - 1 To 0 Step -1
Gericke Hoeksema at 30-Aug-11 3:50am
   
thank you so much for the help I appreciate it.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

for (int h = 0; h < Ds.Tables[0].Rows.Count; h++)
{
if(Ds.Tables[0].Rows[h].IsNull(0)==true)
{
Ds.Tables[0].Rows[h].Delete();
}
}
  Permalink  
Comments
bluewatersoft at 20-Nov-12 10:50am
   
This will not work as you are deleting row from DS.Table[0] while you are looping through DS.table[0].Row count...
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Can I have a look at your query which is fetching all these rows? I think that's where u should look for the solution.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

To remove empty row from datatable.
click here
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

If you are using ODBC connection to retrieve the data, use the OdbcConnection.GetSchema("Columns") option to retrieve the columns list.

Build your own filter condition to exclude the unwanted records.

Code Sample:

private static DataTable ReadExcelData(string filePath)
        {
            string excelConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\T\\Test.xls;";
            excelConnectionString = excelConnectionString.Replace("{fileName}", filePath);
 
            OdbcConnection odbcConnection = new OdbcConnection();
            OdbcCommand command = new OdbcCommand();
            OdbcDataAdapter adapter = null;
            DataSet resultSet = new DataSet();
            DataTable returnTable = new DataTable();
            DataTable dtExcelsheetName = new DataTable();
            try
            {
                odbcConnection.ConnectionString = excelConnectionString;
                odbcConnection.Open();
 
                #region For Getting the Column Names List and Building the Filter Condition
                
                dtExcelsheetName = odbcConnection.GetSchema("Columns");
 
                StringBuilder filterCondition = new StringBuilder();
                foreach (DataRow column in dtExcelsheetName.Rows)
                {
                    filterCondition.Append(column["Column_Name"] + " <> ''");
                    filterCondition.Append(" or ");
                }
 
                filterCondition = filterCondition.Remove(filterCondition.Length - 3, 3);
 
                #endregion
 
                command.CommandText = "Select * from [" + dtExcelsheetName.Rows[0]["TABLE_NAME"].ToString() + "] where (" + filterCondition.ToString() + ");";
                
                command.CommandType = CommandType.Text;
                command.Connection = odbcConnection;
 
                adapter = new OdbcDataAdapter(command);
                adapter.FillSchema(resultSet, SchemaType.Source);
                adapter.Fill(resultSet);
 
                if (resultSet != null)
                {
                    if (resultSet.Tables.Count > 0)
                    {
                        returnTable = resultSet.Tables[0].Copy();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (resultSet != null)
                {
                    resultSet.Dispose();
                    resultSet = null;
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                    adapter = null;
                }
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (odbcConnection.State == ConnectionState.Open)
                    odbcConnection.Close();
                odbcConnection.Dispose();
                odbcConnection = null;
            }
            return (returnTable);
        }
  Permalink  
v3
Comments
Nelek at 10-Jun-13 4:26am
   
It is ok that you want to help, but... did you realize that the question is from 2011 and marked as already solved?.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 9,130
1 OriginalGriff 7,477
2 Maciej Los 3,710
3 Abhinav S 3,298
4 Peter Leow 3,084


Advertise | Privacy | Mobile
Web04 | 2.8.150427.4 | Last Updated 10 Jun 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100