DataTable and AutoIncrement

[Dennis]

I have set a DataTable and one of the columns I set "AutoIncrement" to True.
I then populate the Table by setting the columns to values then add the row
to the table. I inadverently set the AutoIncrement Columns to different
values but didn't get any errors. Should I be able to set the value of an
AutoIncrement Column? I would have thought it couldn't be done as the column
value was set when a row was added.
--
Dennis in Houston

[Cor Ligthert]

Dennis,

I think that you can the best ave a look at this

autoincrement class
http://msdn.microsoft.com/library/de...ementtopic.asp

You cannot set the values, they will not even been in the database.
Setting the seed to -1 is the advised method.

See for this as well the article of Bill
http://msdn.microsoft.com/library/de...anidcrisis.asp

I hope this helps?

Cor

"Dennis" <De****@discussions.microsoft.com>

I have set a DataTable and one of the columns I set "AutoIncrement" to
True.
I then populate the Table by setting the columns to values then add the
row
to the table. I inadverently set the AutoIncrement Columns to different
values but didn't get any errors. Should I be able to set the value of an
AutoIncrement Column? I would have thought it couldn't be done as the
column
value was set when a row was added.
--
Dennis in Houston

[Dennis]

If I understand you correctly, I can add numbers to an AutoIncrement Column
in a talbe in a DataSet but when the actual database is updated, the numbers
I added won't appear in the Database...is this correct?

"Cor Ligthert" wrote:

Dennis,

I think that you can the best ave a look at this

autoincrement class
http://msdn.microsoft.com/library/de...ementtopic.asp

You cannot set the values, they will not even been in the database.
Setting the seed to -1 is the advised method.

See for this as well the article of Bill
http://msdn.microsoft.com/library/de...anidcrisis.asp

I hope this helps?

Cor

"Dennis" <De****@discussions.microsoft.com>

I have set a DataTable and one of the columns I set "AutoIncrement" to
True.
I then populate the Table by setting the columns to values then add the
row
to the table. I inadverently set the AutoIncrement Columns to different
values but didn't get any errors. Should I be able to set the value of an
AutoIncrement Column? I would have thought it couldn't be done as the
column
value was set when a row was added.
--
Dennis in Houston

[Cor Ligthert]

Dennis,

I even do not believe you can add numbers to that. However the last
statement in your message is right.

"Dennis" <De****@discussions.microsoft.com>

If I understand you correctly, I can add numbers to an AutoIncrement
Column
in a talbe in a DataSet but when the actual database is updated, the
numbers
I added won't appear in the Database...is this correct?

"Cor Ligthert" wrote:

Dennis,

I think that you can the best ave a look at this

autoincrement class
http://msdn.microsoft.com/library/de...ementtopic.asp

You cannot set the values, they will not even been in the database.
Setting the seed to -1 is the advised method.

See for this as well the article of Bill
http://msdn.microsoft.com/library/de...anidcrisis.asp

I hope this helps?

Cor

"Dennis" <De****@discussions.microsoft.com>

>I have set a DataTable and one of the columns I set "AutoIncrement" to
>True.
> I then populate the Table by setting the columns to values then add the
> row
> to the table. I inadverently set the AutoIncrement Columns to
> different
> values but didn't get any errors. Should I be able to set the value of
> an
> AutoIncrement Column? I would have thought it couldn't be done as the
> column
> value was set when a row was added.
> --
> Dennis in Houston

[Dennis]

Cor, the below code is what I'm talking about. I would have thought I should
get an error when I set the AutoIncrement Column 1 to the random integer.
Private Function CreateDataTable(ByVal TableName As String) As DataTable
'Create DataTable and add Columns
Dim t As DataTable = New DataTable
t.TableName = TableName
Dim col1 As DataColumn = New DataColumn("Col1",
Type.GetType("System.Int32"))
Dim col2 As DataColumn = New DataColumn("Col2",
Type.GetType("System.String"))

col1.AutoIncrement = True

'Add DataColumns to DataTable
t.Columns.Add(col1)
t.Columns.Add(col2)

'Populate the Columns
Dim irand As New Random(123)
Dim k as integer
For i = 1 To 25
k = irand.Next
newrow = t.NewRow
newrow("col1") = CType(k, Int32) 'AutoIncrement
newrow("col2") = "ABCD" 'String
t.Rows.Add(newrow)
Next i
End Sub

"Cor Ligthert" wrote:

Dennis,

I even do not believe you can add numbers to that. However the last
statement in your message is right.

"Dennis" <De****@discussions.microsoft.com>

If I understand you correctly, I can add numbers to an AutoIncrement
Column
in a talbe in a DataSet but when the actual database is updated, the
numbers
I added won't appear in the Database...is this correct?

