Access query with parameters works in Access, not in C#

Question

I have a query that works in Access perfectly, but does not seem to work in C#. I am pretty sure it is related to the parameters I am adding because if I hard code dates surrounded by '#' symbols then it works fine. Does anyone know what could be the solution here? Thanks Ahead

string queryAccessNewHires =
  @"SELECT cdate([Date of Hire]), Count([SSN]) AS DateCount 
    FROM [FED] 
    WHERE 
        IIf(
          IsNull([Date of Hire]), False, 
             IIf([Date of Hire] <> 'DoesNotApply' 
              AND [Date of Hire] <> ""
              AND CDate([Date of Hire]) > CDate(PBP)
              AND CDate([Date of Hire]) < CDate(PBE), True, False 
             )
        )
    GROUP BY [Date of Hire] 
    ORDER BY [Date of Hire]";

OleDbCommand cmdNewHires = new OleDbCommand(queryAccessNewHires, conn);
cmdNewHires.Parameters.AddWithValue("PBP", tbTYB.Text);
cmdNewHires.Parameters.AddWithValue("PBE", tbTYE.Text);

EDIT:: Thank you for the help. Instead of receiving the "Syntax error in query expression" error, it seems to be understanding what I want when I change the code. However the new error I am receiving is: "The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects"

string queryAccessNewHires = @"SELECT cdate([Date of Hire]), Count([SSN]) AS DateCount 
                               FROM [FED] 
                               WHERE IIf(IsNull([Date of Hire]),False, 
                                     IIf([Date of Hire]<>'DoesNotApply' 
                                     AND [Date of Hire]<>"" 
                                     AND CDate([Date of Hire])>CDate(?) 
                                     AND CDate([Date of Hire])<CDate(?), True, False))
                               GROUP BY [Date of Hire] 
                               ORDER BY [Date of Hire]";

OleDbCommand cmdNewHires = new OleDbCommand(queryAccessNewHires, conn);
cmdNewHires.Parameters.Add(tbTYB.Text);
cmdNewHires.Parameters.Add(tbTYE.Text);

EDIT: Still no solution found. I am thinking that I may have possibly made this harder than it needs to be so I want to show you guys the original query that I want to run from C#. [Date of Hire] is a varchar and needs to be converted. I cannot change the design. This query returns "Invalid use of null." I do not understand why this query does not execute.

SELECT cdate([Date of Hire]), Count([SSN]) AS DateCount 
                                        FROM [FED] 
                                        WHERE (CDate([Date of Hire]) > #1/1/2013# 
                                        AND CDate([Date of Hire]) < #12/31/2013#)
                                        GROUP BY [Date of Hire] 
                                        ORDER BY [Date of Hire]

Answer 1

Since you using OleDb, looks like you need to use ? for your parameters. It doesn't support named parameters.

From OleDbCommand.Parameters property

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

AND CDate([Date of Hire]) > CDate(?)
AND CDate([Date of Hire]) < CDate(?), True, False 

As Remou said;

'Does not support named parameters' just means that it will only recognize a parameter by its position, not that you cannot use a name. You do not have to use ?, you just have to ensure that the order is correct.

Answer 2

You need to use ? as OleDBCommand.Parameters doesn't support named parameters.

OleDbCommand.Parameters Property

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

So your code would be like:

string queryAccessNewHires = @"SELECT cdate([Date of Hire]), Count([SSN]) AS DateCount 
    FROM [FED] 
    WHERE IIf(IsNull([Date of Hire]),False, IIf([Date of Hire]<>'DoesNotApply' AND [Date of Hire]<>"" AND CDate([Date of Hire])>CDate(?) AND CDate([Date of Hire])<CDate(?), True, False))
    GROUP BY [Date of Hire] 
    ORDER BY [Date of Hire]";

OleDbCommand cmdNewHires = new OleDbCommand(queryAccessNewHires, conn);
cmdNewHires.Parameters.Add(tbTYB.Text);
cmdNewHires.Parameters.Add(tbTYE.Text);