Filling a DataGridView from SQLReader

PowerMan2015 picture PowerMan2015 · Aug 9, 2013 · Viewed 36.2k times · Source

Im a little stuck on some code that im writing

An outline is that i am reading some data in from an SQL database and wantint to display it in a DataGridView on a form. I have confirmed that there is data being returned from the database but am unsure as to why this isnt appearing. I have followed a number of tutorials from the internet but so far non have worked

here is my code

Private Sub PopulateGrid()
    Dim Con As New SqlClient.SqlConnection
    Dim strCon As String = CropTrackMod.strConn
    Dim strCommand As String = "select * from customer"


    Try
        Con.ConnectionString = strCon
        Dim Cm As New SqlClient.SqlCommand(strCommand, Con)
        Con.Open()
        Dim reader As SqlClient.SqlDataReader = Cm.ExecuteReader()

        'test to confirm data received
        reader.Read()
        MsgBox(reader.Item("ContactName"))


        DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = reader
        DataGridView1.Refresh()



    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error")

    Finally
        If Con.State = ConnectionState.Open Then
            Con.Close()
        End If
    End Try

End Sub

i have also tried to implement a datatable but receive a conversion error on the data type any help would be appreciated

thanks guys

Answer

Steve picture Steve · Aug 9, 2013

You can't bind a datareader directly to a datagridview in WinForms. Instead you could load a datatable with your reader and assign the datatable to the datasource of the DataGridView

Dim dt = new DataTable()
dt.Load(reader)
DataGridView1.AutoGenerateColumns = True
DataGridView1.DataSource = dt
DataGridView1.Refresh()