?
Solved

Query too complex when updating existing record

Posted on 2008-11-07
5
Medium Priority
?
474 Views
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:
        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.
0
Comment
Question by:LeeLiam
  • 3
  • 2
5 Comments
 
LVL 1

Expert Comment

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

Author Comment

by:LeeLiam
ID: 22908531
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
 
LVL 1

Accepted Solution

by:
myoungbl earned 2000 total points
ID: 22908671
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
 

Author Comment

by:LeeLiam
ID: 22909255
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
 

Author Comment

by:LeeLiam
ID: 22916841
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

850 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