Catalin Cernat Catalin Cernat - 6 months ago 19
Vb.net Question

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

I'm stuck! I have the function below, wich 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 dont 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

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