insert row into table with autoincrement primary key

insert row into table with autoincrement primary key

• Nick Darnell

  Yes it should update automatically. In fact, it is recommended to also set this column to ReadOnly.

  Are you setting the AutoIncrementStep and AutoIncrementSeed properties for the column

  Steve

  
  

• Ron L

  Hi,

  I just tried this code:

  DataTable dt = new DataTable();
  DataColumn dc = dt.Columns.Add("Field1");
  dc.AutoIncrement = true;
  dc.AllowDBNull = false;
  dt.Columns.Add("Field2");
  DataRow dr = dt.NewRow();
  dt.Rows.Add(dr);
  dr = dt.NewRow();
  dt.Rows.Add(dr);
  

  It works just fine. The new row's autoincrement column was auto incremented. Have you tried explicitly specifying your datatable field into AutoIncrement

  Also, here's a link on how to handle autonumbers in ADO .Net:

  http://support.microsoft.com/default.aspx scid=kb%3Ben-us%3B815629

  cheers,

  Paul June A. Domag

  
  
  

• h1

  It probably doesn't matter what you set the seed to, since this is only to maintain unique records in your dataset. When they are written back to your database, you pass all fields EXCEPT your autoincrement column and the database will create auto ID's for the records.

  To prevent conflicts, set it to a negative number, for eample:

  Dim myDataColumn As DataColumn
  
  ' ID Column
  myDataColumn = New DataColumn
  With myDataColumn
  .DataType = System.Type.GetType("System.Int32")
  .ColumnName = "myIDColumn"
  .AutoIncrement = True
  .AutoIncrementSeed = -1
  .AutoIncrementStep = -1
  End With

  myDataSetTable.Columns.Add(myDataColumn)

  ' String Colum
  myDataColumn = New DataColumn
  With myDataColumn
  .DataType = System.Type.GetType("System.String")
  .ColumnName = "myStringColumn"
  End With

  myDataSetTable.Columns.Add(myDataColumn)

  And so on...

  Steve

  
  

• Richie Thorpe

  which can become a problem when the dataset is updated with the new ID from the database. Cause the update is executed row by row
  
  For example dataset has IDs 3 4 5, and database has IDs 1 2 3.
  when you update the first row the dataset row one will be set to ID 4 and have 4 4 5 and an exception will be thrown.
  
  
  

• K. Murli Krishna

  It's weird because when i create the database using access i define a primary key and auto increment columns, but when i try to programmatically edit these values i must first manually define a primary key and an autoincrement column.
  
  When i defined these before i insert a row everything works fine.
  
  

• Escobar4Life

  Hi,

  To avoid this mishaps, you must get the newly generated ID of the table after the insert statement. Use @@IDENTITY or ident_current('tablename') to do that. After getting the new ID set it to your datatable.

  cheers,

  Paul June A. Domag

  
  
  

• Kolf

  Your code is correct. When you call NewRow, the autoincrement column will get its new value.
  
  Try to view the value of the column after you call NewRow.
  Double-check that the column really is part of the table and autoIncrement is set to true, autoincrementstep is set to something other than 0.
  
  Is this a typed dataset If not I would make it a typed dataset.
  
  
  

• Mr.Siddhardhaa

  A problem with setting autoincrement values manually is that i do not know the correct seed for the dataset.
  
  

• Adrian Foot

  One thing you will want to do is set the autoincrement property to -1. this will insure a unique Key when your update is performed on the database.
  
  For example If I do a filtered read and it returns the IDs 1, 2, and 3. The data set will auto increment 4. When I do this write to the database if there is an ID of 4 you will get an exception.
  
  The big question is why did microsoft default this property to a positive value and why does MS let you set this at all. This bug was rare and hard to find.
  
  
  
• insert row into table with autoincrement primary key