Adding a Row With an AutoIncrement Field

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
Wed May 25 17:06:38 CDT 2005

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/



"Phil Galey" <pagaley@starcalif.com.nospam> wrote in message
news:eC2QKwWYFHA.3712@TK2MSFTNGP09.phx.gbl...
> 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.
>
>

jason
Thu May 26 06:06:12 CDT 2005

Are you sure that you have the field defined correctly. I would
suggest calling

da.fillschema(ds, SchemaType.Mapped, TableName)

before filling the ds from the data adapter.

This way the field will behave as an autoincrement.

Also, you need to declare your da withevents if there is a liklihood
that there are other people adding records at the same time.

Since the database is set up with an autoincrement, and the ds is
now setup with an autoincrement, the situation might arise where
two records are added from two differenet sources. Each separate
ds will increment by one, but the database iteself will be out of
sync. This is a problem with related records.

Handle the onUpdated event of the da and read back the
newly created autoincrement field and write it to the DS.

Good luck.

Jason.

On Wed, 25 May 2005 13:55:08 -0700, "Phil Galey"
<pagaley@starcalif.com.nospam> wrote:

>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.
>
>

davidsc
Tue May 31 11:20:06 CDT 2005

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.