dataset grief, addnew row issues

[kenny-e]

I have a dataset which contains a employee table (similar to that in the nwind db).
I use the NewRow method of the table to get a datarow object then fill this with my new data.
At this point the 'EmployeeID field of the datarow is 0.
I then add this new row to tables row collection.
I then call the update method of the data adaptor.
The new row is inserted into the DB but the EmployeeID field is still 0.
How do i get the ID field of the added row to be filled in 'automatically'.
(For every new row that i add to the DS the EmployeeID is 0 - this is no good).

Do i have to write code to requery the added row and get the ID etc
(I'm using SQL and Access DBs in a multi-user environment).

Thanks for help in advance.

[ost]

is the employee-column set to AutoNumber?

[kenny-e]

In the SQL DB, ID field has attributes identity=yes, identity seed =1,identity increment =1
In the Access DB, ID field is autonumber.

[ost]

hmm.. can you show your code?

[jinte]

try this

[Highlight=VB]
datatable = Dataset.Tables("tblEmployee")

Dataset.Tables("tblEmployee").Columns("EmployeeID").AutoIncrement = True
Dataset.Tables("tblEmployee").Columns("EmployeeID").AutoIncrementSeed = -1
Dataset.Tables("tblEmployee").Columns("EmployeeID").AutoIncrementStep = -1
'Voeg een nieuwe rij toe aan de datatabel
row = datatable.NewRow
row("......") = ...... 'fill in your stuf in the columns
datatable.Rows.Add(row)

DAEmployee.Update(Dataset, "tblEmployee")
greetz

[kenny-e]

I've forgot to add that i'm using a strongly typed dataset: the wizard generates code for me:
The typed dataset InitClass method contains code like:


Private Sub InitClass()
...


Me.Constraints.Add(New UniqueConstraint("EmployeeDSKey1", New DataColumn() {Me.columnEmployeeID}, true))
Me.columnEmployeeID.AutoIncrement = true
Me.columnEmployeeID.AllowDBNull = false
Me.columnEmployeeID.ReadOnly = true
Me.columnEmployeeID.Unique = true
Me.columnLastName.AllowDBNull = false
Me.columnFirstName.AllowDBNull = false
end sub

My add new record is coded like:
Public Sub eAddNew()
Dim DR As EmployeeDS.EmployeesRow
Dim DTEmployee As EmployeeDS.EmployeesDataTable

Try
'Get reference to the categories table...
DTEmployee = m_DS.Employees
'Create new row with same schema...
DR = DTEmployee.NewEmployeesRow
'Add dummy fields...None

'Add dummy fields...non-null fields...testing?
'Diagnostic
'DR("LastName") = "Dummy LN"
'DR("FirstName") = "Dummy FN"

DR("LastName") = String.Empty
DR("FirstName") = String.Empty
DTEmployee.AddEmployeesRow(DR)
Catch Ex As Exception
Throw Ex
End Try
End Sub

Existing data has been read into the dataset via fill method..
If i call my eAddNew then examine the DTEmployee object via the debuger(set break point at 'exit sub') - i find that the employeeID
field has not been incremented - it is still 0.

I've tried adding code:
Me.columnLastName.AutoIncrementSeed = -1
Me.columnFirstName.AutoIncrementStep = -1
to the InitClass
This has no effect - employeeID still 0 after add new row.


If the dataset is empty and i addnew rows, the employeeID is incremented as expected.
I've had a read of the documentation again:
'DataColumn.AutoIncrement Property'
The remarks section states the following:
If the type of this column is not Int16, Int32, or Int64 when this property is set, the DataType
property is coerced to Int32. An exception is generated if this is a computed column (that is, the
Expression property is set.) The incremented value is used only if the row's value for this column,
when added to the columns collection, is equal to the default value.

Yeah - this is difficult to understand or bad english...

Any other ideas would be welcomed.