[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Statement for VB2010

Posted on 2011-09-16
6
Medium Priority
?
266 Views
Last Modified: 2012-06-27
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\Attendance_be.mdb"
cnn = New OleDb.OleDbConnection("Provider=Microsoft.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(strSQL)
Dim AttdDataTable As New DataTable("Attendance")

AttdDataAdapter.Fill(AttdDataTable)
DataGridView1.DataSource = AttdDataTable
cnn.Close()
0
Comment
Question by:Patrick3388
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40
ID: 36553107
First of all, those Like "*" are useless. That means everything. If you want everything, no need to specify the field in the WHERE clause.

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.
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1000 total points
ID: 36559743
When you use ADO and/or ADO.net, * should be replaced with % and the # around dates should be replaced with '
0
 
LVL 40
ID: 36560969
@Éric

He is using Access. He should use the Access syntax since the String will be sent straight to Access.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36560989
I am sure not. ADO is an extra layer with an engine that affects the queries
0
 
LVL 40
ID: 36561074
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.
0
 

Author Closing Comment

by:Patrick3388
ID: 36579023
Thanks a lot !
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question