Conditional Image in a datagrid

[Reggie213]

Hey all,

First time poster to this forum... doubt it will be my last...

Only just getting into VB.net coming from a VBA background and trying to taking the plunge into the adult world of programming

So I have managed to create a number of functions that retrieve data for me from a SQL server and either put it into a datatable or sqldatareader.

I have then managed to apply the data to the datasource of a datagrid.

I have been asked to produce a traffic light icon for each row based on the calculation of a certain field called [VariancePcnt] kind of like 0% to 30% is green, 31 to 50 is amber >50 is Red.

I have managed to add a column and assign an image to it but have not clue how to actually make this based on a condition derived from the data I retrieve from SQL!

I have attached all the detail I think is relevant below, forgive me if I am missing anything.

Thanks

Reg

Interface : Visual Studio 2010
Framework : .net 2.0

My data retrieval code

Function fn_getdata(ByVal strSQL As String) As DataTable
        Dim dataadapter As SqlDataAdapter
        'Dim command As SqlCommandBuilder
        Dim cnn As SqlConnection
        Dim table As New DataTable


        cnn = conCreate()
        cnn.Open()


        dataadapter = New SqlDataAdapter(strSQL, cnn)
        'command = New SqlCommandBuilder(dataadapter)
        dataadapter.Fill(table)


        Return table
    End Function

My datasource code

    Public Sub GenerateData()
        strSQL = "SELECT [Item],[Description],[PrefSupp],[SuppName],[PreviousForecast],[PreviousSales],[Variance],[VariancePcnt]," & _
                 "[UnitCost],[MinimumOrder][LeadTime],[CurrentStock],[Order_Cover],[ProductGrp], '' as img2 FROM [vw_Final_Report] ORDER BY item ASC"


        Me.dgdata.DataSource = fn_getdata(strSQL)
        dgdata.Columns(1).Width = 200
        dgdata.Columns(3).Width = 100
    End Sub

 

[Reggie213]

Hi All,

Solved this problem by doing the following :

Instead of assigning the datasource directly to the datagrid

[LEFT][COLOR=#333333]me.dgdata.datasource = fn_getdata(strSQL)[/COLOR][/LEFT]

I saved it to a datatable instead

Dim tmpDatatable as datatable
[/COLOR]tmpDatatable = fn_getdata(strSQL)

Then I added an extra column to the data table

tmpDatatable.Columns.Add("Check", GetType(Image))

Then I looped through the datatable

For Each row As DataRow In tmpDatatable.Rows
            row("Check") = getimage(row("variancepcnt"))
        Next row

The getimage function

Public Function getimage(pct As Integer)
        Dim imgGreen As Image = Image.FromFile(".\\images\flag_green2.ico")
        Dim imgAmber As Image = Image.FromFile(".\\images\flag_orange.ico")
        Dim imgRed As Image = Image.FromFile(".\\images\flag_red.ico")
        Select Case pct
            Case Is < 85
                Return imgRed
            Case 85 To 115
                Return imgGreen
            Case Is > 115
                Return imgAmber
            Case Else
                Return imgGreen
        End Select
    End Function

​