data table will not populate with complex sql select statement

[tatacco]

Below is the code I am using:

    Dim safe10_comp As Integer
    Dim ref_date As Date
    Dim dr As DataRow
    Dim dt As DataTable = New DataTable()
    Dim da As OleDb.OleDbDataAdapter = New OleDbDataAdapter()
    Dim Conn As OleDbConnection = New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString2").ConnectionString)
    Dim sql As String = "with a as (select t.employee_number, location_code, title_code, max(t.start_date) as reference_date from registration_style_view t where t.course_code = 'SAFE-10' and t.status = 'A' and t.registration_status in ('M','F') and title_code in ('MX42','MX02','SU54','TL09','SS12','MX49','MX59','SR21','AS15','631A','197A','SS25','MX01','MX41','SU62','MX54','MX14','MX12','MX13','M411','MX52','MX60','MX11','MX16','MX51','MX53','MX56','LD55','SI66') group by t.employee_number, location_code, title_code union select t.employee_number, location_code, title_code, max(t.start_date) as reference_date from registration_style_view t where t.course_code = 'SAFE10' and t.status = 'A' and t.registration_status in ('M','F') and title_code in ('MX42','MX02','SU54','TL09','SS12','MX49','MX59','SR21','AS15','631A','197A','SS25','MX01','MX41','SU62','MX54','MX14','MX12','MX13','M411','MX52','MX60','MX11','MX16','MX51','MX53','MX56','LD55','SI66') group by t.employee_number, location_code, title_code union select t.employee_number, location_code, title_code, max(t.status_date) as reference_date from registration_style_view t where t.course_code like 'SAFE-10R%' and t.status = 'A' and t.registration_status in ('M','F') and title_code in ('MX42','MX02','SU54','TL09','SS12','MX49','MX59','SR21','AS15','631A','197A','SS25','MX01','MX41','SU62','MX54','MX14','MX12','MX13','M411','MX52','MX60','MX11','MX16','MX51','MX53','MX56','LD55','SI66') group by t.employee_number, location_code, title_code union select t.employee_number, location_code, title_code, max(t.status_date) as reference_date from registration_style_view t where t.course_code like 'SAFE10R%' and t.status = 'A' and t.registration_status in ('M','F') and title_code in ('MX42','MX02','SU54','TL09','SS12','MX49','MX59','SR21','AS15','631A','197A','SS25','MX01','MX41','SU62','MX54','MX14','MX12','MX13','M411','MX52','MX60','MX11','MX16','MX51','MX53','MX56','LD55','SI66') group by t.employee_number, location_code, title_code union select t.employee_number, location_code, title_code, max(t.status_date) as reference_date from registration_style_view t where t.course_code like 'SAFE10S%' and t.status = 'A' and t.registration_status in ('M','F') and title_code in ('MX42','MX02','SU54','TL09','SS12','MX49','MX59','SR21','AS15','631A','197A','SS25','MX01','MX41','SU62','MX54','MX14','MX12','MX13','M411','MX52','MX60','MX11','MX16','MX51','MX53','MX56','LD55','SI66') group by t.employee_number, location_code, title_code) select p.employee_number, p.location_code, p.title_code, max(to_date(a.reference_date)) as reference_date from person p left outer join a on p.employee_number = a.employee_number where p.department = 'MAINTENANCE' and p.title_code in ('MX42','MX02','SU54','TL09','SS12','MX49','MX59','SR21','AS15','631A','197A','SS25','MX01','MX41','SU62','MX54','MX14','MX12','MX13','M411','MX52','MX60','MX11','MX16','MX51','MX53','MX56','LD55','SI66') and p.status = 'A' group by p.employee_number, p.location_code, p.title_code order by location_code"
    Conn.Open()
    Dim selectCMD As New OleDbCommand(sql, Conn)
    da.SelectCommand = selectCMD

        da.Fill(dt)

        safe10_comp = dt.Rows.Count

[Reverend Jim]

Do you expect us to tell you what is wrong with that SQL statement? Perhaps it is doing exactly what it is being told, and returning no data is what is to be expected. That's going to be hard to say seeing as how you haven't told us what you are trying to do or even what the tables look like.

By the way, in ten years working with MS SQL and reviewing code by many developers, I have only seen one query worse than this one. The programmer tasked with maintaining the code (that he didn't write) wanted to know how to optimize it. Unfortunately, he did not know what the query was supposed to do. The only thing I could offer was the comment "it sucks to be you". He agreed.

[Minimalist]

Are you pointing to your database location? Something like this:

 AccessConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
                     "Dbq=nwind.mdb;" & _
                     "DefaultDir=C:\program files\devstudio\vb;" & _
                     "Uid=Admin;Pwd=;"

[kRod]

tatacco; "My advice to you is to start drinking heavily" -John 'Bluto' Blutarsky

[G_Waddell]

Hi,

Your query is soo complex that the connection string is probably timing out.

If you run that query in SQL (not through code but Management Studio,) how long does it take?

BTW you're missing a closing bracket.