Why is SQL in OLEDB? No record in DataGridView MS Access DB in VB.NET

Programming
, , , ,
1

I am using MS Access database and VB.NET to query data from a table. Results appear correctly in MS Access, but no records are displayed in the DataGridView. I have included the SQL query and screenshots of the results from MS Access and DataGridView. I have also included the code used for population.

Fenced Code Blocks

SQL Query:

SELECT EmpID, [Name], TotalCPI, RangeSalary
FROM Query1 AS E INNER JOIN (SELECT Left(RangeCPI,3) AS Start, Mid(RangeCPI,5,3) + 1 AS [End], RangeSalary 
     FROM tblPayrollDecision)  AS PD ON (E.TotalCPI < PD.End) AND (E.TotalCPI >= PD.Start);

Population Code:

Public Sub filltable(ByVal dtgrd As Object, ByVal design As String)
        Dim publictable As New DataTable
        Try
            da.SelectCommand = cmd
            da.Fill(publictable)
            dtgrd.DataSource = publictable
            Select Case design
                Case "EmpInfo"
                    dtgrd.Columns(0).Visible = False
            End Select
            da.Dispose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information)
        End Try
    End Sub
Public Sub jokenselect(ByVal sql As String)
        Try
            con.Open()
            With cmd
                .Connection = con
                .CommandText = sql
            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
        con.Close()
        da.Dispose()
    End Sub
  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 jokenselect("Select * From Query2")
   filltable(DataGridView1, "EmpPic")
    End Sub
2

The issue is that no records are displayed in the DataGridView, even though the results appear correctly in MS Access.

Here is the answer:

You are missing the code to open the connection before executing the SQL query. Update your jokenselect function as follows:

Public Sub jokenselect(ByVal sql As String)
    Try
        con.Open()
        With cmd
            .Connection = con
            .CommandText = sql
        End With
        da.SelectCommand = cmd ' Add this line to assign the command to the data adapter
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
    End Try
    con.Close()
    da.Dispose()
End Sub

By assigning the command to the data adapter, the filltable function will be able to retrieve the data correctly from the database.