Results 1 to 31 of 31

Thread: Constraint Exception

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Constraint Exception

    I called the method below and got the message below. The way I read that is that the method has resulted in more than one record, which would have caused the exception, each having a unique PK. I have checked the query and it does exactly as desired, which is filter for one specific record.


    Code:
    If (Me.TblDocMasterTableAdapter.FillByChangeID(Me._MasterBase5_0DataSet.tblDocMaster, glbintChangeID)) = Nothing Then
    Name:  errormessage.jpg
Views: 1078
Size:  21.1 KB

    Also, of note is that this called from a DGV in another form, as seen below

    Code:
        Private Sub dgvList_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvList.CellDoubleClick
            'Define variable values from record
            glbintChangeID = CInt(Me.dgvList.Rows(e.RowIndex).Cells(1).Value.ToString)
            glbintSiTechID = CInt(Me.dgvList.Rows(e.RowIndex).Cells(0).Value.ToString)
            glbstrTitle = CStr(Me.dgvList.Rows(e.RowIndex).Cells(2).Value.ToString)
            glbstrRevision = CStr(Me.dgvList.Rows(e.RowIndex).Cells(3).Value.ToString)
            If glbstrObject = "Document" Then
                Select Case glbintForm
                    Case = 1
                        frmDocumentRecord.Show()
    This is of note because if I select the first row displayed in the DGV the exception is not thrown. If I select any other row below that the exception is thrown.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Constraint Exception

    That error message mentions three types of constraints, so it could be any of them that's causing the issue. Does the table you're querying contain any foreign key columns? If you're filling by ChangeID then I suspect so. If so, have you already retrieved the related records for this record? If you haven't then you'll be violating a foreign key constraint, i.e. you've retrieved a child record that has no parent record in the DataSet. In that case, you have two options:

    1. Retrieve the parent record into the DataSet.
    2. Don't use a DataSet.

    The second option is done by calling GetDataByChangeID instead of FillByChangeID. GetData* methods return a standalone DataTable that is not subject to foreign key constraints. You could also stick with Fill* and pass a standalone DataTable that you create yourself instead of one you got from a DataSet.

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    You nailed it on the foreign key. I am not familiar with the GetData methods. Could you perhaps provide some instruction on that?

    The table I am using has three records. This works for the first record and the third record, but not the second record. I still do not understand why that would be.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Constraint Exception

    You call the appropriate GetData method and it returns a DataTable. That's really all there is to it. What you then do with that DataTable is up to you, e.g. assign it to the DataSource property of a DataGridView.

  5. #5

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    Why thanks JM. I was not aware that if you call a method that it would return something. To think that I have been using methods all of this time and did not know what their purpose was.

    I was asking how you appropriately call a DetData method. Is there some reason that you are unwilling to answer that question JM? If you are not interested in providing an answer then why not just ignore the question?

  6. #6
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: Constraint Exception

    You call methods all the time. I do think JMC is being needlessly vague, but I think the question's phrased wrong. Just to be clear, though:

    A "method" is the right name for "a function or a sub". Other languages just call everything "a function" but VB distinguishes. So "a method" becomes more convenient in VB discussions, even though it's a more general .NET term.

    So when a method returns a value, you usually store it in a variable:
    Code:
    Dim result As Integer = Integer.Parse("10")
    Integer.Parse() is a Shared Function, aka "a static method" in .NET words. It returns an Integer, and that Integer is being stored in a variable named 'result'.

    JMC is assuming you are using some designer-generated magic with your DataSets. There's some way via VS wizards that you can ask it to look at your database and automatically generate DataSet objects and helper types to work with that. Part of that magic is a lot of methods called "GetDataBy???" that serves to get a DataSet that looks a certain way based on some queries. He can't tell you exactly the right name because the names depend on your specific database columns. People who use this code just call them the "GetData" methods because their names always start with "GetData".

    Maybe you aren't using that magic, that would be a good explanation why you don't have those methods. A lot of VB developers just assume you're using that magic, because it seems to be the easiest approach to them. If you aren't using that magic, I forget what the keywords for that is. I'm sort of surprised the wizards still exist, MS has been recommending EF for 5 or 6 years now. It's the same problems with different magic.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: Constraint Exception

    I think you misread his answer in #2. GetData just gets a datatable. Whether or not that solves your problem is quite a different question. As JMC pointed out, it WILL bypass your problem, because GetData methods return a standalone datatable that isn't subject to the foreign key constraints. So, if foreign key constraints were the issue, then GetData would avoid the issue....but may not solve the problem. Is the lack of foreign key constraints going to cause you trouble? It might, since you might be wanting those constraints for entering data, or because you want something from the parent row. On the other hand, you might be just fine with the datatable returned as it stands. All depends on what you want to do next.
    My usual boring signature: Nothing

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    Sitten,

    Personally, I would not have chosen to call a query a method. But that was something that was seemingly very important to JM, so I acquiesced. There is no designer generated magic, that I know of, with my dataset. All this is, is a query call that should return a single record, if one exists, that meets a parameter criteria. The reason that it is enclosed in an If branch is so that if the table happens to not have any records in it, the user is so informed and can move on.

    The table that I am calling the query for is related, by a foreign key, to another tables primary key. The parameter that is used is intChingeID, which is also the field that is a foreign key. This table currently contains three records. Using the parameter value to specify the records, I can run the query in the tableadapter and successfully return each of the three records. If I use the parameter value by calling the query in the code, as displayed above, records one and three are successfully returned, but not record two. I see no designer generated magic with this, although it may well exist.

    As for the GetData method, I am completely at a loss as to what this is or how to use it. In my tableadapters I have noticed that a GetData method is created whenever I create a query. Now, assuming that JM was referring to these methods, he did explain what they do. However, I have never used them and have no idea what the syntax would be for using one. I know of no magic that I am using and if this is magic, then I would just like an explanation I can understand for how this magic is used.

    It would also be cool to know why one record of the three that exist throws an exception. I have stepped through the code numerous times and have stared at the record for hours (a gross exaggeration, but I have spent a fair amount of time), and have been unable to figure out what is wrong with this record or the query call.

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    Shaggy,

    I understand that. What I do not understand is what the syntax is for using GetData in the situation where I am using FillBy. Nor, as far as I can see, does that address why the exception occurs with the method (query) I am using.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,181

    Re: Constraint Exception

    Nor, as far as I can see, does that address why the exception occurs with the method (query) I am using.
    It was answered in Post #2 by jmc. Also I would be very care about using the GetData, you could end up corrupting your database by bypassing the constraints. you setup the constraints for reason, right??

    When your working with Parent/Child tables in a Typed DataSet, with a relationship with constraints, you can't be Editing/Adding/Deleting with just one of the tables open. It's to keep from doing things like deleting a record from the Parent table and not deleting the associated records in the Child table.

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    It was answered in Post #2 by jmc.
    It was partially answered. Had the answer been complete it would have included some information about how that method is called and used. As far as constrains, I set nothing up. All I did was set a relationship between the foreign key in the table tblDocMaster, child table to the Primary key in tblChangeRequest. parent table. I checked the relationship in the tableadapters and it is set to relationship only.

    I did notice that there was no tableadapter for the parent table in the form properties and put that in then called the fill query (or method) in the load event (both tables should now be open. However, the record in the child table still threw the exception when the query was called using the parameter value that specifies that record.

    I am wary of just going to a method, GetData, that I am unfamiliar with and am able to obtain no useful information about. I would prefer to use the method I am currently using, if possible. I would like to understand why the method I am using is throwing an exception for one particular record and not others?

  12. #12
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: Constraint Exception

    Actually, you ARE using some magic, and that may be something you might consider moving away from. You talk about TableAdapters. That's the magic. Those were only added a few years into .NET (I forget when, and they may have been around from the beginning), but they aren't necessary. When you use TableAdapters, you are letting some framework voodoo create a whole bunch of stuff for you that you have relatively little control over. There are parts of that that I find quite unpleasant, and all of it I find fairly opaque. You CAN find all of what it's doing, so it's not totally opaque, but you are exchanging a bit of ease for a bit of opacity, and I don't like that trade. On the plus side, you get strongly typed datatables, which certainly do have an advantage, but not that great an advantage in my view.

    You can get away from all that if you so chose, and it would mean less code and more clarity. It would also (generally) mean a bit more work, at least until you get used to it. For me to create a datatable, I would do something like this:
    Code:
    dim dt As New Datatable
    
    Using cn As New SqlClient.SqlConnection(myConnectionString)
     Using cmd As SqlClient.SqlCommand = cn.CreateCommand
      Using da As New sqlClient.SqlDataAdapter
        da.SelectCommand = cmd
        cmd.CommandText = "Some Query String Here"
        Try
          da.Fill(dt)
        Catch ex As Exception
          'Do whatever here.
        End Try
       End Using
      End Using
     End Using
    Some of those using blocks aren't technically necessary, and there are other ways to create the command object and dataAdapter such that you consolidate some lines. Furthermore, you could catch specific DB related errors. However, that's the whole thing. This would create a datatable, and that's the whole code with no actions taken other than writing that code.

    My experience with TableAdapters is that you are in various designers doing a variety of things. It's not horrible, it's just confusing. So, for example, you are baffled by those GetData things. There's nothing like that in the raw SQL approach I just showed. No designers, no nothing, other than code. Also, that's not a strongly typed datatable, it's just a plain old datatable.

    You might consider that. Those steps are all (roughly) things you are doing with a TableAdapter, but you don't see that work.
    My usual boring signature: Nothing

  13. #13
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: Constraint Exception

    Quote Originally Posted by gwboolean View Post
    As far as constrains, I set nothing up. All I did was set a relationship between the foreign key in the table tblDocMaster, child table to the Primary key in tblChangeRequest. parent table.
    That's a constraint. When you set a relationship between two tables, you have created a constraint.
    My usual boring signature: Nothing

  14. #14
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,181

    Re: Constraint Exception

    Quote Originally Posted by gwboolean View Post
    It was partially answered. Had the answer been complete it would have included some information about how that method is called and used. As far as constrains, I set nothing up. All I did was set a relationship between the foreign key in the table tblDocMaster, child table to the Primary key in tblChangeRequest. parent table. I checked the relationship in the tableadapters and it is set to relationship only.

    I did notice that there was no tableadapter for the parent table in the form properties and put that in then called the fill query (or method) in the load event (both tables should now be open. However, the record in the child table still threw the exception when the query was called using the parameter value that specifies that record.

    I am wary of just going to a method, GetData, that I am unfamiliar with and am able to obtain no useful information about. I would prefer to use the method I am currently using, if possible. I would like to understand why the method I am using is throwing an exception for one particular record and not others?
    Yes you did set something up. You did it when you created the database table structures. Look at the database table definitions. It should be very easy to find useful information on the GetData method by searching this forum or using Google. But I'm glad to here you don't want to use it. As for why your getting an error, I can't really tell because I don't have enough information about the database structures or data. Also, it would be important to know where and the datatables were being filled. So providing us with more of the relevant code would help.

  15. #15

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    I would be happy to. However, the code that I include is what I believe to be relevant and might well not be. Here is the process:

    I begin with a DGV list of tblDocMaster, in a form. The query call is placed in an if branch so that if no records are returned the user would know that there are no records in that table and can move on.

    Code:
                        If (TblDocMasterTableAdapter.Fill(Me._MasterBase5_0DataSet.tblDocMaster)) = Nothing Then
                            Try
                                Throw ErrorMessage
                            Catch ex As Exception
                                Dim exQueryFailure As New ErrorCode1001
                                Dim strMessage As String = "There are no Documents in this system."
                                exQueryFailure.strMessage = String.Format("{0}", strMessage)
                                MsgBox(exQueryFailure.strMessage)
                                Me.Close()
                                mnuMasterBase.Show()
                            End Try
                        Else
                            DocListLoad()
                        End If
    At this point the routine DocListLoad() is called. The purpose is to display a DGV list from a defined table, in this case tblDocMaster.

    Code:
        Private Sub DocListLoad()
            With dgvList
                .DefaultCellStyle.Font = New Font("Times New Roman", 11)
                .DefaultCellStyle.ForeColor = Color.Black
                .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            End With
            dgvList.DataSource = Me._MasterBase5_0DataSet
            dgvList.DataMember = "tblDocMaster"
            With dgvList.Columns(0)
                .Visible = True
                .Width = 100
                .HeaderText = "Document ID"
            End With
            With dgvList.Columns(1)
                .Visible = True
                .Width = 350
                .HeaderText = "Title/Name"
            End With
            With dgvList.Columns(3)
                .Visible = True
                .Width = 100
                .HeaderText = "Revision"
            End With
            dgvList.Columns(2).Visible = False
            dgvList.Columns(4).Visible = False
            dgvList.Columns(5).Visible = False
            dgvList.Columns(6).Visible = False
            dgvList.Columns(7).Visible = False
            dgvList.Columns(8).Visible = False
            dgvList.Columns(9).Visible = False
            dgvList.Columns(10).Visible = False
            dgvList.Columns(11).Visible = False
            dgvList.Columns(12).Visible = False
        End Sub
    The list is displayed of all existing records in that table. If there are no records in the table a message is generated and the user sent back to the menu form.

    At this point the user double clicks in a cell in a row of the DGV. The purpose of this event is to open a form that will display a record from tblDocMaster based on the selection made with the double click.

    Code:
        Private Sub dgvList_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvList.CellDoubleClick
            If glbstrObject = "Document" Then
                Select Case glbintForm
                    Case = 1
                        'Define variable values from Document record
                        glbintChangeID = CInt(Me.dgvList.Rows(e.RowIndex).Cells(1).Value.ToString)
                        glbintSiTechID = CInt(Me.dgvList.Rows(e.RowIndex).Cells(0).Value.ToString)
                        glbstrTitle = CStr(Me.dgvList.Rows(e.RowIndex).Cells(2).Value.ToString)
                        glbstrRevision = CStr(Me.dgvList.Rows(e.RowIndex).Cells(3).Value.ToString)
                        Me.Close()
                        frmDocumentRecord.Show()
    At this point frmDocumentRecord is opened with the load event

    Code:
        Private Sub frmDocumentRecord_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Me.TblChangeRequestTableAdapter.Fill(Me._MasterBase5_0DataSet.tblChangeRequest)
            Me.LkpWhereTableAdapter.Fill(Me._MasterBase5_0DataSet.lkpWhere)
            Me.LkpDepartmentTableAdapter.Fill(Me._MasterBase5_0DataSet.lkpDepartment)
            Me.LkpDocTypeTableAdapter.Fill(Me._MasterBase5_0DataSet.lkpDocType)
            Select Case glbintForm
                Case = 1
                    DocMaster()
                    DisplayOnly()
                    DisplayByChangeID()
    The routine DocMaster() sets form properties that are specific for document records.
    The routine DisplayOnly() sets specific control properties. In this case all are enabled = False or ReadOnly = True.
    The routine DisplayByChangeID() displays a specifically defined document record. As before, the query is placed in an If branch that will trigger a message if no records are returned.

    Code:
        Private Sub DisplayByChangeID()
            If (Me.TblDocMasterTableAdapter.FillByChangeID(_MasterBase5_0DataSet.tblDocMaster, glbintChangeID)) = Nothing Then
                Try
                    Throw ErrorMessage
                Catch ex As Exception
                    Dim exQueryFailure As New ErrorCode1001
                    Dim strMessage As String = "There are no documents in this system."
                    exQueryFailure.strMessage = String.Format("{0}", strMessage)
                    MsgBox(exQueryFailure.strMessage)
                    Me.Close()
                    mnuMasterBase.Show()
                End Try
            End If
        End Sub
    The exception that I am getting is thrown at the line with the If() branch in the DisplayByChangeID() routine. Here is the content of the three records in the table

    Name:  tblDocMaster.jpg
Views: 911
Size:  7.8 KB

    Name:  tblChangeRequest.jpg
Views: 911
Size:  8.5 KB

    Here is the relationship, as described in the database and the table adapter.

    Name:  DBProperties1.jpg
Views: 959
Size:  37.2 KB

    Name:  Relationship1.jpg
Views: 949
Size:  38.4 KB

    I am not sure what else might be pertinent. But the process is described by a need to display a list of existing records in a table in Form A. Select one record from the display list then close the form and open another form that would display the contents of the selected record.

    As stated previously, the exception is thrown during the execution of the query call noted above, but only when a specific record of three records is called using the query.

    Although I might be completely wrong, it appears to me that the solution lies in determining why the error occurs with one record, but not the others. What other information might be helpful?

  16. #16
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: Constraint Exception

    Personally, I would not have chosen to call a query a method. But that was something that was seemingly very important to JM, so I acquiesced.
    Let's be very clear: when you have .NET code working with a database, we have two contexts. It is very, very important that we call things the right names so we understand the context.

    A "SQL query" is something like "SELECT * FROM customers". You execute that query against your SQL server/client. To do that in .NET, you create some type that has a method that will go out and execute the query for you.

    So it's not quite right to call the .NET thing "a query", because it's "a method that executes a query". Your post is not one of them, but I've seen posts that mix the words up to the point that I need the other person to draw me a picture so I can understand them.

    DataSets introduce a similar multi-context problem. VB .NET is a strongly-typed object-oriented language. Databases are relational, table-based things that aren't always strongly typed, and their types don't always naturally map to the .NET concepts.

    I can't even begin to get into how much I hate DataSet, TableAdapters, and in particular the VS wizards that vomit those things out. I tried a few times to come up with productive reasons why, but really it just comes down to:

    I hear lots of horror stories from people who use the "magic". They always find some dark corner of their tool and have to find some barely-documented way to dig out. Sometimes they're the first person to find that corner. The trick to using magic tools is you have to carefully study the example applications, because those show you the use cases the "magic" is designed to handle. Then you have to make sure if a customer asks you for something that doesn't look like the example application, you have a good place to dispose of the customer's body.

    I don't hear horror stories from people that write the "magic" themselves when the project is complex. Instead of "I spent a month hand-optimizing my Rails code and now the 2-hour query takes 15 minutes", their story is, "I finished the query today, it runs in 10 minutes."
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  17. #17
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: Constraint Exception

    That's pretty much my view on it, too. I did use the magic stuff one time, and I do pretty much see why people do it, but I'm still not all that interested. I want to see into the black box a bit better than that.
    My usual boring signature: Nothing

  18. #18
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,181

    Re: Constraint Exception

    If (TblDocMasterTableAdapter.Fill(Me._MasterBase5_0DataSet.tblDocMaster)) = Nothing Then
    Try
    Throw ErrorMessage
    Catch ex As Exception
    Dim exQueryFailure As New ErrorCode1001
    Dim strMessage As String = "There are no Documents in this system."
    exQueryFailure.strMessage = String.Format("{0}", strMessage)
    MsgBox(exQueryFailure.strMessage)
    Me.Close()
    mnuMasterBase.Show()
    End Try
    Else
    DocListLoad()
    End If
    I have to be honest this code baffles me, why would you want to throw an error, why not just display a MessageBox. I've never seen a Try/Catch used in this way and I don't think it's a good idea. but maybe you know something I don't.

    You showed a relation setup in the database for tblChangeRequest and tblDocMaster, then you show a relation in the Dataset for tblItemMaster and lnkVendorspec and you show two tables with three records each (but I don't know which tables they are). If these are the tables with the constraint that's causing the problem what are their names, which is the Parent and which the child, what is their Primary Key.

    This seems to be what's causing the problem "_MasterBase5_0DataSet.tblDocMaster" , is the datatable opened/filled anywhere else? Is it bound to anything else.

    Just to be clear, is this a SQL Server database or something else.

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    I might well agree with both of you. However, you refer to the use of magic stuff. I am not clear on what exactly constitutes the magic stuff that I should not be using and/or even what has been resolved.

    Really guys, I am just attempting to define a problem and gain an understanding of what and why I have the problem. So let me attempt this at my level and see if I can gain some understanding.


    I have an exception that is thrown when I execute a line of code.

    1. The exception, noted above is thrown when the line of code noted above is executed.
    What have I learn from that? Short version: That this is thrown when a relation constraint is violated between a parent and child table.
    Is this the magic you refer to?
    2. The exception is thrown for only one record, but not the others.
    What did I learn from that? Short version: Nothing. I have no idea.
    Is it because this record violates the relationship and not the others? I have found no reason why this record would violate the relationship.
    Is this the magic you refer to?

    So where can I go from here?

  20. #20
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,181

    Re: Constraint Exception

    I tried to recreate your problem but no luck.
    Code:
    Public Class Form4
    
    
        Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'RecipesDataSet.tblRecipeDetails' table. You can move, or remove it, as needed.
            Me.TblRecipeDetailsTableAdapter.Fill(Me.RecipesDataSet.tblRecipeDetails)
            Me.TblRecipeDetailsDataGridView.DataSource = Me.RecipesDataSet.tblRecipeDetails
    
    
        End Sub
    
        Private Sub TblRecipeDetailsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles TblRecipeDetailsBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.TblRecipeDetailsBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.RecipesDataSet)
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If Me.TblRecipeNamesTableAdapter1.FillByRecipeNamesId(Me.RecipesDataSet.tblRecipeNames, CInt(Me.TblRecipeDetailsDataGridView.CurrentCell.Value.ToString)) = Nothing Then
                MessageBox.Show("Not Found")
            Else
                Me.DataGridView1.DataSource = Me.RecipesDataSet.tblRecipeNames
            End If
        End Sub
    End Class
    The tblRecipedetailsDataGridView does show a "!" next to each row because of the constraints but I don't get a constraint error when I click Button1.

    In this test tblRecipeNamesId is the Parent and tblRecipeDetails is the child and I setup the relationship to enforce all constraints.

  21. #21
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: Constraint Exception

    The magic stuff I am referring to is TableAdapters. I really don't know what the general term for that is. Perhaps anything produced by the Datasource wizard? Though that may just be a means of setting up data connections in the config file. That was what I was trying to get at in post #12.

    You are using tableadapters. To do that, you probably used some wizards or whatnot to graphically (and semi-graphically) connect to the database and get various things from it. That's one way to go, but not one I like. What I was showing in #12 is an approach that is all in code and doesn't use any of that graphical stuff, or wizards, to create a datatable. Yeah, you don't get a tableadapter, but they seem to be more trouble than they are worth. You won't get all that FillBy stuff, either. The plain old DataAdapter has nothing more than a Fill method. No overloading that to have different FillBy methods with different criteria. You just have Fill, and if you want to get different records, you change the SQL of the SelectCommand of the DataAdapter.
    My usual boring signature: Nothing

  22. #22

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    OK, I finally get where you guys have gone.

    I have learned that:

    1. tableadapters are bad, datasets might be bad and the use of wizards should be avoided at all costs.
    2. What has gone wrong for me is probably not reproducible.
    3. That I can remediate the problem and many future problems, by creating an instance of the table, instead of using a tableadapter, and executing the query on the instance.

  23. #23
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: Constraint Exception

    It's easier to explain the magic in terms of maybe something else.

    Imagine I have a Customer object, and I want to write it to a file.

    I could choose any number of text formats, let's say I choose XML. I then have to decide what I want the file to look like. I need to write code to output XML with an element for each property. I need to write code to open XML, read each element, and parse the value into a property. Then I'm done.

    Or I could use XmlSerializer. It can write objects to a file and read them back in one method call.

    The only reason I don't use XmlSerializer all the time is when I use it, the XML input and output have to follow certain rules. And sometimes I'm not the one who decides what the XML should look like, sometimes it's a customer. You can do some things to control how XmlSerializer parses/writes files, but if the customer changes their mind a lot, or I have to support many versions of the format, there's no way to make XmlSerializer work for me.

    That's the story with "database magic". It promises to give you a ton of features with "zero lines of code". But it was designed based on what MS decided a "reasonable" database app might do. It has features that not every database app wants or needs. Support for those features can make it generate queries that aren't as fast as queries you'd write yourself.

    Using "magic" to do complicated things is often harder than it seems. You have to know how each feature relates to your application, and what code it generates, and how to turn it on or off. You need to know where extensibility points are and how to use them. You need to know what the magic does well, and what it doesn't.

    Once you know all that, you will know when it's faster to use the magic and when it's faster to write it all yourself. Part of knowing all of that is being able to write it yourself! A lot of people confuse "hard" and "tedious". They aren't the same thing.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Constraint Exception

    OK. I can see all of that. I still get out of it that tableadapters, etc. are not the best choice and that I should move on to a more manual approach. So here is what I currently can see to resolve this particular issue.

    1. The first thing is to create an instance of the table I am working with. and then run a query from within the code (not using a table adapter generated query).
    I have seen something like this before. I previously was unable to really comprehend what was taking place with this, but I think I can figure out how to use this for what I am doing.

  25. #25
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Constraint Exception

    Quote Originally Posted by gwboolean View Post
    OK. I can see all of that. I still get out of it that tableadapters, etc. are not the best choice and that I should move on to a more manual approach. So here is what I currently can see to resolve this particular issue.
    Then you get wrong. There are people who don't like typed DataSets but, in my experience, they don't know how to use them properly. There are advantages and disadvantages to any approach but there's nothing wrong with using typed DataSets, especially for relatively simple scenarios. All you actually have to do is what I said in the first. You claim to know how to call a method and yet want special instruction on how to call this one for some reason. Your determination to believe that this situation is somehow special is what created this mess. Just call the method!
    vb.net Code:
    1. Dim myDataTable = Me.TblDocMasterTableAdapter.GetDataByChangeID(glbintChangeID)
    Also, let me be clear about something. I chose to call this a "method" because that's exactly what it is. I think it's excellent that you wouldn't choose to call a "query" a method. Neither would I, and I didn't here. GetData* methods ARE methods. They are NOT queries. If you'd just accepted that at face value in the first place then I think that you could have avoided a lot of angst because, as you said yourself, you already know how to call methods.

    Let me also add that if you choose to write your own ADO.NET code, including SQL (the SQL code is the query, by the way), then there's nothing wrong with that. Some people prefer it because it means that you have more direct control over various aspects and that can also lead to a greater understanding of those aspects. That said, you can gain a much better understanding on typed DataSets with a bit of reading on the subject.

    https://msdn.microsoft.com/en-us/library/7zt3ycf2.aspx

    A typed DataSet contains all the same types under the hood as you would use yourself if you wrote all the code manually. For instance, a table adapter is basically a wrapper for a data adapter (a SqlDataAdapter if you use SQL Server, an OleDbDataAdapter if you use Access, etc) with the SQL code in the commands generated specifically for the database you're using. In a great many cases, the SQL code generated by the wizard will be exactly the same as the SQL code you would write yourself anyway and you would call Fill on a data adapter instead of Fill on a table adapter. Woohoo! Great advantage to writing it yourself. As I said, there are plenty of people who recommend against using typed DataSets because of certain "issues" that may be encountered when using them. Most of those issues have fairly simple solutions that those people simply don't know about because they haven't used typed DataSets enough themselves.

    Also, a lot of the bad feeling around typed DataSets is due to the fact that people drag and drop them into forms, thus coupling their data access code and their presentation code. I'm no great fan of that but writing your own data access code is not going to inherently fix that. Most people start out by writing their data access code in their forms anyway. Apart from that, you can use typed DataSets and still completely decouple your data access code from your presentation code. The two are simply not related.

    In short, there's nothing wrong with writing your own ADO.NET code but there's also nothing wrong with using typed DataSets. Switching from the latter to the former has no inherent advantage. The former may well be better for complex situations but the latter will save you effort in simpler situations. Regardless of which way you go, the better understanding of what you're doing you have, the better the results will likely be. Of course, I would tend to recommend the Entity Framework for data access of over raw ADO.NET or typed DataSets. Maybe today is not the day to get into that though.

  26. #26
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,181

    Re: Constraint Exception

    jmc,
    There are people who don't like typed DataSets but, in my experience, they don't know how to use them properly
    I totally agree. There's no "Magic". When you use a designer there is some underlying code added. Is it Magic when you add a TextBox to a form using the IDE. It's the same concept, code is created for you. I had some mishaps using the Datasource wizard but it was because I didn't understand what I was doing. Unlike Shaggy, the more I understand about ADO .Net the more I like Typed Datasets. For the types of things I do they are my goto choice. TableAdapters or DataAdapters is just a personal choice. No matter which one you choose, you have to know what your doing and understand how to use the objects.

  27. #27
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Constraint Exception

    Quote Originally Posted by wes4dbt View Post
    Is it Magic when you add a TextBox to a form using the IDE.
    I often use similar examples myself. I've seen many people recommend not using typed DataSets because you have more control if you write all your queries and ADO.NET code yourself. Why is having more control a selling point if you don't need more control? As someone who has used typed DataSets and written my own ADO.NET code in the past, I took advantage of the tools that VS provides when they were appropriate and did more by hand if and when it was advantageous to do so. To write more code in order to get more control that you don't need is not being productive.
    Quote Originally Posted by jmcilhinney View Post
    There are people who don't like typed DataSets but, in my experience, they don't know how to use them properly.
    I wanted to qualify this because I find that people sometimes like to read more into my comments than is actually there because I don't especially try to soften them. This was not intended to be a pejorative but merely a statement of fact. If people haven't used typed DataSets much then of course they won't know how to use them as well as someone who has. There's plenty of things that I don't know how to use properly. I've seen plenty of occasions where someone has recommended against using a typed DataSet because of some issue that isn't really an issue, but rather it's a situation that they know how to handle in raw ADO.NET because they have experience doing so but they don't have similar experience with a typed DataSet. I would count both Shaggy Hiker and Sitten Spynne amongst those who tend to suggest to people to write their own ADO.NET code but who both have openly admitted that they have limited experience with the alternative. As someone who has a reasonable amount of experience with both, I can say that both have their strengths and weaknesses and you shouldn't not use a typed DataSet in one situation that it is suited to simply because it's not suited to another situation that you don't currently find yourself in.

  28. #28
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: Constraint Exception

    Good backpedal.

    Right now it looks like neither one of us understands how to use typed datasets to solve the problem. Maybe work on that. The way to look smart is to answer a question, not brag that you have the knowledge and not dole it out.

    My personal beef is I can't find documentation for this **** at all. I had 4 hours alone tonight and I'm the kind of nerd that would've loved to have spent it poking at the wizards and writing a tutorial. Instead I watched a bunch of Season 1 of Twin Peaks and told a story in a fantasy world, because it seems like even Microsoft wants to forget about this in favor of EF. Prove me wrong!

    Otherwise I'm going to answer the question as best as I can and continue to insult both typed datasets and the people who read an MSDN magazine article in 2004 and decided they were the only possible solution to any database application.

    I've got 10 years of projects with my approach behind me, you've got I don't know how many with yours. The difference is one of us wants to share.
    Last edited by Sitten Spynne; Nov 4th, 2017 at 10:45 PM.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  29. #29
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Constraint Exception

    Quote Originally Posted by Sitten Spynne View Post
    Good backpedal.
    If, by "backpedal", you mean "clarification" then thank you. If you mean that I'm pretending that I didn't mean what I actually did mean then I congratulate you on knowing me better than I know myself.
    Quote Originally Posted by Sitten Spynne View Post
    Right now it looks like neither one of us understands how to use typed datasets to solve the problem. Maybe work on that. The way to look smart is to answer a question, not brag that you have the knowledge and not dole it out.
    OK, now a statement of fact has become bragging. Do you wonder why I clarified my earlier comment? I don't actually care about whether I look smart or not, but it seems to be something that you're very preoccupied with. The original issue appears to be that populating a child table in a DataSet without populating a parent table violates a constraint. The solution I provided addresses that issue. I said that the OP should calla method. By the OP's own sarcastic admission, they know how to call methods. Where exactly is the issue?
    Quote Originally Posted by Sitten Spynne View Post
    My personal beef is I can't find documentation for this **** at all.
    I guess I must have just made up what I know or absorbed it from the ether then. The fact that you can't do something doesn't mean that it can't be done.
    Quote Originally Posted by Sitten Spynne View Post
    it seems like even Microsoft wants to forget about this in favor of EF. Prove me wrong!
    Why would i even try? Did you miss the part where I said that I use EF myself and would recommend that for data access?
    Quote Originally Posted by Sitten Spynne View Post
    Otherwise I'm going to answer the question as best as I can and continue to insult both typed datasets...
    They always hate what they don't understand.
    Quote Originally Posted by Sitten Spynne View Post
    ...and the people who read an MSDN magazine article in 2004 and decided they were the only possible solution to any database application.
    Please, don't let facts stand in your way. One of us is suggesting doing away with a perfectly good typed DataSet and writing new ADO.NET code where the other is suggesting that both options have their strengths and weakness and may thus be better in different situations, while also suggesting that they consider a third option to be the best overall. Which one of us is suggesting that there's only one possible solution?
    Quote Originally Posted by Sitten Spynne View Post
    I've got 10 years of projects with my approach behind me, you've got I don't know how many with yours. The difference is one of us wants to share.
    But not to brag, right? If that's what we're doing, I have 15 years experience with ADO.NET, typed DataSets and EF. I have a good feel for all three and the relative differences. I did share a solution. If the OP refuses to call a method while simultaneously insisting that they know how to call a method then that's on them.

    I am officially done with this bollocks so you're free to share without my interruption.

  30. #30
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: Constraint Exception

    OK gwboolean, let's try to get back on track. I think I see some miscommunication here that spiraled a little out of control.

    The miscommunication happened when you surprised JMC by asking, "How do I call that method?" He read it a little wrong, as if you were asking, "How do methods work." I read it that way, too. I think you meant something different, but because that question wasn't clear, JMC got distracted by the weirder interpretation. Then you got flustered because he was flustered, and a lot of stuff happeend.

    SO. Let's summarize the thread. Some of this you probably already know, but I think you should make sure not to skip a paragraph.

    Any kind of foreign key dependency in your database is a "constraint". That's because if there's a Master row that is trying to reference a Detail row with Id 2, and you delete the Detail row with id 2 without deleting that Master row, the Master row is now invalid. In general, we NEVER want our databases to even be temporarily invalid, and constraints help us do that. If you try to delete the detail row without deleting or changing the Master row, you violate the constraint and the operation fails.

    So #1 paints a picture of a world where you probably edited your DataSet by adding or removing some rows outside the jurisdiction of your TableAdapter. That created a situation that violated some constraint. The "ViewDetails" button might provide more information, but sometimes when this happens you're on your own, or have to put a breakpoint before the offending line then poke at the DataSet in the debugger to figure out what went wrong.

    This is part of the "magic", and is actually what a "good magic" DB tool promises. It's trying to stop you from doing things that will break the DB. It expects you to know the rules of your own database and try not to break them.

    I can't make a very detailed guess because I don't think post #1 gives me enough information. One of the frustrating things about database-oriented questions is it's often hard for experts to answer without getting a copy of your database schema and some sample data. One of the downsides of using "magic" is we also need either clear instructions for how to recreate your exact types, or, ideally, a .zip file of your project so we're sure we have exactly the same thing. This is true of both "good magic" and "bad magic": WinForms examples are a little harder to share because of the designer, too. It's a price we choose to pay when the magic saves us more time than this cost.

    What I get from JMC's #2 is:

    Your TableAdapter, if configured properly, should generate two magic methods. The FillByChangeID() method is the "most magic" one, that will try its darnedest to make sure no constraints are violated. There should also be a magic GetDataByChangeID() method that does the same thing as FillBy...() without bothering with all those constraints. It has a tiny difference: instead of returning a full DataSet, which is like "a tiny version of your entire database and all its magic", it returns a DataTable, which is why it doesn't bother with all of the magic.

    When I read between the lines, I think JMC's guess is you're doing something like "fetching a Detail row without its Master row". He gives you two suggestions.

    The first is "do whatever ritual makes the tool get all the information it needs". That means figuring out which bit of magic generates FillByChangeId() and changing its components and incantations such that it won't violate a constraint with its DataSet.

    The second is to replace FillByChangeId() with GetDataByChangeId() instead. That will give you a table with the "naked" row you want to update. As he said in #4, you might choose to display that DataTable in some UI somewhere. I'm not 100% sure, but I'd like to think there's an obvious way to get changes to that DataTable back to the TableAdapter.

    I feel like the thread got off the rails at that point, and I didn't help. There was some decent feedback along the way, but I think the trail picks up again in JMC's really big #25. The problem with big posts (and I'm familiar) is people don't tend to read them. But. What sticks out to me is he put a particular URL in a paragraph by itself.

    Like this.

    When an expert makes a URL stand out like that, it's important.

    Read that URL. I don't mean skim it. There's probably 20-30 pages worth of documentation if you follow every link, and this is the place where MS tries to explain how this feature works. It's eluded me for years. This kind of topic can be hard to find if you don't know what to look for. Read everything you can. Expect to spend 2-3 hours on it or you're not reading it enough. This is the spellbook for the magic, and if you don't read it then the tool's results will always be confusing. More importantly: ask questions about the articles you don't understand. Experts learn fast because we've learned a lot: I've already learned a ton in 20 minutes of skimming.

    And I found this, which is probably helpful. Let's see what situation it was designed to solve:
    For example, loading child records before loadingrelated parent records can violate a constraint and cause an error. As soon as you load a child record, the constraint checks for the related parent record and raises an error.
    If there were no mechanism to allow temporary constraint suspension, an error would be raised every time you tried to load a record into the child table.
    Whoaaaa. So this could be an answer. And it was there in #25, but it took us dang long enough to get there.

    Post #25 has some very smart things to say in it. I disagree with a handful of the statements made, but I don't disagree strongly enough to bother opening that can. Post #25 is very good and I wish I could give JMC some reputation points for it.

    (At this point I don't have anything new to add regarding the question, but I want to type something.)

    JMC, now that I've sat down and written out a thread summary, I find I didn't even notice the part of #25 that made me so mad last night when I read it. I overreacted to that exactly as your #27 predicted. It distracted me from realizing the rest of #25 had very good information, including some information I claimed you weren't sharing. I'm sorry I lashed out, it was completely wrong.

    I do think that right now, the way gwboolean maybe should've phrased himself is:

    I get that you want me to change the method I call, but it returns a DataTable instead of a DataSet. I'm still pretty new at this, and I'm not sure how much that means my code has to change. If my form has controls that bind to this DataTable, how do I make sure the edits I make get back to my database properly?
    I'm putting words in his mouth, but I think that's the actual question implied. It's not explicitly stated. We should all be a lot better at being explicit.

    I can't answer this question until I've spent a few hours playing with this tool on my own. "Rewrite it without the tool" was never a true answer to the question, that's was more of a "consider this next time for simple projects". Retrofitting it would be a much bigger task than answering that quoted version of the question.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  31. #31
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: Constraint Exception

    Personally, I went to sleep. I can understand JMC, since he's in a time zone several hours removed from the one I'm in, but some of y'all are rabid insomniacs.

    So, let me clarify my position, as well: TableAdapters are not bad. They can be confusing, and that's bad, but if you understand them well, then there is nothing wrong with them, but nor is there anything particularly right with them. If you understand ADO.NET, then the TableAdapter is just a tool that uses it. I don't see any particular advantage to using that tool over other tools, but it's just a tool.

    However, in THIS case, I think you'd be better off not using TableAdapters. The reason is that it IS a black box. You have gotten to a situation where the TableAdapter doesn't do what you want without effort, at which point you have to learn it to a greater level of understanding to accomplish what you want. Therefore, you aren't moving towards your goal, you're sidetracked into learning something new. At this point, you can try to learn "just enough" to solve your specific problem, while leaving TableAdapters as a black box, or you can learn ADO.NET (which TableAdapters are a part of, or are a layer built on top of, depending on your point of view). Frankly, having read your stuff for some time, I think you'd be happier learning ADO.NET. It's not difficult (I showed a reasonably functional example), and you strike me as somebody who would prefer to have that level of understanding. If you then went back to TableAdapters, you'd find them a bit weird, but you'd be able to look through the code generated by VS and see how it was built on top of what you already know.

    So, I'm not saying that TableAdapters are bad. I'm saying that I prefer not to use them, and that, based on what I've seen from you, I think you'd share that preference.
    My usual boring signature: Nothing

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width