How to update all autoincrement columns in DataTable?

I have a DataTable with a column "Id" which is the identity column in our SQL Server 2005 database. In this column, the AutoIncrement property is set to true. I am not populating the table with data from the DB as I only use it for inserts, so it assigns false IDs starting at 1.

But after calling tableAdapter.Update (), I would like to have the REAL IDs assigned by the database in this column.

For some reason, only the first row is updated and everything else is not. This table is referenced by itself using a cascading DataRelation (hierarchical structure) and also references to the first row.

Please tell me how I can make all ids update accordingly.

Thanks in advance!

INSERT statement:

INSERT INTO Components (ComponentId, OrderNo, SerialNo) 
VALUES (@ComponentId, @OrderNo, @SerialNo) 


And here is the schematic of the component table:

ComponentId BIGINT FK, 
OrderNo int, 
SerialNo int 


Note that the Id column name is "Id", "ComponentId" is the FK reference column.


source to share

4 answers

The problem was the mentioned DataRelation. For some reason, due to this DataRelation, the "child" rows were not inserted into the DB, and their ids were not updated to real ids, although the "Update data table" check was enabled in the DataTable (in the "Configuration / Advanced Settings" ).

I removed the DataRelation and did all the work manually. The following code did the trick for me:

ComponentsTableAdapter cta = new ComponentsTableAdapter();                    
foreach (UpdaterDataSet.ComponentsRow r in uds.Components.Rows)
   long origId = r.Id;

   foreach (UpdaterDataSet.ComponentsRow s in uds.Components.Rows)
      if (s.Id != r.Id && !s.IsComponentIdNull() && s.ComponentId == origId)
         s.ComponentId = r.Id;




It was very simple. You just set Column.AutoIncrementSeed and Column.AutoIncrementStep BOTH to "-1". Thus, newlines that have been added will have IDs -1, -2, etc. Then either your stored Procs or TSQL code will simply be:

    Col1 = @Col1
    Col2 = @Col2
WHERE (ID = @ID) -- If this is -1, -2, etc, it won't update

    INSERT INTO Table(Col1, Col2) VALUES(Col1, Col2)
    SET @ID = SCOPE_IDENTITY();  -- @ID would now change from -1 to 1, 2, 3, etc.


However, Microsoft, in its deepest wisdom, turned all of this for the worse with ADO.NET 4.0. I'm not sure when it changed, but they now have a basic private field in the AutoIncrement columns that stores the current value of that column internally. So let's say in the database the last inserted value is as "52", well, that will be hidden inside the AutoIncrement column. This causes HUGE problems with multi-user applications and I can no longer use the "-1" trick as it starts counting back from the last AutoIncrement value. I even tried to think about it to change it, and it didn’t work. I hate that Microsoft broke this in ADO.NET and yet they stick to their mantra "No, we won't fix bug X [which won't really affect users].but we'll be implementing something completely new that will mess up all the code you've ever gotten to work. "



Before executing the Fill method, create a datacolumn with auto-increments,


    SqlConnection cn = new SqlConnection(@"Connection_String");
    SqlDataAdapter adp;
    SqlCommandBuilder cb;
    DataTable dt;

    private void Form3_Load(object sender, EventArgs e)
        adp= new SqlDataAdapter("select * from temp", cn);
        cb = new SqlCommandBuilder(adp);
        dt = new DataTable();
        dt.Columns.Add("SrNo", typeof(int));
        dt.Columns[0].AutoIncrement = true;
        dt.Columns[0].AutoIncrementSeed = 1;
        dt.Columns[0].AutoIncrementStep = 1;

        dataGridView1.DataSource = dt;
    private void button1_Click(object sender, EventArgs e)




You have to return Identity back from SQL. But I'm not sure about the exact syntax anymore


INSERT INTO Components (ComponentId, OrderNo, SerialNo) 
VALUES (@ComponentId, @OrderNo, @SerialNo);



INSERT INTO Components (ComponentId, OrderNo, SerialNo) 
VALUES (@ComponentId, @OrderNo, @SerialNo);




All Articles