How to update all the autoincrement columns in a DataTable?

Question

I have a DataTable with an "Id" column, which is an identity column in our SQL Server 2005 database. This column has the AutoIncrement property set to true. I don't fill the table with data from the DB, since I use it only for inserts, so it assigns bogus Ids starting from 1.

But after I call the tableAdapter.Update(), I'd like to have in that column the REAL Ids assigned by the database .

For some reason, only the first row gets updated, and all the rest not. This table references itself using a cascading DataRelation (hierarchical structure), and the references to the first row are also updated.

Please tell how do I make all the Ids updated accordingly.

Thanks in advance!

INSERT statement:

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

And here the schema of the Components Table:

Id BIGINT PK, 
ComponentId BIGINT FK, 
OrderNo int, 
SerialNo int

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

Answer 1

Before the execution of Fill method create a datacolumn with autoincrement,

EDIT:

    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;

        adp.Fill(dt);
        dataGridView1.DataSource = dt;
    }
    private void button1_Click(object sender, EventArgs e)
    {
        adp.Update(dt);
    }

Answer 2

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

Try

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

OR

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

Answer 3

The problem was in the mentioned DataRelation. For some reason, because of this DataRelation, the "child" rows were not inserted in the DB and their Ids were not updated to the real Ids, although the DataTable had the "Refresh the Data Table" check on (in Configure/Advanced Settings).

I've removed the DataRelation and did all the work by hand. The following code did the trick for me:

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

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