MS Access, Named Parameters and Column Names

Question

I have the following query that I am executing in an Access database. A query at startup in Access returns accurate results. However, when I run from the code, I return all the items in the database, even those that fall outside the date range I'm looking for.

I was wondering if the problem was that the parameter names match the column names in the table, so I changed the parameter names @StartDateand @EndDateon @FromDateand @ToDateand it fixed the problem, if the parameter names are different, I get the right result. This concerns me, because in the project that I am working on this template is duplicated everywhere. However, I use a parameter with a name @Idto update records Id(column name in the db table), and this works fine. Is this a weird edge case? Can anyone shed light on this behavior.

Sorry 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

, OleDb , SQL, '@EndDate' '@StartDate'. .

, OleDb . SQL.

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

SQL, OleDb , , SQL SQL - SQL , .

, , , SQL.

Answer 2

. ( ):

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

, . ( "cli_name =" John Smith "" cli_birthday = # 12/27/1980 # ")

, linq? ...