Load grid data from data base to gridview without

Question

I am trying to fetch info from mssql server database and want to display it on gridview. But the problem is that i got an error while i run it as Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index and my code is

    img_green = ("~\Icons\" & "circle_green.ico")
    img_orange = ("~\Icons\" & "circle_orange.ico")
    img_red = ("~\Icons\" & "circle_red.ico")

    GridView1.AllowPaging = True
    GridView1.PageSize = 10
    cmd = New SqlCommand("SELECT  a.curr_datetime, a.site_id, b.site_name, a.dc_volt, a.curr_temp, a.eb_val, a.dc_low, a.hrt_temp, a.curr_dfs, a.curr_dft, a.curr_llop, a.curr_dgonl, a.fa_alarm, a.door_open, a.curr_spare FROM final_test a INNER JOIN site_details b ON a.site_id = b.site_id;", conn)

    If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If

    da.SelectCommand = cmd
    da.Fill(ds, "final_test")
    dt = ds.Tables("final_test")

    dr = cmd.ExecuteReader

    If dr.Read() Then

        GridView1.Rows(index_flag).Cells(0).Text = ds.Tables(0).Rows(0).Item("curr_datetime").ToString


        lcl_ebval = ds.Tables(0).Rows(0).Item("eb_val").ToString
        If lcl_ebval = 0 Then
            eb_img = img_green
        ElseIf lcl_ebval = 1 Then
            eb_img = img_red
        End If
        GridView1.Rows(index_flag).Cells(5).Text = ds.Tables(0).Rows(0).Item("eb_img").ToString


        lcl_dclow = ds.Tables(0).Rows(0).Item("dc_low").ToString
        If lcl_dclow = 0 Then
            dclow_img = img_green
        ElseIf lcl_dclow = 1 Then
            dclow_img = img_red
        End If
        GridView1.Rows(index_flag).Cells(6).Text = ds.Tables(0).Rows(0).Item("dclow_img").ToString

        lcl_hrt = ds.Tables(0).Rows(0).Item("hrt_temp").ToString
        If lcl_hrt = 0 Then
            hrt_img = img_green
        ElseIf lcl_hrt = 1 Then
            hrt_img = img_red
        End If
        GridView1.Rows(index_flag).Cells(7).Text = ds.Tables(0).Rows(0).Item("hrt_img").ToString

        lcl_dfs = ds.Tables(0).Rows(0).Item("curr_dfs").ToString
        If lcl_dfs = 0 Then
            dfs_img = img_green
        ElseIf lcl_dfs = 1 Then
            dfs_img = img_red
        End If
        GridView1.Rows(index_flag).Cells(8).Text = ds.Tables(0).Rows(0).Item("dfs_img").ToString

        lcl_dft = ds.Tables(0).Rows(0).Item("curr_dft").ToString
        If lcl_dft = 0 Then
            dft_img = img_green
        Else
            dft_img = img_orange
        End If
        GridView1.Rows(index_flag).Cells(9).Text =    ds.Tables(0).Rows(0).Item("dft_img").ToString


        index_flag = index_flag + 1

Answer 1

I can see that you are populating the GridView manually by setting each rows' cells the value you get from datareader.

But actually, you can populate the grid by simply:

GridView1.DataSource = dt
GridView1.DataBind()

Answer 2

I think you are making this more difficult than you have to. Read the entire data into a table, bind the data and then use the OnRowDataBound event to modify the image.

Another way is to make a field that you then fill in and have the gridview display that image name. So you go through all of the records before binding set some field to the name of the image you want to use and then bind it.

I also noticed - in the line that says:

GridView1.Rows(index_flag).Cells(5).Text = ds.Tables(0).Rows(0).Item("eb_img").ToString

Is there actually a field called eb_img? You set the a local variable call eb_img in the lines above but then try to read it from the table. Don't you want to set the text to the variable eb_img not the table column?

That will only set the text, it won't actually display the image. If you want to build the cell yourself you need to define a control of type image and set the image location to eb_img.