I spent a few months late last year re-writing one of my products, Timesheets Lite, from the ground up in C# and WinForms. The old version was written in Visual Basic 6 and was working just fine but in the interests of “future-proofing” the product and learning a modern language I decided to re-write it.

The process was fairly smooth apart from a large number of inexplicable (and seemingly pointless) changes to the way that OleDB connections work. This has thrown up a lot of problems that the old VB6 version never experienced and it’s proving to be a frustrating experience getting to the bottom of them. One that was thrown up yesterday was one user who was getting the following error message:

Product: Timesheets Lite NET
Version: 3.3.4.0
Error:Query is too complex.
Source:Microsoft JET Database Engine
Raised by:Void ExecuteCommandTextErrorHandling(System.Data.OleDb.OleDbHResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteScalar()
at timesheetslite.appClass.iExecuteScalarQuery(String sSQL, DateTime[] datParameterValues)

The error was being thrown in a method that checked for employee timesheet activity across a time period and for a (potentially large) number of projects. The large number of projects was the problem as It turns out that there’s an upper limit on the number of AND’s and OR’s that can be used as criteria in an OleDb select statement. Supposedly it’s something around 40. So, I’ve had to re-write the routine to break up the select statements into blocks of 20 projects. It works fine now but you have no idea how annoying it is to see new error messages from a modern programming language when an archaic language like VB6 had no problems with the same thing. Anyway, for those who are interested here’s the code.

            string sSql = string.Empty;
            string sProjectCriteria = string.Empty;
            Int32 iProjectCount = 0;
            DateTime[] parameters = new DateTime[2];
            Int32 iRecords = 0;
            Int32 iCriteriaCount = 0;
            Int32 iCriteriaLimit = 20;

            parameters[0] = this.getStartOfDay(pStart);
            parameters[1] = this.getEndOfDay(pEnd);

            if (projects.Count > 0)
            {
                foreach (Project project in projects)
                {
                    if (iCriteriaCount == 0)
                    {
                        sProjectCriteria = " AND (";
                    }

                    sProjectCriteria += "lngProjectID=" + project.ID.ToString();

                    iProjectCount++;
                    iCriteriaCount++;

                    if (iProjectCount != projects.Count && iCriteriaCount<iCriteriaLimit)
                        sProjectCriteria += " OR ";

                    if (iCriteriaCount == iCriteriaLimit) //break up query into blocks to stop the query too complex error
                    {
                        iCriteriaCount = 0;
                        sProjectCriteria += ") ";
                        sSql = "SELECT count(lngID) as time_records from tblTimes where datDate>=@Parm0 and datDate<=@Parm1 and lngEmployeeID=" + this.ID.ToString() + sProjectCriteria;

                        iRecords = app.iExecuteScalarQuery(sSql, parameters);

                        if (iRecords > 0)
                            return true;

                    }
                }

                sProjectCriteria += ") ";
            }

            return false;