c# adding row to datatable which has an auto increment column

Question

I've a datatable, with column A, B, C. I've set column A's "is identity" property to true, but I can't add any row to the table now.

The code I'm trying is this:

dsA.dtA row = dsA.dtA.NewdtARow();

row.B = 1;
row.C = 2;

dsA.dtA.Rows.Add(row);

I'm getting NoNullAllowedException, but I don't understand why. Column A is PK as well. If I tried to set row.A = 5 (or any similiar) I'll get an error when I try to update the datatable saying "cannot insert explicit value for identity column in table when identity_insert is set to off"

How can I solve this issue? It's frustrating.

Solution 1

Do this way. Reference link

DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.AutoIncrement = true;
column.AutoIncrementSeed = 1;
column.AutoIncrementStep = 1;

// Add the column to a new DataTable.
DataTable table = new DataTable("table");
table.Columns.Add(column);

DataRow oRow = table.NewRow();
table.Rows.Add(oRow);

Solution 2

Try one of the these two:

1. Set field values:

   row.A = null;
   row.B = 1;
   row.C = 3;
   

2. Add row to DataTable:

   dtA.Rows.Add(null,1,2);
   

They are both the same just try any of them and it should get you going. Also remember that whenever you want to make a column auto-increment in DataTable then you have to insert null into it.

Solution 3

Open the designer of the dataset xsd file and set the AutoIncrement, AutoIncrementSeed and AutoIncrementStep property of the column A in datatable for an existing column.

Comments

[Raju Padhara]

@John also see this help

[John Smith]

Both method fail. The first one won't even compile, saying cannot convert null to int. Second one simply just fails like my own method with NoNullAllowedException.

[khalid khan]

DataTable dt = new DataTable(); dt.Columns.Add("A",System.Type.GetType("System.Int32")).Auto‌​Increment = true;dt.Columns.Add("B"); dt.Rows.Add(null, "abc"); dt.Rows.Add(null, "xyz"); I checked this and it worked in visual studio.

[John Smith]

But what if I already have the datatable with an existing column that has autoincrement? I just simply want to add a row to that table, that's it.