"Cor Ligthert" wrote:

Dennis,

I think that you can the best ave a look at this

autoincrement class
http://msdn.microsoft.com/library/de...ementtopic.asp

You cannot set the values, they will not even been in the database.
Setting the seed to -1 is the advised method.

See for this as well the article of Bill
http://msdn.microsoft.com/library/de...anidcrisis.asp

I hope this helps?

Cor

"Dennis" <De****@discussions.microsoft.com>
>I have set a DataTable and one of the columns I set "AutoIncrement" to
>True.
> I then populate the Table by setting the columns to values then add the
> row
> to the table. I inadverently set the AutoIncrement Columns to
> different
> values but didn't get any errors. Should I be able to set the value of
> an
> AutoIncrement Column? I would have thought it couldn't be done as the
> column
> value was set when a row was added.
> --
> Dennis in Houston

[Cor Ligthert]

Dennis,

My believe was not true, however the rest is. Therefore I have not to
believe anymore I know.

You can try this sample it needs a button and a datagrid on a form and a
reference to COM adox ext 2.x for dll and security
\\\ When you have tested it than change the part where I have told that add
your adding of the key
Private da As New OleDb.OleDbDataAdapter
Private Sub Form7_Load(ByVal sender As Object, ByVal _
e As System.EventArgs) Handles MyBase.Load
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\test1\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=c:\test1\db1.mdb")
'To make tables we use Adonet
Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;" & _
" Data Source=C:\test1\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE Dennis ( " & _
"Col1 int identity ," & _
"Col2 NVarchar(50)," & _
"CONSTRAINT [pk_Col1] PRIMARY KEY (Col1)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
Dim dt As New DataTable
Dim selectstring As String = "Select * from Dennis"
da = New OleDb.OleDbDataAdapter(selectstring, conn)
da.FillSchema(dt, SchemaType.Mapped)
dt.Columns(0).AutoIncrementSeed = -1
dt.Columns(0).AutoIncrementStep = -1
'Populate the Columns
Dim irand As New Random(123)
Dim k As Integer
For i As Integer = 1 To 25
k = irand.Next
Dim newrow As DataRow = dt.NewRow
newrow("col1") = CType(k, Int32)
'delete this row to see it with the autoseed
newrow("col2") = "ABCD"
dt.Rows.Add(newrow)
Next i
DataGrid1.DataSource = dt
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim cmb As New OleDb.OleDbCommandBuilder(da)
Dim dt As DataTable = DirectCast(DataGrid1.DataSource, DataTable)
da.Update(dt)
dt.Clear()
da.Fill(dt)
DataGrid1.DataSource = dt
End Sub
///

I hope this helps?

Cor

[Dennis]

Thanks Cor. You help a lot of people on this newsgroup.

"Cor Ligthert" wrote:

Dennis,

My believe was not true, however the rest is. Therefore I have not to
believe anymore I know.

You can try this sample it needs a button and a datagrid on a form and a
reference to COM adox ext 2.x for dll and security
\\\ When you have tested it than change the part where I have told that add
your adding of the key
Private da As New OleDb.OleDbDataAdapter
Private Sub Form7_Load(ByVal sender As Object, ByVal _
e As System.EventArgs) Handles MyBase.Load
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c:\test1\db1.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db1.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=c:\test1\db1.mdb")
'To make tables we use Adonet
Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;" & _
" Data Source=C:\test1\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("CREATE TABLE Dennis ( " & _
"Col1 int identity ," & _
"Col2 NVarchar(50)," & _
"CONSTRAINT [pk_Col1] PRIMARY KEY (Col1)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
Dim dt As New DataTable
Dim selectstring As String = "Select * from Dennis"
da = New OleDb.OleDbDataAdapter(selectstring, conn)
da.FillSchema(dt, SchemaType.Mapped)
dt.Columns(0).AutoIncrementSeed = -1
dt.Columns(0).AutoIncrementStep = -1
'Populate the Columns
Dim irand As New Random(123)
Dim k As Integer
For i As Integer = 1 To 25
k = irand.Next
Dim newrow As DataRow = dt.NewRow
newrow("col1") = CType(k, Int32)
'delete this row to see it with the autoseed
newrow("col2") = "ABCD"
dt.Rows.Add(newrow)
Next i
DataGrid1.DataSource = dt
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim cmb As New OleDb.OleDbCommandBuilder(da)
Dim dt As DataTable = DirectCast(DataGrid1.DataSource, DataTable)
da.Update(dt)
dt.Clear()
da.Fill(dt)
DataGrid1.DataSource = dt
End Sub
///

I hope this helps?

Cor