是否可以在Where子句之外的其他地方使用OleDBCommand参数?

本文关键字：其他 方使用 OleDBCommand 参数 Where 子句 是否 | 更新日期: 2023-09-27 18:13:01

我有这样的代码:

using (OleDbCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = string.Format(
        @"SELECT TOP {0} t_accounts.account_no as AccountID, IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
        FROM t_accounts 
        INNER JOIN td_department_accounts ON (t_accounts.account_no = td_department_accounts.account_no) 
        WHERE (AccountID >= @firstId) AND type = 'DE'", CountToFetch);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@firstId", FirstId);

…但我想知道我是否也可以为顶部计数使用参数，如:

using (OleDbCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = 
        @"SELECT TOP @count t_accounts.account_no as AccountID, IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
        FROM t_accounts 
        INNER JOIN td_department_accounts ON (t_accounts.account_no = td_department_accounts.account_no) 
        WHERE (AccountID >= @firstId) AND type = 'DE'";
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@firstId", FirstId);
    cmd.Parameters.AddWithValue("@count", CountToFetch);

…还是数据库参数仅限于WHERE子句?

更新

使用下面的代码:

  cmd.CommandText = 
        @"SELECT TOP @countToFetch t_accounts.account_no as AccountID, 
    IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
        FROM t_accounts 
        INNER JOIN td_department_accounts ON (t_accounts.account_no = 
    td_department_accounts.account_no) 
        WHERE (AccountID >= @firstId) AND type = 'DE'";
    . . .
  cmd.Parameters.AddWithValue("@firstId", FirstId);
  cmd.Parameters.AddWithValue("@countToFetch", CountToFetch);

…我得到，" SELECT语句包含一个保留词或参数名称，拼写错误或缺失，或标点符号不正确。"

所以我回到了:

   cmd.CommandText = string.Format(
        @"SELECT TOP {0} t_accounts.account_no as AccountID, 
    IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
        FROM t_accounts 
        INNER JOIN td_department_accounts ON (t_accounts.account_no = 
    td_department_accounts.account_no) 
        WHERE (AccountID >= @firstId) AND type = 'DE'", CountToFetch);
    . . .
    cmd.Parameters.AddWithValue("@firstId", FirstId);

更新2

: 

SELECT TOP (@countToFetch) t_accounts.account_no as AccountID, IIF(ISNULL
(t_accounts.name),'[blank]',t_accounts.name) AS Name 
FROM t_accounts 
INNER JOIN td_department_accounts ON (t_accounts.account_no = td_department_accounts.account_no) 
WHERE (AccountID >= @firstId) AND type = 'DE'"

…in Access告诉我"SELECT语句包含一个保留词或参数名称，拼写错误或缺失，或者标点错误。"

: :在进行REST调用时，邮差告诉我的是完全相同的事情，该调用以生成该查询结束。

3

更新

我也尝试了它在更新2，但用":"而不是"@"，这样:

SELECT TOP (?) t_accounts.account_no as AccountID, IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
FROM t_accounts 
INNER JOIN td_department_accounts ON (t_accounts.account_no = td_department_accounts.account_no) 
WHERE (AccountID >= ?) AND type = 'DE'"

是否可以在Where子句之外的其他地方使用OleDBCommand参数?

我偶然看到这篇文章，似乎表明这个确实工作，至少与T-SQL。鉴于此，我倾向于假设，它可以与Access一起工作。就像上面说的，记住要包括括号。

无论如何，参数只能在WHERE子句中是绝对不正确的。您可以在SELECT甚至ORDER BY中使用它们。

相关文章：