Link to home
Start Free TrialLog in
Avatar of gardmanIT

asked on

Conditional image in datagrid

Hey all,

Only just getting into coming from a VBA background and trying to taking the plunge into the adult world of programming :P

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.



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()

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

        Return table
    End Function

Open in new window

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

Open in new window

Avatar of Ajay Sharma
Ajay Sharma
Flag of India image

You can add a ImageField column in the grid view like below:

    <asp:ImageField HeaderText='Traffic Status' DataImageUrlField='<%# getImage(Eval("VariancePcnt")) %>' />

Now in the code behind create a getImage function:
protected string getImage(int pct)
    if (pct<=30) { return "~\Greenthumbsup.png";}
    if (pct>30 && pct<=50) { return "~\Amberthumbsup.png";}
    if (pct>50) { return "~\Redthumbsup.png";}

Sorry the above code is in c#, you need to implement same vb
Avatar of Nasir Razzaq
For VB

protected Function getImage(pct As Integer) As String
    If pct<=30 Then
       return "~\Greenthumbsup.png"
    End If
    If pct>30 AND pct<=50 Then
       return "~\Amberthumbsup.png"
    End If
    If pct>50 Then
       return "~\Redthumbsup.png"
    End If
End Function
I would prefer, to implement this logic at database level i.e. into SQL query or stored procedure, whichever is with better performance.
Avatar of gardmanIT

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How is that better than

><asp:ImageField HeaderText='Traffic Status' DataImageUrlField='<%# getImage(Eval("VariancePcnt")) %>' />


And if stored procedure is your preferred approach then you can easily add a table with percentage values and image URLs and use CASE statements in your SQL to return correct image URL.
Avatar of gardmanIT


not using its not a web application...perhaps I was not clear on this!

using a case statement to calculate which image to display was never the problem. the problem was how to display that image in the datagridview based on that percent... had it of been in I could of put the DataImageUrlField in its place but there is no way to insert into a sql result set a field that returns an image unless I am using a binary object with the image already stored in the table.. which I am not!
I would also like state that I meant no offence to the people that kindly tried to help... but it seemed to me the responses were focusing on the logic of the percent.. my problem was always

How to actually make an image show in a datagridview based on a value on the same row of that datagridview..!
Yeah. I jumped on the bandwagon of ajaysharma. For future, please remember to either use the word DataGridView for winforms or GridView for webforms.
Fair enough I wasnt specific enough... that is down to lack of knowledge.. but im learning quick!.

Tested and worked!