Typed Dataset Constraint Exception on 2nd Pass

JaedenRuiner

Well-known member
Joined
Aug 13, 2007
Messages
340
Programming Experience
10+
Well,

I've done a lot of revamping, reducing the global access to everything, and made the application a bit more modular overall. But a weird problem that I don't quite see how it could be occurring keeps popping up.

As with all typed datasets generated by the wizard/designer it comes with the basic TableAdapterManager, MyDatabaseDataSet, and all the typed DataTables and TableAdapters for the database. Simple.

I Extended this to bascially provide a link between each DataTable and their respective TableAdapters, as well as I provided a Refresh command on all these DataTables which intrinsically calls it's own Adapter's Fill() method.

I continued to take the two primary classed (TableAdapterManager and MyDatabaseDataSet) and wrapped them into another class that controls and manipulates everything I would ever possibly need with the typed dataset, and I must say it makes life easier.
  • MyWrapper.Commit() - Cycles through all tables and attempts to Update() any added, modified, or deleted rows:
    • CommitUpd() is recursive, cycling through ParentRelations collection and Commiting any Parent Tables First.
    • CommitDel() is Recursive, cycling through ChildRelations collections and committing and Child Tables First
  • MyWrapper.FillDataset() - Fills all the typed tables of the dataset, (including my views one of which is a list and typing flag of each table in my database. This is for usage determinations in my app, and quite helpful overall).
The FillDataset is where the problem lies. Several tables are "list" tables, or simply ID to Value combinations, like Customer lists, or Inventory Lists, and are pretty much the primary "Parent" tables. They have no parents whatsoever. They are filling fine, as are all their child (foreign key) tables.

My FillDataSet is also recursive, basically, reading the table it is given to fill, and if that table happens to have any parentrelations, it fills them first to maintain relational integrity as with the commit.

So my problem:
I load my app, Log into the Database, and open my Database "Form" (the one which directly manipulates all the tables and such).
VB.NET:
  Private _RexCon as MyWrapper ' It's not named that, but i'm saving on typing
  Private Sub AdminForm_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
                                    Handles Me.VisibleChanged
      If Me.Visible Then
         'If A User/Pass is not supplied
         If (Not My.Application.ConnectionMgr.CanConnect) Then 
            My.Application.DoLogon(Me, _RexCon) ' Open the "Login" form and attempt connect
         Else 'otherwise User/Pass is available so just connect directly
            _RexCon = New RexamDatabase.RexamDBConnection(My.Application.ConnectionMgr)
            _RexCon.Connect(My.Application.UserLogin)
         End If
         If _RexCon IsNot Nothing AndAlso _RexCon.Connected Then
            InitBindings() 'Form Method added to create ALL databindings for
                        ' controls, binding sources, and DataGridViews
            _RexCon.FillDataset() 'Fills the Dataset 
            Me.TabCtrl.Enabled = _RexCon.DSFilled 'Enables controls
            WasHidden = False
            FormTimer.Enabled = True 
         End If
      Else
         DisconnectBindings() 'Disconnects ALL databindings for controls,
                        'bindingsources, and DataGridViews
         _RexCon.Dispose() 'Disposes all of the object implementing IDisposable,
                        'Closing internal SqlConnections, and clearing the dataset
         _RexCon = Nothing
         FormTimer.Enabled = False
         WasHidden = True
      End If
   End Sub

The first time i load it up, the form becomes visible and all is hunky dory. I close the form.
VB.NET:
   Private Sub AdminForm_FormClosing(ByVal sender As Object, _
                           ByVal e As System.Windows.Forms.FormClosingEventArgs) _
                           Handles Me.FormClosing
      If e.CloseReason = CloseReason.UserClosing Then
         e.Cancel = True
         Me.Hide()
      End If
      If My.Application.Preferences.SaveLayout Then
         My.Settings.AdminLoc = Me.Location
         My.Settings.AdminSize = Me.Size
      End If
   End Sub
As you can see i've had to set it up to work with the closing of the form, so that the form would be hidden during application operation, but when Me.Hide() is executed the Visible_Changed event triggers and the connection and dataset is cleared (disposed)

