Bug in DataTable wizard code ? MS Access, retrieving @@Identity ?
Hi !
I think I have found something strange...
I'm using Visual Studio 2005 Beta 2.
I'm accessing a Access 2003 database using the built in DataTableAdaptor component to insert a new row into a tabel.
The table has a Identity column called "Id".
Now, I want to retrieve the Identity using the normal Event RowUpdated method but here's the thing.
When looking at the wizard created source code in ReportDataSet.Designer.vb no event is published for the ReportDataAdapter.
(My Class is called "Report")
A component called ReportDataAdapter.Adapter (public property, that mirrors the m_adapter variable of type TabelAdapter) is however published but the RowUpdated event will not fire for some reson, when Im running the code:
Me.ReportsTableAdapter.Insert("Test")Has anyone succeded in retrieving a @@Identity from a Access database using the build in componenets by hooking on to the RowUpdated event ? or is there another way :) ?
Regards,
Per Bornsjo
Per Bornsjo,
There's nothing wrong with the TableAdapter. It does not have the additional code required to retrieve the database-generated auto-increment values. You're correct in looking for the RowUpdated event.
The reason you can't find a RowUpdated event on the TableAdapter is because it isn't really a DataAdapter, it's a simple component that contains a DataAdapter. In this case, the solution is to add code inside the TableAdapter.
You can add code like the following to the MyDataSet.Designer.vb file to use the RowUpdated event to retrieve the new value and assign it to the appropriate column. (Note: You'll need to change the column name in the code.)
| |
Private IdentityQuery As New System.Data.OleDb.OleDbCommand("SELECT @@IDENTITY", Nothing)
Private Sub m_adapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles m_adapter.RowUpdated
If e.StatementType = StatementType.Insert AndAlso _
e.Status = UpdateStatus.Continue Then
IdentityQuery.Connection = e.Command.Connection
e.Row("OrderID") = IdentityQuery.ExecuteScalar
End If
End Sub
|
The drawback to this approach is that Visual Studio .NET will re-generate this file each time you change the strongly-typed DataSet and you'll lose your code. To address scenarios like this, Visual Studio .NET 2005 uses partial classes. (For more information on partial classes, see the .NET Framework documentation.)With partial classes, you add similar code to a separate code file with the following code: | |
Namespace MyDataSetTableAdapters
Partial Public Class MyTableTableAdapter
'Same code as before
End Class
End Namespace
|
Now if you change the strongly-typed DataSet, Visual Studio .NET will re-generate the MyDataSet.Designer.vb class but you won't lose your code.
I hope this information proves helpful.
David Sceppa
ADO.NET Program Manager
Microsoft
Great ~! Very userful for coding
it is a good way to combine manual commander and adapter command
Namespace DataSetTableAdapters
Partial Public Class DataTableTableAdapter
Private IdentityQuery As New Odbc.OdbcCommand("SELECT LAST_INSERT_ID();", Nothing)
Private Sub adapter_RowUpdated(ByVal sender As Object, ByVal e As Odbc.OdbcRowUpdatedEventArgs) Handles _adapter.RowUpdated
If e.StatementType = StatementType.Insert AndAlso _
e.Status = UpdateStatus.Continue Then
IdentityQuery.Connection = e.Command.Connection
MessageBox.Show(IdentityQuery.ExecuteScalar)
End If
End Sub
End Class
End Namespace
Difficult to search this method, hope the keywork can help for newcomer
Refresh / Retrieve after update / autoincrement / autonumber / Wizard
