MS访问，命名参数和列名参数、MS
我有我执行上的Access数据库下面的查询。查询，在Access中运行时返回准确的结果。从code运行然而，当我回来的所有项目在数据库中，即使是那些会超出日期范围我寻找。
我在想，如果这个问题是因为参数名称相同的表中的列名，所以我改变了参数的名称 @StartDate 和 @EndDate 是 @FromDate 和 @ToDate 这解决了这一问题，如果参数名称是不同的，我得到正确的结果集返回。这涉及到我，因为在我工作的这个模式的项目是重复的所有的地方。但是我使用一个名为 @Id 参数由编号（在数据库表中的列名），这对更新记录是工作的罚款。这是一个奇怪的边缘情况？任何人都可以揭示这种行为任何光线。
道歉了大量的code样品，但在这种情况下，我认为是必要的整个方法。
公众覆盖AcademicCycleTransportCollection FetchForDateRange（日期时间的startDate，日期时间结束日期）{
常量字符串查询=
参数 N+
@StartDate日期， N+
@EndDate日期 N+
N+
选择 N+
[AcademicCycle]。[ID] AS [ID]， N+
[AcademicCycle]。[名称] [名称]， N+
[AcademicCycle]。[AcademicCycleCategoryId] AS [AcademicCycleCategoryId]， N+
[AcademicCycle]。[ParentAcademicCycleId] AS [ParentAcademicCycleId]， N+
[AcademicCycle]。[起始日期] AS [起始日期]， N+
[AcademicCycle]。[结束日期] AS [结束日期]， N+
[AcademicCycle]。[IsPerpetual] AS [IsPerpetual]， N+
[AcademicCycle]。[IsLocked] AS [IsLocked] N+
由 N+
AcademicCycle N+
WHERE N+
（起始日期＆LT; = @EndDate日期和结束日期＆GT; = @StartDate）或者正 n+
IsPerpetual＆其中;大于0;
AcademicCycleTransportCollection transportCollection =新AcademicCycleTransportCollection（）;
OleDbCommand的_fetchForDateRangeCommand = NULL;
如果（_fetchForDateRangeCommand == NULL）{
OleDbConnection的连接= _parentDataConnection.Connection;
_fetchForDateRangeCommand =新的OleDbCommand（查询，连接）;
_fetchForDateRangeCommand.Parameters.Add（@起始日期，OleDbType.Date）;
_fetchForDateRangeCommand.Parameters.Add（@结束日期，OleDbType.Date）;
}
_fetchForDateRangeCommand.Transaction = _parentDataConnection.Transaction;
_fetchForDateRangeCommand.Parameters [@起始日期]值=的startDate。
_fetchForDateRangeCommand.Parameters [@结束日期]值=结束日期。
使用（OleDbDataReader dbReader = _fetchForDateRangeCommand.ExecuteReader（））{
NullableDataReader读卡器=新NullableDataReader（dbReader）;
而（reader.Read（））{
AcademicCycleTransport运输= FillTransport（读卡器）;
transportCollection.Add（运输）;
}
如果（！reader.IsClosed）{
reader.Close（）;
}
}
返回transportCollection;
}
解决方案
你这样做的方式，OLEDB是使用位置参数的插入，让您在SQL中第一个参数，@EndDate被替换为第一个参数传递， '@开始日期'。该参数的名称使用位置插入时，被完全忽略。
不过，这是一个鲜为人知的事实是OleDb的实际上并接受命名参数。你刚刚宣布在SQL中的参数也是如此。
请参阅：的low-bandwidth.blogspot.com.au/2013/12/positional-msaccess-oledb-parameters.html
如果您不声明SQL中的参数，OLEDB使用纯粹的位置参数的插入，它并不重要，如果该参数的名称相匹配的SQL，或者如果参数在SQL中使用两次 - 它只是通过与盲目，以便从开始替换SQL发现的任何参数的结束，那些过去了。
不过，如果你正确地声明参数，你会得到允许SQL语句中被重复多次命名的参数和参数的利益。
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;
}
解决方案
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.
