Query is too complex

  • Thread starter Thread starter Ben de Vette
  • Start date Start date
B

Ben de Vette

Hi,

I'm using the querybuilder when updating a (rather wide) record in a table
(Access).
However, I get a "Query is too complex" message.
It seems that for "optimistic locking " purposes, the WHERE Clause is very
complex

Any suggestions how to get this working?

Thanks in advance,
Ben
 
Depending on which JET version you're using ther are limits tio the number
of AND clauses you can have in your query. JET 4.0n allows for up to 40 AND
clauses in your WHERE clause, and I suspect this is what is happening. I
don't believe there's anyway around this, although looking at the Query
Builder output and possibly midfying it to keep it below 40 AND clauses
would be the forward IMHO.
 
Carsten,

I tried to limit the Where clause with all the AND's by writing a wrapper
around the GetUpdateCommand(). It modifies the CommandText so only the
Primary Key is used. (I Asume the primary Key to be the first in the list,
but am not sure this is always the case. But for trying out it's OK).

protected OleDbCommand GetUpdateCommand(OleDbCommandBuilder
commandBuilder)
{
// let the command builder create the Command
OleDbCommand updateCommand = commandBuilder.GetUpdateCommand();

// make a working copy of the commandText
string command = updateCommand.CommandText;

// Find the index of the first ' AND ' in the text. It's just
behind the first entry in the WHERE clause
int indexWhere = command.IndexOf(" AND ");

// get the needed part
command = command.Substring(0, indexWhere);

// change the text of CommandText with the new SQL statement. Add
one ')' to complete the SQL statement
updateCommand.CommandText = commandTillWhere + " ) ";

return updateCommand;
}


But this does not work. I still get the Query too Complex error while the
CommandText WHERE clause is restricted to the Primary key only now.

Ben
 
Hi everyone,

The following wrapper works, but I find it kinda ugly.
If I just change the CommandText of the OleDbCommand, it is simply rewritten
by .NET to the old value. So, I create a new OleDbCommand and copy all
needed information from the generated to the newly created.

Any suggestions to make this more beautifull are welcome

Greetz,
Ben
-----------------------------------
protected OleDbCommand GetUpdateCommand(OleDbCommandBuilder commandBuilder)

{

OleDbCommand updateCommand = commandBuilder.GetUpdateCommand();

// What is the commandtext

string command = updateCommand.CommandText;

// find the first AND in the WHERE clause

// assuming the Primary key is the parameter just befor the first ' AND '

int indexWhere = command.IndexOf(" AND ");

// We only need the first part of the commandstring

string commandTillWhere = command.Substring(0, indexWhere);

// Make a new OledBCommand

OleDbCommand newUpdateCommand = new OleDbCommand( commandTillWhere + " )
" );

// Copy needed properties from the old to the new connection

newUpdateCommand.Connection = updateCommand.Connection;

newUpdateCommand.Transaction = updateCommand.Transaction;

// Loop through all the parameters and make a copy

for (int i = 0 ; i < updateCommand.Parameters.Count ; i++)

{

OleDbParameter par = updateCommand.Parameters;

// For some rare case, we can nut use the method par.get_IsNullable(), so
alsways true

bool isNullable = true;

//Create the new parameter

OleDbParameter newPar = new OleDbParameter( par.ParameterName,
par.OleDbType, par.Size, par.Direction, isNullable, par.Precision,
par.Scale, par.SourceColumn, par.SourceVersion, par.Value );

// Add it to the updateCommand

newUpdateCommand.Parameters.Add(newPar);

}

// return the newly created UpdateCommand

return newUpdateCommand;

}
 
Back
Top