MS Access, Named parameters and Column Names

Question

I have the following query which I am executing on an Access database. The query, when run in Access returns accurate results. However when run from the code I get back all of the items in the database, even those which fall outside the date range I am searching for.

I was wondering if the issue was because the parameter names are the same as the column names in the table, so I changed the parameter names @StartDate and @EndDate to be @FromDate and @ToDate and this fixed the problem, if the parameter names are different I get the right result set back. This concerns me because in the project that I am working on this pattern is duplicated all over the place. However I am using a parameter named @Id to update the records by Id (column name in db table) and this is working fine. Is this a weird edge case? Can anyone shed any light on this behaviour.

Apologies for the massive code sample, but in this case I think the whole method is needed.

  public override AcademicCycleTransportCollection FetchForDateRange(DateTime startDate, DateTime endDate) {
     const String query =
        "PARAMETERS \n" +
        "   @StartDate DATE, \n" +
        "   @EndDate DATE \n" +
        "   ; \n" +
        "SELECT \n" +
        "      [AcademicCycle].[Id] AS [Id], \n "  +
        "      [AcademicCycle].[Name] AS [Name], \n "  +
        "      [AcademicCycle].[AcademicCycleCategoryId] AS [AcademicCycleCategoryId], \n "  +
        "      [AcademicCycle].[ParentAcademicCycleId] AS [ParentAcademicCycleId], \n "  +
        "      [AcademicCycle].[StartDate] AS [StartDate], \n "  +
        "      [AcademicCycle].[EndDate] AS [EndDate], \n "  +
        "      [AcademicCycle].[IsPerpetual] AS [IsPerpetual], \n "  +
        "      [AcademicCycle].[IsLocked] AS [IsLocked] \n " +
        "FROM \n" +
        "  AcademicCycle \n" +
        "WHERE \n" +
        "  (StartDate <= @EndDate AND EndDate >= @StartDate) OR \n" +
        "  IsPerpetual <> 0";

     AcademicCycleTransportCollection transportCollection = new AcademicCycleTransportCollection();

     OleDbCommand _fetchForDateRangeCommand = null;

     if (_fetchForDateRangeCommand == null) {
        OleDbConnection connection = _parentDataConnection.Connection;
        _fetchForDateRangeCommand = new OleDbCommand(query, connection);
        _fetchForDateRangeCommand.Parameters.Add("@StartDate", OleDbType.Date);
        _fetchForDateRangeCommand.Parameters.Add("@EndDate", OleDbType.Date);
     }

     _fetchForDateRangeCommand.Transaction = _parentDataConnection.Transaction;

     _fetchForDateRangeCommand.Parameters["@StartDate"].Value = startDate;
     _fetchForDateRangeCommand.Parameters["@EndDate"].Value = endDate;

     using (OleDbDataReader dbReader = _fetchForDateRangeCommand.ExecuteReader()) {
        NullableDataReader reader = new NullableDataReader(dbReader);

        while (reader.Read()) {
           AcademicCycleTransport transport = FillTransport(reader);
           transportCollection.Add(transport);
        }
        if (!reader.IsClosed) {
           reader.Close();
        }
     }

     return transportCollection;
  }

Answer 1

The way you have done it, OleDb is using positional parameter insertion, so your first parameter in SQL, '@EndDate' is being substituted with the first parameter passed, '@StartDate'. The names of the parameters are completely ignored when using positional insertion.

However, it's a little-known fact that OleDb actually DOES accept named parameters. You've just got to declare the parameters in SQL as well.

See: low-bandwidth.blogspot.com.au/2013/12/positional-msaccess-oledb-parameters.html

If you DON'T declare the parameters in SQL, OleDb uses purely positional parameter insertion, and it doesn't matter if the names of the parameters match the SQL, or if parameters are used twice in the SQL - it will just go through and blindly replace any found parameters in the SQL in order from start to end, with those passed.

However if you DO declare the parameters correctly, you get the benefit of named parameters and parameters allowed to be repeated multiple times within the SQL statement.

Answer 2

try to edit the query string directly with the desired parameters. Simple example (output query string):

"SELECT t001_clients.cli_id as id, t001_clients.cli_name WHERE (id = 1);"

Isn't the pretiest way but would work. Be care about the type characters on parameters ("cli_name = 'John Smith'" or "cli_birthday = #12/27/1980#")

Also, why did not you use linq queryes? Should be easier...