8

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.

John Smith
  • 2,291
  • 4
  • 22
  • 33
  • I have already answered it but it would be great it you can put a break point on the line dts.rows.add(row) and check it out that does row.A is carrying. – khalid khan May 01 '14 at 14:59
  • You may want to check if IDENTITY_INSERT is ON or OFF for your table. – TaW May 01 '14 at 17:26
  • ColumnModel.IsIdentity (EF) and DataColumn.AutoIncrement (ADO Datatable) are two different things in two different frameworks. IsIdentity is saying the database will provide the value. AutoIncrement is saying the datatable object will provide the value. – ileff May 01 '14 at 23:40

4 Answers4

10

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);
Raju Padhara
  • 687
  • 1
  • 7
  • 20
  • @John also see this [help](http://msdn.microsoft.com/en-us/library/system.data.datacolumn.autoincrement.aspx) – Raju Padhara May 01 '14 at 14:58
  • 9
    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. – John Smith May 01 '14 at 15:42
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.

bluish
  • 26,356
  • 27
  • 122
  • 180
khalid khan
  • 135
  • 1
  • 9
  • 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. – John Smith May 01 '14 at 15:00
  • DataTable dt = new DataTable(); dt.Columns.Add("A",System.Type.GetType("System.Int32")).AutoIncrement = true;dt.Columns.Add("B"); dt.Rows.Add(null, "abc"); dt.Rows.Add(null, "xyz"); I checked this and it worked in visual studio. – khalid khan May 01 '14 at 15:23
2

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.

Gary
  • 99
  • 1
  • 9
0

The way that I do it is 

public void AppendtodtA(int num1, doubledouble1, string string1)
{
    object[] RowArray = new object[] {null,(int)num1, (double) doubledouble1, (string) string1}
    DataRow CurrentRow = dtA.NewRow();
    CurrentRow.ItemArray = RowArray;
    dtA.Rows.Add(CurrentRow);
}

use ItemArray property and leave a null in the place of the autoincremented column

AppendtodtA(MyNumber,MyDouble,MyString);

Then just call the method with your variables.

Carlos
  • 1
  • 1