Then to test I re-open the form, and thats when I get a ConstraintException:
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Now, i wouldn't have too much concern, because I know such things can happen, but It loaded great the first time. but no matter how many time I execute disconnect/connect in my app, it won't load past the a specific table. What I'm concerned about mostly, is that the data is in the database and it doesn't seem to have a problem with it, and the fact that the dataset loads fine the first time is very suspect.
below is my FillDataset base code, to help follow the flow of what is going on in there, but overall, i'm a bit stymied.

VB.NET:
  ''' <summary>
      ''' Fills the Entire RexamShipDataSet Datatables.
      ''' </summary>
      ''' <returns>Boolean - Whether or not the dataset was filled.</returns>
      ''' <remarks></remarks>
      Public Function FillDataset() As Boolean
         If Connected Then
            _filling = True
            _dataset.Clear()
            RefreshTableList()
            For Each tbl As DataTable In _dataset.Tables
               If Not Refresh(tbl) Then 'function below
                  Return False
               End If
            Next
            _filling = False
            _dsFilled = True
            RaiseEvent DataSetFilled(Me, New EventArgs())
            Return True
         End If
         Return False
      End Function

      Protected Overloads Function Refresh(ByVal tbl As DataTable, _
                                    Optional ByVal bRefill As Boolean = False) As Boolean
         Dim b As Boolean = False
         If Connected AndAlso (tbl IsNot Nothing) AndAlso _
                           (bRefill OrElse (Not _dataset.TableInfo(tbl.TableName).Filled)) Then
            DoWaitCursor(True)
            b = True
            For Each rel As DataRelation In tbl.ParentRelations
               b = b AndAlso Refresh(rel.ParentTable) 'recurse into this again
            Next
            b = b AndAlso _dataset.Refresh(tbl) 'function below
            If b Then
               RaiseRefreshEvent(tbl, _dataset.TableInfo(tbl.TableName).Type)
            End If
            DoWaitCursor(False)
         ElseIf _dataset.TableInfo(tbl.TableName).Filled Then
            b = True
         End If
         Return b
      End Function

   Public Function Refresh(ByVal tbl As DataTable)
      If tbl IsNot Nothing Then
         Try 
            'ta_* prefix is typed TableAdapter
            '_manager is internal to the Typed DataSet pointing to the AdapterManager
            Select Case tbl.TableName
               Case "tbl_BOL"
                  'this is the table that bombs, even though both parent's are loaded fine
                  _manager.ta_tblBOL.Fill(tbl_BOL) 
               Case "tbl_Email"
                  _manager.ta_tblEmail.Fill(tbl_Email) 'parent to tbl_BOL
               Case "tbl_MailBox"
                  _manager.ta_tblMailBox.Fill(tbl_MailBox) 'parent to tbl_Email
               Case "lst_Customers"
                  _manager.ta_lstCustomers.Fill(lst_Customers)
               Case "lst_Material"
                  _manager.ta_lstMaterial.Fill(lst_Material)
               Case "lst_CorbiLoc"
                  _manager.ta_lstCorbiLoc.Fill(lst_CorbiLoc)
               Case "qry_EmailSum"
                  _manager.ta_qryEmailSum.Fill(qry_EmailSum)
               Case "qry_Tables"
                  RefreshList()
               Case Else 'In case it doesn't have a typed adapter for the table
                  _manager.SpareAdapter.SelectCommand = New SqlClient.SqlCommand( _
                                    Format(tbl.TableName, "SELECT * FROM {0}"), _manager.Connection)
                  _manager.SpareAdapter.Fill(tbl)
            End Select
            If Not _list.Contains(tbl.TableName) Then
               _list.Add(tbl.TableName, New RexamTableInfo(RexamTableType.View, True, False))
            Else
               _list(tbl.TableName).Filled = True
            End If
            _list(tbl.TableName).Filled = True
            Return True
         Catch ex As Exception
            My.Application.LogError(ex, "{0}")
         End Try
      End If
      Return False
   End Function

Thanks
 

JaedenRuiner

Well-known member
Joined
Aug 13, 2007
Messages
340
Programming Experience
10+
I am a bit concerned about this because it is very consistent, and I thought there might be a way to find out, hopefully, which constraint is causing the exception on the Adapter.Fill(). I've looked into the Reflector to see what happens in the Adapter.Fill() method, and somewhere in there a call is made to DataSet.EnableConstraints. From there it winds up calling FailedEnabledConstraints(). Currently there are three ForiegnKeys associated with the table along with the Primary Key, but the exception message is very vague "One of the constraints." How could I find out Which one? I really need to figure out why I can load and run the program with no problems, but when I close the form (which disconnects and disposes the dataset) when I re-show it (reloading the dataset and filling it) it pops up this constraint error.

Thanks
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
I once had a problem where I had a dataset that was full of related tables

in particular, 3 tables (banks, suppliers, customers) could logically have addresses so each would use the addresses table. Thing is, the way that the relationships were, with banks/suppliers/customers as parent and addresses as child, I'd fill ONE parent (e.g.g on the banks form) and then fill child, and it would fail because the other two relationships couldnt be satisfied (suppliers/customers)


I've added a DLL to this post that may tell you. I've had to include the bin and obj folders (bin most important) because it's in C#, so you'll have to use it in compiled form. Read the code if youre curious, but use it like:

VB.NET:
Try
'ops that break
Catch Exception
  Dim sb as New StringBuilder
  Nitpick.DataSets.WhatsWrong(myDs, sb) 'errors will be laoded into sb
  MsgBox.Show(sb.ToString())
End Try
 

Attachments

  • Nitpick.zip
    21.2 KB · Views: 12

JaedenRuiner

Well-known member
Joined
Aug 13, 2007
Messages
340
Programming Experience
10+
Thanks

I will check it out. The only concern I have, is that I specifically make sure to load all parent relations recursively, so all parent tables to the table the consistently brings up the constraint error are already Filled before I Fill that table, so it shouldn't be a FK error. But I will test the nitpick error descriptor to see if it can give me a more in depth error report.
 

JaedenRuiner

Well-known member
Joined
Aug 13, 2007
Messages
340
Programming Experience
10+
Well, cjard, this is interesting, and perhaps you can explain this situation.

To Give a Quick Understanding of the Tables so we are on the same page:

  • tbl_BOL
    • BOL_ID nvarchar(12)
    • Material nvarchar(6)
    • Customer nvarchar(6)
    • Email nvarchar(10)
    • Quantity int
  • tbl_Email
    • Email_ID nvarchar(10)
    • Customer nvarchar(6)
    • Num int
    • ExpQty int
    • MailID int

Okay, so these are the offending tables, and I must say that nitpick helped me narrow down so kudos for that, very helpful.

However, as you can see tbl_email and tbl_Bol both relate to the lst_Customer table (on the customer field in both tables). The Email table, uses Customer and Num as it's Primary Key. This may seem odd at first, but it's not, the reason being is that the Email_ID field is Generated off of Customer and Num, and since i use a StoredProcedure to add records into tbl_Email it auto calculates the next Num for Customer and inserts the record for me.

I tried to make it computed and identity but it never worked to my satisfaction, as for each customer (6 char ID code) they start at email 1 and go up, the email id then becomes a string something like this:
VB.NET:
Customer: 100107
  Num : 1
  Email_ID: 107-00001
  Num : 2
  Email_ID: 107-00002
etc. With Num as an Identity, i can't reset it for each Customer, so assuming the above as two entries, Customer 100108, it's num would start at 3...i needed it to start at 1. I tried to use expression based columns but I couldn't use them as a Foreign Key for another table, and I wanted the Email_ID field (the generated field) to be the Unique Parent Column to the child FK column.
So in the end, I had to hack it a bit, making both Customer & Num the Primary key, and then enforcing Email_ID to be Unique. And so far it has worked great.

Now the Email field in tbl_BOL is allowed to be NULL, since I add the BOLs (Bill of Lading) first, and then process them per customer.
So I'm Basically, Loading in all this BOLs, and then I loop through them updating them by BOL_ID & Customer with the Max Email_ID for that customer until the Sum of the BOL Quantity >= 350, at which point i create a new Email_ID (StoredProc: Increments Num and Inserts the cust/num/generated id into the table for me and returns the new id), and then the loop continues updating more BOLs following that pattern for each customer in all the BOLs loaded until all are processed.

So, now that you see the structure of the DB, and the relation between the tbl_Email and tbl_BOL perhaps you can explain this:

Above, in a previous post I listed several functions one of which was my Dataset.Refresh() function, here it is again, but smaller for ease of reference:
VB.NET:
Public Function Refresh(ByVal tbl As DataTable)
      If tbl IsNot Nothing Then
         Try 
            Select Case tbl.TableName
               Case "tbl_BOL"
                  _manager.ta_tblBOL.Fill(tbl_BOL) 
               Case "tbl_Email"
                  _manager.ta_tblEmail.Fill(tbl_Email) 'parent to tbl_BOL
               Case "tbl_MailBox"
                  _manager.ta_tblMailBox.Fill(tbl_MailBox) 'parent to tbl_Email
               Case "lst_Customers"
                  _manager.ta_lstCustomers.Fill(lst_Customers)
            End Select
            If Not _list.Contains(tbl.TableName) Then
               _list.Add(tbl.TableName, New RexamTableInfo(RexamTableType.View, True, False))
            Else
               _list(tbl.TableName).Filled = True
            End If
            _list(tbl.TableName).Filled = True
            Return True
         Catch ex As Exception
            'I Put your  NitPick Lines Here 
         End Try
      End If
      Return False
   End Function
Okay, so a function loops through the Dataset.Tables() collection telling each one to "Refresh()" or fill itself with a TableAdapter. Ignoring all the superfluous checks and conditions the key point is this:
VB.NET:
sub MyDSManager.Refresh(tbl as DataTable)
if not  _list(tbl.tablename).filled then
  for each rel as DataRelation in tbl.ParentRelations
     MyDSManager.Refresh(rel.ParentTable)
  next
  myDataset.refresh(tbl)
end if
end sub
NOTE: That _list(name).filled is not exact but it would take too much time to copy all the code again, but just figure that when i call the MyDataset.Refresh() it flags a value for each table indicating it has been filled already, so that during the recursive operation, i don't try to fill the same table 3 times for each of its children. When i call MyDSManager.Refresh(MyDataset.tbl_BOL) before it attempts to call MyDataSet.Refresh(tbl_Bol) it first loops through the parent relations recursively and Calls in Sequence:
  1. MyDataset.Refresh(lst_Customer)
  2. MyDataset.Refresh(lst_Material)
  3. MyDataset.Refresh(tbl_Email)
    Calls:
    • MyDataset.Refresh(lst_Customer) 'list(Customer).filled = true
    • MyDataset.Refresh(tbl_MailBox)

So, I put a break point at the top of the select Case statement in MyDataSet.Refresh() after a constraint exception occurred. So, I got the error from the NitPick listing, and while the MsgBox waited for the OK, I put the break point in, and then continued execution by clicking OK. Now, once this error has occurred once, the only way to resolve it is to shut down and reopen the program, so I did not do that yet, because every time i try to fill the DS after it will have the same error, and this time I know where the offense is so i wanted to step it and see for myself. Now that the break point is in, i close out the AdminForm (which disposes the Dataset vars and everything) and then while MainForm was still active, I reopen AdminForm which tries to refill the dataset (and will cause the error).

Nitpick Reported:
2 occurrences: In tbl_Email, Column 'Customer, Num' is constrained to be unique. Value '100107, 1' is already present.
So I put In a Watch for myself:
VB.NET:
tbl_Email.Select("Customer = '100107'")
I Stepped through the Filling Process, and this is the order of what happened each time MyDataset.Refresh() was called:
  1. _manager.ta_lstCustomer.Fill(lst_Customer)
  2. _manager.ta_lstMaterial.Fill(lst_Material)
  3. _manager.ta_tblMailBox.Fill(tbl_MailBox)
  4. _manager.ta_tblEmail.Fill(tbl_Email)
  5. _manager.ta_tblBOL.Fill(tbl_BOL)
Quick Explaination: Stage 3 is the start of tbl_BOL recursive parents. First tbl_BOL tries to fill lst_Customer followed by lst_Material, but they are already filled, but tbl_Email is not, so it tries to fill tbl_Email, but that has a parent and rescurses a second time to fill tbl_MailBox. After tbl_Email has executed it's Fill, i refreshed the Watch of the tbl_Email.Select() and got 3 rows returned. I continued execution to stage 5, where it fills tbl_BOL and the exception is raised, and these were my results over both stages:
VB.NET:
[B][U]Stage 4[/U][/B]
Email_ID,	Customer,	Num,	ExpQty,	MailID
107-00001,	100107,	1,	358,		41
107-00002,	100107,	2,	353,		99
107-00003,	100107,	3,	-1,		<DBNull>
[B][U]Stage 5[/U][/B]
Email_ID,	Customer,	Num,	ExpQty,	MailID
025-00001,	100107,	1,	352,		20
107-00001,	100107,	1,	358,		41
107-00002,	100107,	2,	353,		99
107-00003,	100107,	3,	-1,		<DBNull>

So something weird is happening after the tbl_Email is filled and tbl_BOL is trying to fill. I'm rather concerned that when i fill tbl_BOL it affects tbl_Email. both of these are from tbl_Email.Select() so how could ta.Fill(tbl_BOL) add an invalid row to tbl_Email, when tbl_Email isn't even in the picture at that point?
ta_TblBOl.Fill() is the generated code that uses the typed table adapter's SqlDataAdapter.Fill() command to fill the passed datatable which in this case is tbl_Bol.
Quickly, while VB was still halted at the exception (after printing this data out in the console), I went over to Sql Management Studio for SqlSvr, and ran a query on the database there:
VB.NET:
select tbl_email.* 
from lst_customers, tbl_Email 

where lst_customers.Cust_ID = tbl_Email.Customer and
lst_customers.cust_id in ('100025', '100107')
order by cust_id

Email_ID,	Customer,	Num,	ExpQty,	MailID
025-00001	100025	1	352		20
025-00002	100025	2	352		36
025-00003	100025	3	352		36
025-00004	100025	4	352		94
025-00005	100025	5	352		94
025-00006	100025	6	-1		NULL
107-00003	100107	3	-1		NULL
107-00002	100107	2	353		99
107-00001	100107	1	358		41

So that's the problem: How is tbl_BOL when it is trying to fill itself manage to mess up so badly, that it overwrites the customer field in one of the tbl_Email entries in its own memory space, because it doesn't affect the database, it's just messing up it's own data.

Thanks
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Just to clarify and summarise:

Youre encountering an exception because one of the Fill() operations oure performing on tblEmail is causing a row to be filled:

025-00001, 100107, 1, 352, 20


Which is in direct conflict with an existing row:

107-00001, 100107, 1, 358, 41


On account of both rows having 100107, 1, which is constrained to be unique on the client side, but not on the server side.

Given that something appears to be changing the Customer field, I'd break on that property, or set a watch so that when the value of Customer changes, the code breaks.

I have a feeling that Customer is being filled as 100025, but edited to 107 by some relationship enforcement.. I can't quite see it being possible that the value is being downloaded incorrectly. One thing I'm puzzled about though; how do you parameterise the queries so you don't pick up every email? I see nothing that obviously restricts the data that is downlaoded from the DB..
 

JaedenRuiner

Well-known member
Joined
Aug 13, 2007
Messages
340
Programming Experience
10+
Youre encountering an exception because one of the Fill() operations oure performing on tblEmail is causing a row to be filled:
No, it's the Fill() operation on tblBOL that causes the row error in tblEmail.
025-00001, 100107, 1, 352, 20
Which is in direct conflict with an existing row:
107-00001, 100107, 1, 358, 41
This is correct in the nature of the conflict.
On account of both rows having 100107, 1, which is constrained to be unique on the client side, but not on the server side.
No, the constraints are IDENTICAL both in the Database and in the DataSet. I mimicked them to the letter on both sides. I'm used to databases, not datasets, so I design everything in the database and see how it interacts, I set my FK, Delete/Update rules and my Unique Fields, etc, all in the Database.
Then I generate the Dataset for it, and go through all the relationships it interpreted and update them to make sure each column, and each relationship is identical to the server.
I have a feeling that Customer is being filled as 100025, but edited to 107 by some relationship enforcement..
Well, I'm using Express, so I can't do conditional breakpoints. However, that idea that a relationship enforcement is causing an edit of some sort does not make total sense, because I just closed the application, and then restart it, and voila, it does not have a constraint error, and that row that the customer was altered from 100025 to 100107 is 100025 again as it should be. The error is not 100% reciprocal, because I'll run it and nothing will happen. Then I'll play around with my interface, viewing different tables via a combobox that lists the table names, close the AdminForm, open the AdminForm, run an Import process (that adds new BOLs and Emails) open AdminForm, play around viewing different tables. The Classic "stress" testing to see what will break, and then somewhere in the middle this error will occur.

A thought did come to mind about the possibility of Control Bindings Affecting the Values in the DataSet during Download from DB. I have two Methods I added, because i was having some binding issues during connect and disconnect of the DataSet to the Database, along with other threading issues that got in the way. I figured, when the form opens, I'll create the connection, InitDataBindings(), and MyDSManager.FillDataset() which is what handles all the refreshes of dataset.tables() and when it closes I'll DisconnectBindings() and dispose the dataset, and close out the sqlconnection, so that when a different thread needs to access the database it is the only one active at the moment. Now InitDataBindings() and DisconnectBindings() basically add/remove all bindings from EVERY control that has one on the form. So my InitializeComponents() method in the designer has NO DATABINDING lines whatsoever, it is all handled programmatic in those two methods. It came to me that it might be possible that the current "customer" in the Customer Details View might be set to 100107, and the current Email in the Email Details view could be set to 025-00001 and somehow the controls are affecting the edit (if that is possible during fill)

One thing I'm puzzled about though; how do you parameterise the queries so you don't pick up every email? I see nothing that obviously restricts the data that is downlaoded from the DB..
Which Queries that pick up every email? i'm not sure i understand the question.
 

JaedenRuiner

Well-known member
Joined
Aug 13, 2007
Messages
340
Programming Experience
10+
It came to me that it might be possible that the current "customer" in the Customer Details View might be set to 100107, and the current Email in the Email Details view could be set to 025-00001 and somehow the controls are affecting the edit (if that is possible during fill)
BINGO!!!

After writing this, i thought to myself while I wait for a response I might as well test my theory. I found that when I viewed the Email Table, whose first entry is '025-00001', and then switched over to view the Customer Table, selecting customer '100107' from the list, and then Disconnected, my code was disconnecting the DataBindings before disconnecting/disposing the Dataset, so my TextBox fields were remaining filled at 025-00001 and 100107. Upon re-opening the form, the dataset is created (so the pointer to it is valid) and then i would Reconnect the DataBindings before filling the dataset, so that first email entry, through whatever order of operation was occurring, was getting reset to customer 100107 by the textbox and caused the error. Debugging on the fly, I reworked the code to dispose the dataset, (order of op: clear dataset, disassociate TA manager from connection, disconnect, and then it continues with the dispose protocols of ta manager.dispose, _dataset.dispose) and then disconnect the bindings. This causes all the text fields etc to revert to blank, so when I fill the dataset and reconnect the bindings there is no error, and without having to restart the app it loads every time now.

It took me a while but finally got this glitch wrapped up. Thanks for all the help, and for that NitPick dll, which made it a whole heck of a lot easier to find the cause.

Cheers!
 
Top Bottom