DataColumn Autoincrement value

[JC Voon]

Hi:

How to reset the autoincrement value generated by DataTable ?

I've master and detail table, the detail table has a autoincrement
column, each time i add a new master record, i need to reset the
detail table autoincrement column to start from 1.

I set the dataColumn AutoIncrementSeed=0 and AutoIncrementStep=1 each
time i append a new master row, but it seem not work, it still remain
the previous value, any idea ?

Thanks
JCVoon

 

[Cor Ligthert]

JC,

When the increment is 0 than you would get directly (I thought) a
concurrency vialation error.

Cor

 

[Greg Burns]

Dim dt As New DataTable

Dim myColumn As DataColumn = New DataColumn("ID")
myColumn.ReadOnly = True
'myColumn.DataType = System.Type.GetType("System.Int32")
myColumn.DataType = GetType(Integer)
With myColumn
.AutoIncrement = True
.AutoIncrementSeed = 1
.AutoIncrementStep = 1
End With

' Add the column to a new DataTable.
dt.Columns.Add(myColumn)

Dim myKey(0) As DataColumn
myKey(0) = dt.Columns("ID")

dt.PrimaryKey = myKey

HTH,
Greg

 

[Greg Burns]

Sorry! I didn't read the question fully.

I assume you detail table has a composite primary key...

MasterKey, DetailKey

100,1
100,2
100,3

101,1
101,2

102,1
102,2
102,3
102,4

etc.

If this was in SQL Server your could write a trigger to determine your
DetailKey value. This starts to get real ugly when you need to later delete
a row or insert in between values. Not sure how to do this using a
datatable.

You maybe better off using a GUID as your DetailKey. (I've had success,
using this strategy)

100, {F6C3B751-59EB-49BF-AE8F-0009097087D8}
100, {9258B3BD-4683-4DBE-822F-000B3757BACB}

101, {E39C2D34-11E4-4645-9921-00130E4AB40E}

etc.

If order is important (I assume it is, hence your desire for the sequential
numbers). You could add a nonkey column that is a timestamp or something.

Greg

 

[JC Voon]

Greg Burns, Cor:

Thanks for the reply.

MasterKey, DetailKey

100,1
100,2
100,3

101,1
101,2

102,1
102,2
102,3
102,4

Yes this is exactly what i need

If order is important (I assume it is, hence your desire for the sequential
numbers). You could add a nonkey column that is a timestamp or something.

So it is impossible to reset the autoincrement value ?


Regards
JCVoon

 

[Greg Burns]

So it is impossible to reset the autoincrement value ?

AFAIK, it is impossible.