asked on

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.

myoungbl

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

LeeLiam

ASKER

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.

ASKER CERTIFIED SOLUTION

myoungbl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LeeLiam

ASKER

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.

LeeLiam

ASKER

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.