ReadOnlyException DataTable DataRow "Column X is read only."

Question

I've got a short piece of code that originally created an SqlDataAdapter object over and over.

Trying to streamline my calls a little bit, I replaced the SqlDataAdapter with an SqlCommand and moved the SqlConnection outside of the loop.

Now, whenever I try to edit rows of data returned to my DataTable, I get a ReadOnlyException thrown that was not thrown before.

NOTE: I have a custom function that retrieves the employee's full name based on their ID. For simplicity here, I used "John Doe" in my example code below to demonstrate my point.

ExampleQueryOld works with the SqlDataAdapter; ExampleQueryNew fails with the ReadOnlyException whenever I try to write to an element of the DataRow:

• ExampleQueryOld

This works and has no issues:

public static DataTable ExampleQueryOld(string targetItem, string[] sqlQueryStrings) {
  DataTable bigTable = new DataTable();
  for (int i = 0; i < sqlQueryStrings.Length; i++) {
    string sqlText = sqlQueryStrings[i];
    DataTable data = new DataTable(targetItem);
    using (SqlDataAdapter da = new SqlDataAdapter(sqlText, Global.Data.Connection)) {
      try {
        da.Fill(data);
      } catch (Exception err) {
        Global.LogError(_CODEFILE, err);
      }
    }
    int rowCount = data.Rows.Count;
    if (0 < rowCount) {
      int index = data.Columns.IndexOf(GSTR.Employee);
      for (int j = 0; j < rowCount; j++) {
        DataRow row = data.Rows[j];
        row[index] = "John Doe"; // This Version Works
      }
      bigTable.Merge(data);
    }
  }
  return bigTable;
}

• ExampleQueryNew

This example throws the ReadOnlyException:

public static DataTable ExampleQueryNew(string targetItem, string[] sqlQueryStrings) {
  DataTable bigTable = new DataTable();
  using (SqlConnection conn = Global.Data.Connection) {
    for (int i = 0; i < sqlQueryStrings.Length; i++) {
      string sqlText = sqlQueryStrings[i];
      using (SqlCommand cmd = new SqlCommand(sqlText, conn)) {
        DataTable data = new DataTable(targetItem);
        try {
          if (cmd.Connection.State == ConnectionState.Closed) {
            cmd.Connection.Open();
          }
          using (SqlDataReader reader = cmd.ExecuteReader()) {
            data.Load(reader);
          }
        } catch (Exception err) {
          Global.LogError(_CODEFILE, err);
        } finally {
          if ((cmd.Connection.State & ConnectionState.Open) != 0) {
            cmd.Connection.Close();
          }
        }
        int rowCount = data.Rows.Count;
        if (0 < rowCount) {
          int index = data.Columns.IndexOf(GSTR.Employee);
          for (int j = 0; j < rowCount; j++) {
            DataRow row = data.Rows[j];
            try {
              // ReadOnlyException thrown below: "Column 'index'  is read only."
              row[index] = "John Doe";
            } catch (ReadOnlyException roErr) {
              Console.WriteLine(roErr.Message);
            }
          }
          bigTable.Merge(data);
        }
      }
    }
  }
  return bigTable;
}

Why can I write to the DataRow element in one case, but not in the other?

Is it because the SqlConnection is still open or is the SqlDataAdapter doing something behind the scene?

Dennard

There are a few users on here that don't like me, I guess. – Dennard

It's probably the DataTable.Load(IReader) setting the column as readonly. Can you just set ReadOnly to false for all the columns? – Ding

Maybe! ...if I knew where to find such a ReadOnly property. :) – Dennard

Since DataTable, DataRow, and DataRow item have no ReadOnly property, I tried modifying the DataTable.Load(IReader) command with DataTable.Load(IReader, LoadOption.Upsert), DataTable.Load(IReader, LoadOption.Upsert) (the default), and DataTable.Load(IReader, LoadOption.OverwriteChanges), but I got the same results: ReadOnlyException. – Dennard

foreach (System.Data.DataColumn col in tab.Columns) col.ReadOnly = false; – Tervalent

Answer 1

using DataAdapter.Fill does not load the database schema, which includes whether a column is a primary key or not, and whether a column is read-only or not. To load the database schema, use DataAdapter.FillSchema, but then that's not your questions.

using DataReader to fill a table loads the schema. So, the index column is read-only (probably because it's the primary key) and that information is loaded into the DataTable. Thereby preventing you from modifying the data in the table.

I think @k3b got it right; by setting ReadOnly = false, you should be able to write to the data table.

foreach (System.Data.DataColumn col in tab.Columns) col.ReadOnly = false; 

Cowans

Mr. Pieng: A follow up, after a year. Is there a way to unload or discard the schema after the DataReader retrieves the data? – Dennard

@jp2code You can probably create a new Table and copy just the data without the schema info. If it's only a specific property that you want to change such as the read-only flag, you might be better off just setting the value of that property. – Cowans

I already used my up votes for today, but virtual +1 for this answer. I was fighting with this for couple of hours before I found out that one of columns was read only. Now my little DataGridView wok like a charm! :) Thanks! – Despotic

Answer 2

I kept getting the same exception while trying different approaches. What finally worked for me was to set the column's ReadOnly property to false and change the value of the Expression column instead of row[index] = "new value";

Insightful

Answer 3

In VB, don't pass a read-only DataRow Item by reference

The likelihood that you'll run into this is low, but I was working on some VB.NET code and got the ReadOnlyException.

I ran into this issue because the code was passing the DataRow Item to a Sub ByRef. Just the act of passing-byref triggers the exception.

Sub Main()

    Dim dt As New DataTable()
    dt.Columns.Add(New DataColumn With {
        .ReadOnly = True,
        .ColumnName = "Name",
        .DataType = GetType(Integer)
    })

    dt.Rows.Add(4)

    Try
        DoNothing(dt.Rows(0).Item("Name"))
        Console.WriteLine("All good")
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try 

End Sub

Sub DoNothing(ByRef item As Object) 
End Sub 

Output

Column 'Name' is read only

C-sharp

You can't even write code like this in C# . DoNothing(ref dt.Rows[0].Item["Name"]) gives you a compile time error.

Kowalczyk

Answer 4

open the yourdataset.xsd file of your data set. click on the table or object and click on the specific column which readonly property need to be changed. its simple solutions.

Oribel