Adding a Row With an AutoIncrement Field

[Phil Galey]

I am trying to add a row to a table in a dataset. The table contains an ID
field, which is set up as an AutoIncrement field, with AutoIncrementSeed = 1
and AutoIncrementStep = 1. I like the convenience of the following syntax:

MyDataset.Tables("MyTable").Rows.Add(New Object(){"data for field1", "data
for field2", "data for field3"...})

but because the first field is an AutoIncrement field, I'm not sure what to
put in place of the first field. I tried vbNull, but that doesn't help. It
complains on the second add that the ID already exists. How do I deal with
the AutoIncrement field in an add such as the one above? Thanks.

 

[Sahil Malik [MVP]]

Phil,

You can do something like this -

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
DataTable dt = GetDataTable();
dt.LoadDataRow(new Object[] { 0, "One", "Two" },
LoadOption.Upsert);
dt.LoadDataRow(new Object[] { (int)dt.Compute("MAX (PK) + 1",
""), "One", "Two" }, true);
dt.LoadDataRow(new Object[] { (int)dt.Compute("MAX (PK) + 1",
""), "One", "Two" }, true);
dt.LoadDataRow(new Object[] { (int)dt.Compute("MAX (PK) + 1",
""), "One", "Two" }, true);
}

static DataTable GetDataTable()
{
DataTable dt = new DataTable();
DataColumn dc = new DataColumn("PK");
dc.AutoIncrement = true;
dc.Unique = true;
dt.Columns.Add(dc);

dt.Columns.Add(new DataColumn("X"));
dt.Columns.Add(new DataColumn("Y"));

return dt;
}
}
}

Do note however that this will run hella slow - considering it is computing
the max everytime.
Enjoy !!

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[David Sceppa]

Phil,

You want to omit the auto-increment column from the list of values
you're supplying when adding the new row. In VB.NET, use the Nothing
keyword. C# developers would use null. Your code will look something like:

Dim tbl As New DataTable
With tbl.Columns.Add("ID", GetType(Integer))
.AutoIncrement = True
.AutoIncrementSeed = -1
.AutoIncrementStep = -1
End With
tbl.Columns.Add("OtherColumn", GetType(String))

tbl.Rows.Add(New Object() {Nothing, "First row"})
tbl.Rows.Add(New Object() {Nothing, "Second row"})

Console.WriteLine(tbl.Rows(0)("ID"))
Console.WriteLine(tbl.Rows(1)("ID"))

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.