1

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]
Improve this question
13
  • 2
    By not working do you mean it throws an error or it returns no data? Commented Apr 9, 2014 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. Commented Apr 9, 2014 at 14:28
  • 1
    @UweKeim IsNull, CDate and IIf are not 'VBA stuff' there are Jet/ACE stuff and work fine outside of MS Access. Commented Apr 9, 2014 at 15:07
  • Thanks, @Remou Are you sure? IIRC I tried code like above in the past without success. Great to hear though! Commented Apr 9, 2014 at 15:07
  • 1
    @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 :) Commented Apr 9, 2014 at 15:10

2 Answers 2

Reset to default
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);
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you as well. However, I am now getting a new error. "The OleDbParameter Collection only accepts non-null type objects, not string objects"
I have commented on named parameters above.
@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.
@Remou, but how does it differentiate between a parameter and other text in query ?
? and @ symbols are the only ones that seem to trigger a parameter from my quick testing.
|
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.

Improve this answer

3 Comments

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"
'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.
@Remou You're right. Update my answer with your quote.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.