Query too complex when updating existing record

Posted on 2008-11-07
Last Modified: 2012-06-21
I have a windows form with many fields. The fields are bound through a dataset, dataadapter and bindingsource. When I select a record and open this form everything is populated properly.

If I add a new record, I assign initial values to many of the fields using statements like:
            TblPATBindingSource.Current("PPatientNo") = CStr(mPPatNum)
            TblPATBindingSource.Current("AcctNumber1") = CStr(mAcctNum)
            TblPATBindingSource.Current("PDateAdded") = CStr(Today)
            TblPATBindingSource.Current("PDateModified") = CStr(Today)
            TblPATBindingSource.Current("PFeeSched") = "1"
Then, I can fill in fields and click an update button with code:
and everything gets added properly.

However, if I select an existing record, make changes and click the update button, I get an OleDbException "Query too complex" error in the designer :
        <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _
         Global.System.ComponentModel.Design.HelpKeywordAttribute("")>  _
        Public Overloads Overridable Function Update(ByVal dataTable As DSpatd.tblPATDataTable) As Integer
            Return Me.Adapter.Update(dataTable)
        End Function

How do I avoid this?

Question by:LeeLiam
    LVL 1

    Expert Comment

    Have you inspected the query statement being used by the update function? You may have to modify it by hand.

    Author Comment

    Sorry, but I'm very new to .NET. I do not know where to find the query statement.

    I created the dataset using the Data Source Cofiguration Wizard, if that helps.

    I then used the Table Adapter Configuration Wizard to create the query. It is essentially:
    SELECT * FROM tblPAT WHERE (PPatientNo = ?)

    In other words, it gets every field in the record.
    LVL 1

    Accepted Solution

    Go into your dataset and then right click on the tableadapter.  Select Properties. There should be an UpdateCommand property. Expand it and you will see the command text. It will contain the SQL statement used for the update.

    Author Comment

    Okay, I did that. It's just an Update like:

    UPDATE `tblPAT` SET `PTitle` = ?, `PFirstName` = ?, `PMiddleName` = ?, `PLastName` = ?,

    only it lists every field (about 100 of them). I wouldn't know what to do to modify it.

    What I really don't understand is why it updates when I add a new record, but give the error when I try to save an existing record. The only real difference is for new records I use:


    and then add the many statements like:

    TblPATBindingSource.Current("PPatientNo") = CStr(mPPatNum)

    Any ideas?



    Author Comment


    You were correct. After I dug a little deeper, I found the WHERE clause in the UpdateCommand had a lot of unnecessary stuff. I removed all but

    WHERE ID = ?

    and it worked great.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Article by: jpaulino
    XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now