MS Access query code in C#

Question

I'm trying to run a query on an Access DB, I'm used to SQL queries but this doesn't seem to be working the same way. Here's my query:

OleDbCommand command = new OleDbCommand();
command.Connection = connect;
command.CommandText = "SELECT RecordID, TimeStamp, EmployeeName, AreaDescription       FROM LoginRecords r, Employees e, Areas a WHERE((e.EmployeeID = r.EmployeeID) && (a.AreaID =r.AreaID) && (TimeStamp > startDate AND < endDate)) ORDER BY TimeStamp;"

I can't seem to get this to run but technically from a SQL standpoint this should be a good query. The tables are LoginRecords, Employees, Areas. I can load the tables if that would be helpful. I appreciate any feedback as to why this won't work in Access. And startDate and endDate are variables from user input boxes.

"this doesn't seem to be working the same way" - "can't seem to get this to run" - Are there any errors? What is your indication that it isn't working? — David
– David, Commented Sep 4, 2012 at 0:34
It says missing expression on the command.CommandText when command.Execute() is run. — John Verber
– John Verber, Commented Sep 4, 2012 at 0:51
@Verber: It's just a guess, but this doesn't look right: (TimeStamp > startDate AND < endDate). Unless MS Access has weird SQL syntax parsing, that doesn't look very structured. The overall mix of AND and && probably isn't good either. Maybe stick with one (AND) and be more explicit in that parenthetical statement, something like: (TimeStamp > startDate AND TimeStamp < endDate). — David
– David, Commented Sep 4, 2012 at 0:56

John Woo · Accepted Answer · 2012-09-04 02:16:13Z

1

Try this one,

This is SQL-92

SELECT      RecordID, 
            TimeStamp, 
            EmployeeName, 
            AreaDescription       
FROM        LoginRecords r
                INNER JOIN Employees e
                    ON e.EmployeeID = r.EmployeeID
                INNER JOIN Areas a 
                    ON a.AreaID = r.AreaID
WHERE   TimeStamp > startDate AND
        TimeStamp < endDate
ORDER BY TimeStamp;

Use SQL-92 format rather SQL-89 format because SQL-89 (aside from old style) is prone to going CROSS JOIN if not handled correctly.

and this is SQL-89

SELECT      RecordID, 
            TimeStamp, 
            EmployeeName, 
            AreaDescription       
FROM        LoginRecords r, Employees e, Areas a
WHERE   (e.EmployeeID = r.EmployeeID) AND
        (a.AreaID = r.AreaID) AND
        (TimeStamp > @startDate AND
         TimeStamp < @endDate)
ORDER BY TimeStamp;

MSACCESS: INNER JOIN, OUTER JOIN (LEFT and RIGHT)

edited Sep 4, 2012 at 2:16

answered Sep 4, 2012 at 0:35

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

John Verber Over a year ago

I'm getting the error: Syntax error (missing operator) in query expression 'e.EmployeeID = r.EmployeeID INNER JOIN Areas a ON a.AreaID = r.AreaI'. I'm not sure if Access has a different syntax...I can't find anything about coding a query on MSDN. They just give a few super simple examples but nothing with joins or anything like that.

John Woo Over a year ago

@Verber what version of ms access you are using?

John Woo Over a year ago

@Verber seeing you comment above, can you show us some code of your ado?

John Woo Over a year ago

@Verber you code is fine even though it needs proper object handling and error trapping, the only problem is the error on your syntax. It seems fine with me. seeing your connection string, you are using MSAccess 2010 right?

John Verber Over a year ago

Yes 2010. Now with you're updated query I'm seeing: No value given for one or more required parameters-that's on the OleDbDataReader reader = command.ExecuteReader();

|

Collectives™ on Stack Overflow

MS Access query code in C#

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related