Query is too complex error -- dotnet odbc.net

dotnet odbc.net : Query is too complex error

Peter
2/29/2004 10:57:05 PM

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
-------------------
czupet@wsinc.com

Peter
3/1/2004 7:46:27 AM

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!

[quoted text, click to view]

timhuang NO[at]SPAM online.microsoft.com
3/1/2004 9:23:03 AM

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.

DotNetJunkies User
11/5/2004 5:32:06 AM

This helped me as well. Thanks!

---
Posted using Wimdows.net NntpNews Component -

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet odbc.net

home · blog · groups

Questions? Comments? Contact the dn