Query too complex when updating existing record

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:
        TblPATBindingSource.EndEdit()
        TblPATTableAdapter.Update(Me.DSpatd.tblPAT)
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("vs.data.TableAdapter")>  _
        Public Overloads Overridable Function Update(ByVal dataTable As DSpatd.tblPATDataTable) As Integer
            Return Me.Adapter.Update(dataTable)
        End Function

How do I avoid this?

Thanks.
LeeLiamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

myoungblCommented:
Have you inspected the query statement being used by the update function? You may have to modify it by hand.
0
LeeLiamAuthor Commented:
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.
0
myoungblCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeeLiamAuthor Commented:
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:

TblPATBindingSource.AddNew()

and then add the many statements like:

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

Any ideas?

Thanks.



0
LeeLiamAuthor Commented:
myoungbl,

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.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.