Member Avatar for 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
  • 5 Contributors
  • 4 Replies
  • 215 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by G_Waddell
Member Avatar for 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.

Edited by Reverend Jim
Member Avatar for 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=;"
Member Avatar for kRod

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

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.