2

I am trying to get the values from column 18, Test to uppercase after the upload of the excel file but without any succes.

enter image description here

How exactly can I reach the values that are in a DataTable?

This is where I fill the DataTable with the values from the excel file:

 using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
        {
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }

I would like to have all the values in 18 to be uppercase.

Bayern
  • 330
  • 3
  • 20

2 Answers2

6

Loop through all rows making the 18th columns data UpperCase:

foreach (DataRow row in dataTable.Rows)
{
    row["Test"] = row["Test"].ToString().ToUpper();
}
Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
Microsoft DN
  • 9,706
  • 10
  • 51
  • 71
  • This can also be done with an index. row[17] = row[17].ToString().ToUpper(); – oppassum Jul 07 '15 at 13:18
  • 1
    @oppassum Bui if in any case column/row index changes, need to update the code as well. Better to use 'column name' . – Microsoft DN Jul 07 '15 at 13:19
  • agreed, but I wasn't 100% sure "Test" was actually his column name :-) – oppassum Jul 07 '15 at 13:20
  • @MicrosoftDN Yes that works, but i had to use row["18"] instead of "Test" that gave the error "Test" isn't part of the table. Thank you! – Bayern Jul 07 '15 at 13:24
  • Probably your actual column name is something different. But it is a good practice to use column name instead of indexes as indexes may change later but column name frequently. :) – Microsoft DN Jul 07 '15 at 13:26
3

You can add an Event Handler for the RowChanged event of the DataTable.

using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
    DataTable dataTable = new DataTable();
    dataTable.RowChanged += changedRow;
    adapter.Fill(dataTable);
    return dataTable;
}

void changedRow(object sender, DataRowChangeEventArgs e)
{
     if(e.Action == DataRowAction.Add)
         e.Row[17] = e.Row[17].ToString().ToUpper();
}

Probably some check about null values should be planned also.

Of course this avoid a second loop on the data because this event will be called while loading the datatable within the Fill call.

Notice that you should use RowChanged event because RowChanging event doesn't allow to modify the row values. Also I have used a fixed index for the column because I don't know if your connection string contains the configuration to activate column names in the first row of your excel sheet. However if your column name is really named "18" then you could replace the numeric index with the string "18".

Steve
  • 213,761
  • 22
  • 232
  • 286
  • I'm too lazy to try it at 11:30 PM, but does `if(e.Action == DataRowAction.Add)` indicate you expect this event to fire outside of the data population? – Jeremy Thompson Jul 07 '15 at 13:31
  • Yes it is possible. It is an event that will be called _Occurs after a DataColumn value or the RowState of a DataRow in the DataTable has been changed successfully._ And [DataRowAction](https://msdn.microsoft.com/en-us/library/system.data.datarowaction(v=vs.110).aspx) has many values for every possible action on the row. (For example during the fill call the event will be called 3 times for Add, Change and Commit) – Steve Jul 07 '15 at 13:35
  • There is something about this that feels right ie **this method is optimised**, but I only commented because there is something about this that doesn't *Instinctively feel right* and that is to release the event, when your done to avoid memory leaks. + 1 Sorry Steve, code review habits. I'm sure its good either way :) – Jeremy Thompson Jul 07 '15 at 13:37
  • Just of you want to avoid this behavior if you add other rows outside the Fill method. But why? There is no memory leak if you leave the event handler and if the table is disposed.... More, if you add a row outside the Fill then the rule. _Column 18 should be upper case_ should be remembered and you have two or more place where to apply ToUpper code. With the event handler you are more Object Oriented. – Steve Jul 07 '15 at 13:44
  • ok, thanks for making me look into this... I prefer to be explicit in my code.. I guess it is just over-hyped: http://stackoverflow.com/a/4526840/495455 – Jeremy Thompson Jul 07 '15 at 13:49