none
Query method returns exception RRS feed

  • Question

  • I am using the following method to filter for a single record to display on a form.

    If (Me.TblDocMasterTableAdapter.FillByChangeID(_MasterBase5_0DataSet.tblDocMaster, glbintChangeID)) = Nothing Then

    The table has three records in it.  When the method is used on either the first or last record the requested record is returned.  However, when the method is used for the second record I get the following exception:

    The table is related to another table with a foreign key, but I fail to understand how anything is being violated...... except me.


    gwboolean

    Saturday, November 4, 2017 4:56 PM

Answers

  • Sorry, been out.

    This is yet another reason I stay away from TableAdapter method for working with data. Any ways you can set EnforceConstraints to False but that is really not wise in general.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by gwboolean Tuesday, November 21, 2017 7:03 PM
    Saturday, November 4, 2017 10:39 PM
    Moderator

All replies

  • Hello,

    See post 5 in the link below, ignore the last paragraph.

    http://www.vbforums.com/showthread.php?737541-RESOLVED-Failed-to-enable-constraints-One-or-more-rows-contain-values-violating-non-null


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, November 4, 2017 5:13 PM
    Moderator
  • I think I get that.  It would appear that I can set the constraints in my relationship such that this exception will not be thrown.  I think.  Is that correct?

    However, I still do not even see why this exception should be thrown in the first place.  Why is the query successful  records one and three, but not record two?


    gwboolean

    Saturday, November 4, 2017 6:39 PM
  • Sorry, been out.

    This is yet another reason I stay away from TableAdapter method for working with data. Any ways you can set EnforceConstraints to False but that is really not wise in general.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by gwboolean Tuesday, November 21, 2017 7:03 PM
    Saturday, November 4, 2017 10:39 PM
    Moderator
  • It has been suggested to me that if I created an instance of the table and did not use a table adapter that this problem might go away.  

    OK, I can do that, but I never did get it on how to query except from a tableadapter.

    Can you tell me how that is done again?  Or is this one of those things that is going to require me to re-learn much of what I know?


    gwboolean

    Sunday, November 5, 2017 12:41 AM
  • First off, you would need to learn all aspects when moving away from a TableAdapter to work with data. Think of it for the most part as starting from scratch.  

    There is no shortcuts to moving away from TableAdapters but once you have it you will immediately see in the long run TableAdapters constrain you while hand writing code opens millions of doors where your limitations are from not learning something.

    For instance, I was just trolling the web and found a cool idea that required me to get my hands dirty in SQL and then ended up one hour later with a published MSDN code sample which helps others and gave me new knowledge. The key point here is you never stop learning.

    From the code sample (it's not here to help you in your code but instead shows how looking for the unknown can help).

    Conceptually speaking a developer loads data from a database into a DataTable then adds an un-bound column for a check box or adds a new DataColumn for the checkbox column while the SQL below does that for us.

    DECLARE @ContactTypeIdentifier INT = 7
    SELECT 
            IIF(Customers.ContactTypeIdentifier = @ContactTypeIdentifier, CONVERT(BIT, 1), CONVERT(BIT, 0)) AS Process ,
            Customers.CustomerIdentifier ,
            Customers.CompanyName ,
            Customers.ContactName ,
            ContactType.ContactTitle
    FROM    Customers
            INNER JOIN ContactType ON Customers.ContactTypeIdentifier = ContactType.ContactTypeIdentifier
    WHERE dbo.Customers.ContactTypeIdentifier = @ContactTypeIdentifier
    ORDER BY ContactTitle
    

    Try and do this with a TableAdapter which is possible to an extent but difficult to implement overall. The code to execute the above query uses a connection and a command, that is it.

    Back to your question to move away from TableAdapters, you need to study good samples were sometimes the samples are for SQL-Server but you are using MS-Access, this doesn't mean you can learn and adapt to MS-Access, you can. Sadly most of the best code samples (including mine) are in C# so it's good to have a handy dandy language converter to assist there too.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, November 5, 2017 1:34 AM
    Moderator
  • Karen,

    Actually, I intend to make a move from Access to SQL pretty soon, so I will be able to really take a serious look at this and contemplate making a change.  Koinkidinkilly, just this morning I figured out why this error actually occurs in this one instance.

    The problem is in the SELECT of the Query method used.  Below is the query as it is right now.  Previously the query included in the SELECT, lnkEmployeeInformation.SiTechID.  I would run the query builder and it would pull the requisite records just fine.  However, when I called the query method in the form code it would throw the exception with the message above.

    I went back and looked at the query method in the query builder and noticed that there was an expr 1 where the SELECT for the above noted column was.  Taking that column out of the SELECT was all that was needed to resolve the issue.  I am still not entirely sure why this occurs, but I am getting closer to understanding it.

    SELECT        tblEmployee.intSiTechID, tblEmployee.strFullName, tblEmployee.blnActive, tblEmployee.blnInactive, lnkEmployeeInformation.strJobTitle, lnkEmployeeInformation.strDepartment
    FROM            (lnkEmployeeInformation INNER JOIN
                             tblEmployee ON tblEmployee.intSiTechID = lnkEmployeeInformation.intSiTechID)
    WHERE        (tblEmployee.blnActive = TRUE)

    gwboolean


    By the way, Karen.  You had mentioned that you live in Salem.  You must work at home, because I can think of no place that you would work that is commutable with Salem.  By the way, we considered moving there when we ended up in The Couv.
    • Edited by gwboolean Tuesday, November 21, 2017 6:59 PM
    Tuesday, November 21, 2017 6:58 PM