Patrick3388
asked on
SQL Statement for VB2010
Dear Experts,
I have the SQL Statement as below to fetch records from two Access 2003 tables "Attendance" and "Employee". It works perfectly on Access 2003 Query Editor.
Unfortunately, it never gets working on VB2010.
Could you please help to review and advise ?
Best Regards,
Patrick
SQL Statement for fetching Attendance records between Sep 1, 2011 and Sep 16, 2011SELECT Employee.Name, Employee.Dept, Employee.Division, Attendance.ADate, Attendance.AOn, Attendance.AOff, Attendance.AOff2, Attendance.OVTM, Attendance.OVTM2, Attendance.WeekDay, Attendance.Holiday, Attendance.LateMin FROM Attendance INNER JOIN Employee ON Attendance.EName = Employee.Name
WHERE Employee.Name Like '*' AND Employee.Dept Like '*' AND Employee.Division Like '*'
AND Attendance.ADate Between #09/01/2011# And #09/16/2011# AND Employee.Attd=-1
ORDER BY Employee.Name, Attendance.ADate;
Coding in VB2010
Dim cnn As New OleDb.OleDbConnection
Dim dbFullPath = "\\JPHK-FS2\Attendance\Att endance_be .mdb"
cnn = New OleDb.OleDbConnection("Pro vider=Micr osoft.Jet. OLEDB.4.0; Data Source=" & dbFullPath & ";Jet OLEDB:Engine Type=4;")
Dim strSQL As New OleDb.OleDbCommand("SELECT Employee.Name, Employee.Dept, Employee.Division, Attendance.ADate, Attendance.AOn, Attendance.AOff, Attendance.AOff2, Attendance.OVTM, Attendance.OVTM2, Attendance.WeekDay, Attendance.Holiday, Attendance.LateMin FROM Attendance INNER JOIN Employee ON Attendance.EName = Employee.Name WHERE Employee.Name Like '*' AND Employee.Dept Like '*' AND Employee.Division Like '*' AND Attendance.ADate Between #09/01/2011# And #09/16/2011# AND Employee.Attd=-1 ORDER BY Employee.Name, Attendance.ADate", cnn)
Dim AttdDataAdapter As New OleDb.OleDbDataAdapter(str SQL)
Dim AttdDataTable As New DataTable("Attendance")
AttdDataAdapter.Fill(AttdD ataTable)
DataGridView1.DataSource = AttdDataTable
cnn.Close()
I have the SQL Statement as below to fetch records from two Access 2003 tables "Attendance" and "Employee". It works perfectly on Access 2003 Query Editor.
Unfortunately, it never gets working on VB2010.
Could you please help to review and advise ?
Best Regards,
Patrick
SQL Statement for fetching Attendance records between Sep 1, 2011 and Sep 16, 2011SELECT Employee.Name, Employee.Dept, Employee.Division, Attendance.ADate, Attendance.AOn, Attendance.AOff, Attendance.AOff2, Attendance.OVTM, Attendance.OVTM2, Attendance.WeekDay, Attendance.Holiday, Attendance.LateMin FROM Attendance INNER JOIN Employee ON Attendance.EName = Employee.Name
WHERE Employee.Name Like '*' AND Employee.Dept Like '*' AND Employee.Division Like '*'
AND Attendance.ADate Between #09/01/2011# And #09/16/2011# AND Employee.Attd=-1
ORDER BY Employee.Name, Attendance.ADate;
Coding in VB2010
Dim cnn As New OleDb.OleDbConnection
Dim dbFullPath = "\\JPHK-FS2\Attendance\Att
cnn = New OleDb.OleDbConnection("Pro
Dim strSQL As New OleDb.OleDbCommand("SELECT
Dim AttdDataAdapter As New OleDb.OleDbDataAdapter(str
Dim AttdDataTable As New DataTable("Attendance")
AttdDataAdapter.Fill(AttdD
DataGridView1.DataSource = AttdDataTable
cnn.Close()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Éric
He is using Access. He should use the Access syntax since the String will be sent straight to Access.
He is using Access. He should use the Access syntax since the String will be sent straight to Access.
I am sure not. ADO is an extra layer with an engine that affects the queries
The engine affects the queries when you use tools such as a CommandBuilder. SQL is always passed straight. There is no treatment done, because different applications use slightly different implementations. That is one of the many reasons stored procedures are preferred. There is a better chance that they will still work if you ever move the data from one database to another.
Passing dates between ' ' is never a good thing anyway, because your are then dependant on the culture. # # are better because you have a standard format (with Microsoft databases as least) of month-day-year.
Passing dates between ' ' is never a good thing anyway, because your are then dependant on the culture. # # are better because you have a standard format (with Microsoft databases as least) of month-day-year.
ASKER
Thanks a lot !
When you say "it never gets working on VB2010", what do you mean? Do you get an error message? Is the Fill returning nothing?
What is the code after the table is filled? Maybe your way of accessing the DataTable is wrong.
Is what you give us the exact same coding that you use in VB? The dates are hardcoded in what you show us. This is very rare in a SQL command of this type. The dates usually comes from somewhere else and are concatenated into the SQL. Lot of problems arise at that point.
Are you working in 64-bits? There are issues between 64-bits applications and Access 2003 databases.