DataTable ArgumentOutOfRangeException

Question

The code below works exactly as it should when it runs against my DataTable the first time. The data table is loaded, it updates all of the column data specified using the string that is specified. Everything is closed (and presumably cleared) properly.

public void AppendKeyColumn(string appendText, params string[] columnNames)
{               
    string columns = "";

    // Create a list of the columns to use, comma separated.
    for (int column = 0; column < columnNames.Length; column++)
    {
        if ((column + 1) == columnNames.Length)
        {
            columns += columnNames.ElementAt(column);
        }
        else
        {
            columns += columnNames.ElementAt(column) + ", ";
        }
    }

    // SQL Statement for querying the file. Change after FROM for proper table name.
    string sql = "SELECT " + columns + " FROM " + TableName;

    // Using to create a command variable (so it will be properly disposed after block execution).
    using (OleDbCommand cmd = new OleDbCommand(sql, Connection))
    {
        // Open a connection to the data source.
        Connection.Open();

        // Open the datareader, load the datatable with the data in the reader, and then close the reader.
        Data = cmd.ExecuteReader();
        Table.Load(Data);
        Data.Close();

        foreach (string column in columnNames)
        {
            // Run UPDATE statement to append text to end of column name.
            for (int row = 0; row < Table.Rows.Count; row++)
            {
                string sql2 = "UPDATE " + TableName + " SET " + column + " = @newName WHERE " + 
                        column + " = @oldName";

                using (OleDbCommand cmd2 = new OleDbCommand(sql2, Connection))
                {
                    string oldString = Table.Rows[row][0].ToString();                          
                    string newString = oldString.Remove(oldString.Length - 1);
                    newString = newString.Insert(newString.Length, appendText);

                    cmd2.Parameters.AddWithValue("@newName", newString);
                    cmd2.Parameters.AddWithValue("@oldName", oldString);

                    if (!Equals(oldString.Substring(oldString.Length-1, 1), appendText))
                    {
                        cmd2.ExecuteNonQuery();
                    }
                }  
            }
        }

        Table.Clear();
        Connection.Close();
    }
}

My issues lies with when I run this code for the second time. If I call it again on the same table, I get the following error (from this line string newString = oldString.Remove(oldString.Length - 1);). The DataTable should not have any empty strings in the columns that this is being called against. On the first call, the DataTable loads and this works as expected. On the second run, it seems to acknowledge the correct number of rows in the DataTable, however it seems to be physically empty.

InnerException:
System.ArgumentOutOfRangeException: StartIndex cannot be less than zero.
Parameter name: startIndex
    at System.String.Remove(Int32 startIndex)

I'm assuming I'm probably overlooking something basic, but could somebody please point my in the right direction?

Looks like in table one of the column value is null so it will have oldstring.length as 0 hence it is throwing this error ? — Akhilesh, Jun 08 '16 at 18:19

score 0 · Answer 1 · answered Jun 08 '16 at 18:21

0

You're running into empty strings. Without getting too in-depth, one thing you could do to fix this is:

var newString = "";
if(oldString != String.Empty)
{
    newString  = oldString.Remove(oldString.Length - 1);
}

answered Jun 08 '16 at 18:21

Buck A. Mayzing

128
8

Sorry. I'll edit my post to clarify, however there are no empty strings in the columns that I'm running this function against. Rather there shouldn't be any empty strings. On the first run, there are actually records in the DataTable. On the second run, it shows the right number of rows...but it doesn't seem to actually be loading the values? – Jun 08 '16 at 18:25

score 0 · Accepted Answer · answered Jun 08 '16 at 18:40

0

Your problem seams to be caused by the Table object, which i suppose it is an DataTable, so if you add Table = new DataTable; before the line Table.Load(Data);

And here it's a way to make a good cleanup
string sql = String.Format("SELECT ({0}) FROM {1};", String.Join(", ", columnNames), TableName);

answered Jun 08 '16 at 18:40

Allan Leon Alencar Leitão

94
1
8

This fixed it! Why exactly do I need to instantiate a new DataTable before it can re-use it? I was under the assumption I could create it once in my object and then just Clear() and Load() it again... – Jun 08 '16 at 18:50
1

DataTable is an temperamental component when comes to garbage collection. What happen is that the DataTable believes is already loaded, so it does not load the second time. – Allan Leon Alencar Leitão Jun 08 '16 at 23:50

DataTable ArgumentOutOfRangeException

2 Answers2