my Access-Database has a Mastertable (Customer) with the ID-Field CostumerID und a Detailtable (Telefon) with a 1:n relationship. In the Detailtable is the field CostumerID. The CustomerID-Field in the Mastertable is autonumber.
There are a Dataset1.xsd, Dataset1, CustomerTableAdapter, CustomerBindingsource, TelefonTableAdapter, TelefonBindingsource and an UltraGrid1.
UltraGrid1.Datasource=CustomerBindingsource
AllowAddnew=true
Form-load-Event:
Me.CustomerTableAdapter.Fill(Me.DataSet1.Customer)
Me.TelefonTableAdapter.Fill(Me.DataSet1.Telefon)
Form-Closing-Event:
Me.CustomerBindingSource.EndEdit()
Me.CustomerTableAdapter.Update(Me.DataSet1.Customer)
Me.TelefonBindingSource.EndEdit()
Me.TelefonTableAdapter.Update(Me.DataSet1.Telefon)
When the program starts, all things are fine. Data are seen in the grid. My problem is:
I add a new Customer. The CustomerID is -1. Now I add a new telefon-row to the new customer (the CostumerID in the telefon-row is -1 too). So far so good. But if the programs ends, I get an error because of error in relationship.
Please, what can I do ?
What's the error you are getting?
This issue really has nothing to do with the grid, the grid doesn't deal directly with the local back end, only the local data source. My guess is that you will probably have to jump through some hoop to save the parent row and get the auto-generated ID field value and then update the child row with the correct value. I remember seeing an article on Microsoft site many years ago which described this technique, but it's been a while and I can't seem to find it, now. You might want to post your question on a more general DotNet programming forum rather than here, since this is more of a DataSet/DataAdapter question.
Dear Mike,
thank you for your answer. You are right, if you say that it is a dataset-/dataAdapter-problem. I have read many articles about that and I tested many samples. But all failed. In the help-document of NetAdvantage “Bind WinGrid to a Hierarchical Data Source” is a sample to show the data, but without change, delete or insert sample. The sample use the TableAdapter-component and a typed dataset. I think there is no event for use @@IDENTITY.
Because it is a every-day-problem to insert a new master-row and a new detail-row with ultragrid, I hoped that someone can help me. But that’s a mistake. It seems no one except me want to save a new customer and his orders with the ultragrid.
My wish: Please complement the “Bind WinGrid to a hierarchical Data Source”-article with a complete update-prozedur.
The error I get say, that there is no master-record saved to bind the detail-record to it. The ID=-1 (Master-record) change during the update prozess but in the detail-record it still stands to -1.
Thank you
Juergen Schroeder
Hi Juergen,
I can understand your frustration. I had to do the same thing several years ago while writing an application for internal use here at Infragistics.
As I said above, I don't remember where I found the article that helped me out, but I thikn the basic approach I had to take was to handle the RowUpdated event of the dataAdapter and use SQL to get the last identity and update the row.
Here's an MSDN article I just found which look promising.
http://msdn.microsoft.com/en-us/magazine/cc164120.aspx
H i Mike,
I get it! Only 3 month for a problem which is done in 10 seconds within VB6, but I get it. Yesterday the US-and the german-footballteams get it, I get it today.
For all who has the same problem:
AddHandler <TableAdapter>.Adapter.RowUpdated, AddressOf Me.OnRowUpdated
Private
Sub OnRowUpdated(ByVal sender As Object, ByVal args As Data.OleDb.OleDbRowUpdatedEventArgs)
If args.Row.Item(<fieldname>) < 0 Then Dim NewID As Integer Dim cmd As New OleDbCommand("SELECT @@IDENTITY", Me.<TableAdapter>.Connection) NewID = cmd.ExecuteScalar() args.Row.Item(<fieldname>) = NewID args.Row.AcceptChanges() cmd.Dispose() End If
If args.Row.Item(<fieldname>) < 0 Then
Dim NewID As Integer
Dim cmd As New OleDbCommand("SELECT @@IDENTITY", Me.<TableAdapter>.Connection)
NewID = cmd.ExecuteScalar()
args.Row.Item(<fieldname>) = NewID
args.Row.AcceptChanges()
cmd.Dispose()
End If
End Sub
Thank you for help!