Conditional image in datagrid

gardmanIT
gardmanIT used Ask the Experts™
on
Hey all,

Only just getting into VB.net 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.

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

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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can add a ImageField column in the grid view like below:

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

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
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Hi Mroonal,

The logic was never the problem, the other responses completely missed the point. My problem was getting an image into a datatable based on some logic. The logic being easy to work out. I would always do as many logic calculations at the stored procedure level as possible

I decided in the end to add a column to the datatable pre binding to the datagrid. Then sadly having to loop through the datatable to apply the image to the column based on the percent logic.

I ahve attached the working code below in case anyone else gets stuck on this!

 Public Sub GenerateData()
        Dim tmpDatatable As DataTable

        strSQL = "SELECT top 1000 [Item],[Description],[PrefSupp],[SuppName],[PreviousForecast],[PreviousSales],[Variance],[VariancePcnt]," & _
                 "[UnitCost],[MinimumOrder],[LeadTime],[CurrentStock],[Order_Cover],[ProductGrp] FROM [vw_Final_Report] ORDER BY item ASC"

        'Get the datatable
        tmpDatatable = fn_getdata(strSQL)

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

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

        Me.dgdata.DataSource = tmpDatatable

        'Put custom column sizes here
        dgdata.Columns(1).Width = 150
        dgdata.Columns(3).Width = 100

    End Sub

Open in new window


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

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
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.

Author

Commented:
not using ASP.net 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 asp.net 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!

Author

Commented:
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..!
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yeah. I jumped on the bandwagon of ajaysharma. For future, please remember to either use the word DataGridView for winforms or GridView for webforms.

Author

Commented:
Fair enough I wasnt specific enough... that is down to lack of knowledge.. but im learning quick!.

Thanks

Author

Commented:
Tested and worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial