Qn: retrieving autonumber fields (MSAccess) into datasets

[pplppp]

1.I made a typed dataSet using the VS.net xml editor, but it seems
that specifying the key using the xml editor to specify primary keys
does not work, since repeated calls to the Fill method of the
dataadapter would put duplicate rows into the datatable. so I had to
programatically add the keys to each table. just wondering if it's
some problem that other people faces or whether I missed some setting
in the xml editor.

2. I'm using MS Access as database and I have a table with an
autonumber field. I read an article on MSDN :
http://msdn.microsoft.com/library/d...cpconRetrievingIdentityOrAutonumberValues.asp
and used the OnRowUpdated event to retrieve the autonumber field, but
that means I would need to put null in the autonumber field of the
datatable when I call update. So I cannot specify that field as part
of a key since a key cannot take a null value. but then I would get
duplicate rows in the datatable that is not supposed to happen. I am
wondering if someone has a way to work around this

thx in advance.

 

[k]

Pplppp,

You may find it convenient to use a similar method to what I am using.
This method is very convenient and takes very little time to implement.
It uses autogenerated primary keys from the database and wizard
generated dataset/insert/update/delete code. I included some sample
code below:

1. Let the database generate the primary key on all insert operations,
2. Retrieve that primary key from the database,
3. Overwrite the PK value in your datatable with the database's value,
using a datarow reference via it's currency manager.
4. Set the rowstate on that row to "Unchanged" so you'll know it's
in-synch with the database, and it will work with all Update and
Delete operations.


Another option is to forgo the database autogenerated primary keys
and instead create your own primary keys based off of something such
as a datetimestamp + a random number. I have used it before, and
it is usually somewhat messy and therefore somewhat slow to
implement.

There are other options such as "pre-generated keys" which are mainly
more available if you were using a different DBMS,
but I think the method I've mentioned is fairly slick and resilient.

Good luck.

k


'///////////////////////////////////////////////////////////////////////////
/////////////////////////////
'// This method is the closing method for the second form (detail form)
'// in a Master/Detail relationship. Before we close the form, we
'// synchronize the Primary Keys between our Dataset.Datatable
'// and our Database.Datatable by overwriting the Dataset.Datatable PK.
'///////////////////////////////////////////////////////////////////////////
/////////////////////////////
Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnOK.Click
Me.cmEmployees.EndCurrentEdit()
Try
Me.m_parent.OleDbDataAdapter1_Users.Update(Me.m_parent.DsUsers1)
If Me.m_actionToTake = formAction.newUser Then
'// To avoid having the autogenerated primary key in dataset out
'// of synch with database, retrieve the newly created
'// database PK and update our dataset's PK with it.
'// Include a variable and a command to retrieve the
'// autogenerated primary key value (identity value)
'// from the Access database.
'// cf.ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/
'// cpconretrievingidentityorautonumbervalues.htm
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = _
New OleDbCommand("SELECT @@IDENTITY",
Me.m_parent.OleDbConnection1)

'// Retrieve the identity value.
newID = CInt(idCMD.ExecuteScalar())

'// Create a datarow reference to inspect/modify our current
row.
Dim drCm As DataRow = CType(Me.cmEmployees.Current, _
DataRowView).Row

'// push DB's new PK to row's Current/Original/Default values
drCm.Item("EmployeeID") = newID


'// Roll our PK value change into the row's "original version."
'// The deleteCommand uses the original row PK values to do the
'// deletion, which may be out of synch with the database.
'// If so, the dataset would throw an exception when the
'// database (looking for the wrong PK) could still not find
'// the row to delete.
cmEmployees.EndCurrentEdit() '// allow this row's changes to
remain


'// set row's state from "Modified" to "Unchanged" we don't
'// accidentally try to update this field, etc.
drCm.AcceptChanges()

End If
Catch ex As Exception
MessageBox.Show(ex.Message + vbcrlf + vbcrlf + ex.StackTrace)
End Try

Me.Owner.Focus()
Me.Close()
End Sub