Avatar of gardmanIT
gardmanIT
 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

Visual Basic.NETMicrosoft Development.NET Programming

Avatar of undefined
Last Comment
gardmanIT

8/22/2022 - Mon
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
gardmanIT

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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..!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23