K
Kenneth Bohman
Linking two or more datagrids in .NET (1.0) is by design
a nightmare. There are several examples on how this has
been disencouraged.
The problem I'm facing now is slightly different and
definitely more severe than previous ones.
One client started complaining after six months of using
a form with linked datagrids, that sometimes data they
have entered no longer can be found.
I've for the last three days traced the database
transactions and can helas confirm that this is the case.
Although to me it seems completely random there must be a
reason and a pattern of course. I would very much like to
hear from anyone with similar experiences.
The users enter information into three linked tables
1) PolicyStatements
Presented as a couple of text- and comboboxes.
2) PolicyStatementPolicies
[Each statement consists of one or more policies
Presented in a parent datagrid
3) PolicyStatementDetails
[Each policy consists of one or more details(funds)
Presented in a child datagrid
When the form in loaded corresponding datatables are
populated with all of the records for each table. Since
there only are a little more than 200 statements and all
in all 670 details performance is not an issue.
This is something I will change straight away though!!!
The user can from this form delete Details and Policies
on a row-by-row level, but cannot delete Statements
What happens is that about 1 time in 80 when the user
saves the form after adding or editing previously entered
records are deleted. Never one, but more often 5-10.
Mostly but not always in sequence.
From SQL Profiler (deletePolicyStatementDetails is the SP)
exec deletePolicyStatementDetails
@Original_PolicyStatementDetailID = 379, @Timestamp =
0x0000000000007E47
exec deletePolicyStatementDetails
@Original_PolicyStatementDetailID = 604, @Timestamp =
0x0000000000007E53
exec deletePolicyStatementDetails
@Original_PolicyStatementDetailID = 605, @Timestamp =
0x0000000000007E54
How can datarows (supposedly) get rowstate "Deleted" when
the records have not even been touched by the user?
What can Data binding have to do with it? Other than this
data binding seems to work allright. See code below
Datagrid validation is nothing short of a joke in .NET.
As I test I will disable all of that to see if it
improves anything
Data relations and linked datagrids don't go well
together. Could that be the problem? See code below
All input is welcome
Regards,
Kenneth Bohman
Code Data binding
With DatagridParent
.DataSource = dvPolicyStatements
.DataMember = "Statement_StatementPolicies"
.Enabled = True
.CaptionText = "Policies"
.AllowNavigation = False
End With
With DatagridChild
.DataSource = dvPolicyStatements
.DataMember
= "Statement_StatementPolicies.StatementPolicies_Statement
Details"
.Enabled = True
.CaptionText = "Holdings"
.AllowNavigation = False
End With
Code Data Relations
'Relation PolicyStatement -> PolicyStatementPolicies
parentcol = dtPolicyStatements.Columns
("PolicyStatementID")
childcol = dtPolicyStatementPolicies.Columns
("PolicyStatementID")
'Create relation
ds.Relations.Add("Statement_StatementPolicies",
parentcol, childcol, False)
'Create ForeignKeyConstraint
ForeignKey = New ForeignKeyConstraint
("FK_Statement_StatementPolicies", parentcol, childcol)
With ForeignKey
.AcceptRejectRule = AcceptRejectRule.None
.UpdateRule = Rule.Cascade
.DeleteRule = Rule.Cascade
End With
ds.Tables("PolicyStatementPolicies").Constraints.Add
(ForeignKey)
'Relation PolicyStatementPolicies ->
PolicyStatementDetails
parentcol = dtPolicyStatementPolicies.Columns
("PolicyStatementPolicyID")
childcol = dtPolicyStatementDetails.Columns
("PolicyStatementPolicyID")
'Create relation
ds.Relations.Add("StatementPolicies_StatementDetails",
parentcol, childcol, False)
'Create ForeignKeyConstraint
ForeignKey = New ForeignKeyConstraint
("FK_StatementPolicies_StatementDetails", parentcol,
childcol)
With ForeignKey
.AcceptRejectRule = AcceptRejectRule.None
.UpdateRule = Rule.Cascade
.DeleteRule = Rule.Cascade
End With
ds.Tables("PolicyStatementDetails").Constraints.Add
(ForeignKey)
ds.EnforceConstraints = True
a nightmare. There are several examples on how this has
been disencouraged.
The problem I'm facing now is slightly different and
definitely more severe than previous ones.
One client started complaining after six months of using
a form with linked datagrids, that sometimes data they
have entered no longer can be found.
I've for the last three days traced the database
transactions and can helas confirm that this is the case.
Although to me it seems completely random there must be a
reason and a pattern of course. I would very much like to
hear from anyone with similar experiences.
The users enter information into three linked tables
1) PolicyStatements
Presented as a couple of text- and comboboxes.
2) PolicyStatementPolicies
[Each statement consists of one or more policies
Presented in a parent datagrid
3) PolicyStatementDetails
[Each policy consists of one or more details(funds)
Presented in a child datagrid
When the form in loaded corresponding datatables are
populated with all of the records for each table. Since
there only are a little more than 200 statements and all
in all 670 details performance is not an issue.
This is something I will change straight away though!!!
The user can from this form delete Details and Policies
on a row-by-row level, but cannot delete Statements
What happens is that about 1 time in 80 when the user
saves the form after adding or editing previously entered
records are deleted. Never one, but more often 5-10.
Mostly but not always in sequence.
From SQL Profiler (deletePolicyStatementDetails is the SP)
exec deletePolicyStatementDetails
@Original_PolicyStatementDetailID = 379, @Timestamp =
0x0000000000007E47
exec deletePolicyStatementDetails
@Original_PolicyStatementDetailID = 604, @Timestamp =
0x0000000000007E53
exec deletePolicyStatementDetails
@Original_PolicyStatementDetailID = 605, @Timestamp =
0x0000000000007E54
How can datarows (supposedly) get rowstate "Deleted" when
the records have not even been touched by the user?
What can Data binding have to do with it? Other than this
data binding seems to work allright. See code below
Datagrid validation is nothing short of a joke in .NET.
As I test I will disable all of that to see if it
improves anything
Data relations and linked datagrids don't go well
together. Could that be the problem? See code below
All input is welcome
Regards,
Kenneth Bohman
Code Data binding
With DatagridParent
.DataSource = dvPolicyStatements
.DataMember = "Statement_StatementPolicies"
.Enabled = True
.CaptionText = "Policies"
.AllowNavigation = False
End With
With DatagridChild
.DataSource = dvPolicyStatements
.DataMember
= "Statement_StatementPolicies.StatementPolicies_Statement
Details"
.Enabled = True
.CaptionText = "Holdings"
.AllowNavigation = False
End With
Code Data Relations
'Relation PolicyStatement -> PolicyStatementPolicies
parentcol = dtPolicyStatements.Columns
("PolicyStatementID")
childcol = dtPolicyStatementPolicies.Columns
("PolicyStatementID")
'Create relation
ds.Relations.Add("Statement_StatementPolicies",
parentcol, childcol, False)
'Create ForeignKeyConstraint
ForeignKey = New ForeignKeyConstraint
("FK_Statement_StatementPolicies", parentcol, childcol)
With ForeignKey
.AcceptRejectRule = AcceptRejectRule.None
.UpdateRule = Rule.Cascade
.DeleteRule = Rule.Cascade
End With
ds.Tables("PolicyStatementPolicies").Constraints.Add
(ForeignKey)
'Relation PolicyStatementPolicies ->
PolicyStatementDetails
parentcol = dtPolicyStatementPolicies.Columns
("PolicyStatementPolicyID")
childcol = dtPolicyStatementDetails.Columns
("PolicyStatementPolicyID")
'Create relation
ds.Relations.Add("StatementPolicies_StatementDetails",
parentcol, childcol, False)
'Create ForeignKeyConstraint
ForeignKey = New ForeignKeyConstraint
("FK_StatementPolicies_StatementDetails", parentcol,
childcol)
With ForeignKey
.AcceptRejectRule = AcceptRejectRule.None
.UpdateRule = Rule.Cascade
.DeleteRule = Rule.Cascade
End With
ds.Tables("PolicyStatementDetails").Constraints.Add
(ForeignKey)
ds.EnforceConstraints = True