Function to return SQL query and display result in a new Form's datagrid view

Question

I'm stuck! I have the function below, which needs to return a table and display it on a new Form's datagrid. If I run the query in SSMS it returns a table. But I don't know how to display it in a datagrid. Any tips will be appreciated.

Public Shared Function verificaschimb(ByVal schimb As String, data As DateTime) As Integer
    Dim verificare As New SqlDataReader
    Dim con As New SqlConnection
    Try
        con = New SqlConnection("Data Source=SVNAV;Initial Catalog=NAV_Vermorel_Live;User ID=sa;Password=1234")
        Using cmd As SqlCommand = New SqlCommand("Select COUNT(DISTINCT [Cod Angajat]), [Cod Angajat], [Nume], [Timp declarat] FROM [SC Vermorel SRL$ProductieVE] WHERE (sum([Timp declarat] <8 AND cast(CONVERT(varchar(8), Data, 112) As DateTime) = @data2) And ([Schimb] = '" & ProceseazaSCH(Now()) & "' GROUP BY [Nume], [Cod Angajat], [Timp declarat] HAVING sum([Timp declarat])<8", con)
            cmd.Parameters.AddWithValue("@data2", data)
            con.Open()
            verificare = cmd.ExecuteReader
            con.Close()
        End Using
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    Finally
        If con IsNot Nothing Then
            If con.State = ConnectionState.Open Then                    '
                con.Close()
            End If
            con.Dispose()
        End If
    End Try
    Return verificare
End Function

Answer 1

The most common way to bind to DataGridView is using a DataTable. Your function returns integer which can't be used as data source.

I made some changes to your function to return DataTable and some lines of code for databinding:

    Public Shared Function verificaschimb(ByVal schimb As String, data As DateTime) As DataTable
        Dim verificare As New DataTable
        Dim con As New SqlConnection
        Try
            con = New SqlConnection("Data Source=SVNAV;Initial Catalog=NAV_Vermorel_Live;User ID=sa;Password=1234")
            Using cmd As SqlDataAdapter = New SqlDataAdapter("Select COUNT(DISTINCT [Cod Angajat]), [Cod Angajat], [Nume], [Timp declarat] FROM [SC Vermorel SRL$ProductieVE] WHERE (sum([Timp declarat] <8 AND cast(CONVERT(varchar(8), Data, 112) As DateTime) = @data2) And ([Schimb] = '" & ProceseazaSCH(Now()) & "' GROUP BY [Nume], [Cod Angajat], [Timp declarat] HAVING sum([Timp declarat])<8", con)
                cmd.SelectCommand.Parameters.AddWithValue("@data2", data)
                con.Open()
                cmd.Fill(verificare)
                con.Close()
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            If con IsNot Nothing Then
                If con.State = ConnectionState.Open Then                    '
                    con.Close()
                End If
                con.Dispose()
            End If
        End Try
        Return verificare
    End Function

    Public Sub MainCode()
        Dim dt As DataTable = verificaschimb("test", Now.Date)
        DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = dt
        DataGridView1.Refresh()
    End Sub