asked on

Conditional image in datagrid

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

Ajay Sharma

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

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

Mrunal

I would prefer, to implement this logic at database level i.e. into SQL query or stored procedure, whichever is with better performance.

ASKER CERTIFIED SOLUTION

gardmanIT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nasir Razzaq

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.

gardmanIT

ASKER

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!

gardmanIT

ASKER

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..!

Nasir Razzaq

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

gardmanIT

ASKER

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

Thanks

gardmanIT

ASKER

Tested and worked!