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]

By not working do you mean it throws an error or it returns no data? — Yuriy Galanter, Apr 09 '14 at 14:25
I strongly believe that you cannot use the VBA stuff that works in Access inside your own application. I.e. `IIf`, `CDate`, `IsNull` etc. — Uwe Keim, Apr 09 '14 at 14:28
@UweKeim IsNull, CDate and IIf are not 'VBA stuff' there are Jet/ACE stuff and work fine outside of MS Access. — Fionnuala, Apr 09 '14 at 15:07
Thanks, @Remou Are you sure? IIRC I tried code like above in the past without success. Great to hear though! — Uwe Keim, Apr 09 '14 at 15:07
Have you looked at exactly what you have in PBP & PBE? This may help http://stackoverflow.com/questions/16621695/c-sharp-oledbparameter-with-access-datetime-query Do not forget that you are using CDate, so the query expects a string. Is that what you are supplying? — Fionnuala, Apr 09 '14 at 15:08
@UweKeim I have just tested with C# & ACE, although seeing I have been using some of this stuff in classic ASP, I really did not need to :) — Fionnuala, Apr 09 '14 at 15:10
@Remou I gave that solution a try, but I am still getting syntax errors with the query if I try and parameterize any other way than just using parameters.add — Leyth G, Apr 09 '14 at 15:20
You have `AND [Date of Hire]<>""`, that should be `AND [Date of Hire]<>''`, however, it is quite unlikely that you will have an empty string in MS Access. — Fionnuala, Apr 09 '14 at 15:23
Even when I hardcode the date in. It works in Access, but fails in C# saying data type mismatch in criteria expression. It does not make any sense why an unaltered query fails in C# and not in Access to me. — Leyth G, Apr 09 '14 at 15:57
BTW There is no need for GROUP BY and ORDER BY when both are the same field / column. GROUP BY will order by without any further fussing. — Fionnuala, Apr 09 '14 at 15:58
This is something else. If I do not parameterize the query and leave it vulnerable to SQL injection it still does not run, but if I debug it and copy and paste that same query to access it runs just fine. — Leyth G, Apr 09 '14 at 17:58

score 1 · Answer 1 · edited May 23 '17 at 12:28

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.

edited May 23 '17 at 12:28

Community

1
1

answered Apr 09 '14 at 14:26

Soner Gönül

97,193
102
206
364

Ah I see, I did not know that. Thank you. However, I am now receiving a different error. "The OleDbParameter Collection only accepts non-null type objects, not string objects" – Leyth G Apr 09 '14 at 14:36
'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. That quote is not quite correct. – Fionnuala Apr 09 '14 at 14:46
@Remou You're right. Update my answer with your quote. – Soner Gönül Apr 09 '14 at 14:49

score 1 · Answer 2 · answered Apr 09 '14 at 14:27

1

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);

answered Apr 09 '14 at 14:27

Habib

219,104
29
407
436

Thank you as well. However, I am now getting a new error. "The OleDbParameter Collection only accepts non-null type objects, not string objects" – Leyth G Apr 09 '14 at 14:45
I have commented on named parameters above. – Fionnuala Apr 09 '14 at 14:47
@Remou, so any character(s) could be used instead of `?`, but only the order matters ? That is a very good point and I wans't aware of it. Certainly MSDN docs are misleading. – Habib Apr 09 '14 at 14:49
@Remou, but how does it differentiate between a parameter and other text in query ? – Habib Apr 09 '14 at 14:51
? and @ symbols are the only ones that seem to trigger a parameter from my quick testing. – Leyth G Apr 09 '14 at 14:56
If the name is unknown, that is, it does not belong to a field / column in the sql, it is a parameter. Alternatively, you can state that it is a parameter at the beginning of the sql. From this, you can see that it is sensible to use a name that could not be a field. – Fionnuala Apr 09 '14 at 15:15

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

2 Answers2