I have this code:
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);
...but wonder if I could use a param for the top count too, like:
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);
...or are database params limited to the WHERE clause only?
UPDATE
With this code:
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);
...I got, "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."
So I reverted back to:
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);
UPDATE 2
This:
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 tells me "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."
NOTE: Postman tells me the exact same thing when making the REST call that ends up with the making of that query.
UPDATE 3
I also tried it as in Update 2 but with ":" instead of "@" and this way:
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'"