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?
 
myoungblConnect With a Mentor Commented:
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.