Click here to Skip to main content
15,886,518 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
VB
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
Comments
Herman<T>.Instance 26-Aug-11 4:28am    
prevent retrieving empty rows via your query
Herman<T>.Instance 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
Anil Honey 206 26-Aug-11 8:01am    
Correct It will work

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?
 
Share this answer
 
Comments
Gericke Hoeksema 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 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 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 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 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.
Please check this piece of code:

VB
'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.
 
Share this answer
 
v2
Comments
Gericke Hoeksema 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 26-Aug-11 9:38am    
did you try setting:
i=i-1
after deletion?
Dave Kreskowiak 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 30-Aug-11 3:50am    
thank you so much for the help I appreciate it.
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();
}
}
 
Share this answer
 
Comments
bluewatersoft 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...
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.
 
Share this answer
 
To remove empty row from datatable.
click here
 
Share this answer
 
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:

C#
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);
        }
 
Share this answer
 
v3
Comments
Nelek 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?.
I am not sure if my code will work for DataTABLE. I am using the following code with dataSET. Please Tweak as necessary and repost here for others to use.

VB
Public Function DeleteBlankRowsfromDataset(ByRef Dtset As DataSet) As Boolean
        Try
            Dtset.Tables(0).AsEnumerable().Where(Function(row) row.ItemArray.All(Function(field) field Is Nothing Or field Is DBNull.Value Or field.Equals(""))).ToList().ForEach(Sub(row) row.Delete())
            Dtset.Tables(0).AcceptChanges()
            DeleteBlankRowsfromDataset = True
        Catch ex As Exception
            MsgBox("Deleting Blank Records in Dataset Failed")
            DeleteBlankRowsfromDataset = False
        End Try

    End Function
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900