.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
Query is too complex error
Messages   Related Types
This message was discovered on microsoft.public.dotnet.framework.odbcnet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
Post a new message to this list...

Peter
I am updating Access database using System.Data.Odbc

I am using the following code:
////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////
foreach(DataColumn cl in dsNew.Tables[0].Columns)
{
dataRowDest[cl.ColumnName] = dataRowOrigin[cl.ColumnName];
}

adapter.Update(ds, tableName);
////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////

It works fine most of the time, but once in a while I get the following
error
System.Data.Odbc.OdbcException: ERROR [HY000] [Microsoft][ODBC Microsoft
Access Driver] Query is too complex.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)

How can I determine what column is causing the problem, and how can I fix
the data?

Peter

--
Thanks
-------------------
Click here to reveal e-mail address
Reply to this message...
 
    
Tian Min Huang (VIP)
Hello,

Thanks for your post. As I understand, the problem you are facing is it
reports "Query is too complex" error when updating MDB. Please correct me
if there is any misunderstanding. Now I'd like to share the following
information with you:

1. Generally speaking, the error appears when the table to be updated has
more than 99 fields. This behavior occurs because the default behavior of
ADO is to use each field in the recordset to determine the record to be
updated on the server. That is, when the update is attempted, a SQL Update
query is sent to the server. Part of this query is a WHERE clause that is
used to identify the record to be updated. An AND clause appears within the
WHERE clause for each field to be used in that identification. Updating a
recordset with more than 40 fields involves a WHERE clause with more than
40 ANDs.

The Jet engine allows only 40 AND clauses within a SQL WHERE or HAVING
clause. Jet 3.51 increased this limit to 50 AND clauses, and Jet 4.0
increased the limit to 99 AND clauses.

In order to avoid more than 99 fields are involved while updating a record,
I suggest you to add a primary key to the underlying table, which ensures
that a unique key is used to identify the record to be updated, rather than
the default behavior of using every field.

For your reference, here is a related article on this kind of issue:

HOWTO: Update More Than 40 fields in an Access (Jet) Database

http://support.microsoft.com/?id=192716

2. If it's not the case, I suggest that you can output the content of each
field before calling adapter.Update(). When it fails, you can check
fields' data and compare them with those work properly.

Hope this helps.

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! -- www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Reply to this message...
 
    
Peter
I did have a primary key consisting of two data fields, but was still
getting the error.
I added AutoNumber field and made it the primary key and that fixed the
problem, but I could not use the AutoNumber field for my application. I
reverted back to using two fields as the primary key and everything worked.

Thanks for your help!

"Tian Min Huang" <Click here to reveal e-mail address> wrote in message
news:04cSV72$Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
 
System.Data.Common.DataTableMapping
System.Data.Common.DbDataAdapter
System.Data.DataColumn
System.Data.DataRow
System.Data.DataSet
System.Data.Odbc.OdbcException




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification