Replace Text with Image in GridView using dynamic SQL Pivot as Data Source

I am using Visual Web Developer, ASP.NET 3.5 and SQL 2008 Express

I have a GridView with a datasource that contains    Task    WeekEnd   and TaskStatus (Top Image)

I replaced the words generated in TaskStatus with an image.  (Middle Image)
To do this, I used a statement
      <asp:ImageField DataImageUrlField="TaskStatus"
                      DataImageUrlFormatString="~/IMAGE/{0}.jpg" HeaderText="Task Status"
        SortExpression="TaskStatus" ItemStyle-HorizontalAlign="Center"
        ItemStyle-Width="150" >
      </asp:ImageField>

This works.

I have created a Stored Procedure that creates a dynamic SQL Pivot table that works well.

It has the Task as the Header, The WeekEnd on the left side and the TaskStatus is in the body.

Is it possible to replace the TaskStatus words with the image?   (Bottom Image
ALTER PROCEDURE dbo.TaskStatusPivot
as
 
Declare @strSQL varchar(max)
DECLARE @cols varchar(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                            '],[' + task
                    FROM    WeekTaskStatus
                    ORDER BY '],[' + task
                    FOR XML PATH('')
                  ), 1, 2, '') + ']'
 
 
set @strSQL = 'select CONVERT(varchar,WeekEnd,101) as WeekEnding, ' + @cols +
            ' from (
                  select WeekEnd, Task, TaskStatus from WeekTaskStatus) o
              pivot (Max(TaskStatus) for Task in (' + @cols + ')) p'
 
exec(@strSQL)

Open in new window

Pivot.jpg
LVL 1
ScamquistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
I am confused about what you are asking, and what the attached image is from?  To me, it looks like you already have an image where you need it.
0
ScamquistAuthor Commented:
The top image is the actual output when i use the  <asp:ImageField DataImageUrlField... statement to replace the text with the image.

The center image is what is returned in the GridView when I used the stored procedure.  

For the bottom image, I used excel and pasted images in to get a sample  of what I am trying to do.  

Currently, the stored procedure returns the pivot table with words.  I want to change the words to image.  I cannot use a hard coded solution because the dynamic SQL Pivot table may change the number of column as additional tasks are added.
0
Bob LearnedCommented:
So, you want to replace "Green Up", "Yellow Up" with an image file name?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ScamquistAuthor Commented:
I have a folder IMAGE with image files named Green Up.jpg, Yellow Down.jpg, Red Neutral.jpg, ect.

If the words Green Up are returned, I want to replace the words with the image    Green Up.jpg
Similarly, if the words Red Down are returned, I want to replace the words with the image Red Down.jpg
0
Bob LearnedCommented:
You have this field definition that does the job for you:

<asp:ImageField DataImageUrlField="TaskStatus"
                      DataImageUrlFormatString="~/IMAGE/{0}.jpg" HeaderText="Task Status"
        SortExpression="TaskStatus" ItemStyle-HorizontalAlign="Center"
        ItemStyle-Width="150" >
      </asp:ImageField>

That would take "Green Up", and turn it into "~/IMAGE/Green Up.jpg".
0
ScamquistAuthor Commented:
That works for the GridView using a datasource that uses a table named WeekTaskStatus.

To get the dynamic SQL Pivot, I am using a datasource that calls a stored procedure named TaskStatusPivot (the code in provided in the question).

The top and middle images are the actual output when I view the page in a browser.

I have added an image of the Design View.  The top gridview produces the top image in the question.  The bottom gridview produced the middle image with the words.


 <asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSource2" 
      Width="750px" Visible="False">
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
      ConnectionString="<%$ ConnectionStrings:NovumConnectionString %>" 
      SelectCommand="TaskStatusPivot" SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>

Open in new window

GridViewSample.jpg
0
Bob LearnedCommented:
I believe that all you need is a column name for DataImageUrlField, and mask for DataImageUrlFormatString.  Are you saying that DataImageUrlField is unknown at design time?  If that is so, then you might be able to get the column names from the SqlDataSource, and then configure ImageFields dynamically in the code-behind.
0
ScamquistAuthor Commented:
The DataImageUrlField and the DataImageUrlFormatString are unknown at the design time.  Is there a way to do this with the code-behind?  I am learning this and while I am glad to have gotten this far, I think I have stepped in over my head on getting the necessary code. :)
0
Bob LearnedCommented:
Here is a C# test to determine what needs to happen:


        DataTable dt = new DataTable();
 
        dt.Columns.Add("Budget");
 
        dt.Rows.Add("Green Up");
        dt.Rows.Add("Yellow Up");
 
        ImageField field = this.GridView1.Columns[0] as ImageField;
        field.DataImageUrlField = dt.Columns[0].ColumnName;
 
        this.GridView1.DataSource = dt;
        this.GridView1.DataBind();
 
 
      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
        <Columns>
          <asp:ImageField DataImageUrlFormatString="{0}.jpg" HeaderText="Budget">
          </asp:ImageField>
          <asp:BoundField DataField="Budget" HeaderText="Budget" SortExpression="Budget" />
        </Columns>
      </asp:GridView>

Open in new window

0
ScamquistAuthor Commented:
In the source code, I replace

    <asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSource2"
      Width="750px" Visible="True">
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"
      ConnectionString="<%$ ConnectionStrings:NovumConnectionString %>"
      SelectCommand="TaskStatusPivot" SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>

With
 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
        <Columns>
          <asp:ImageField DataImageUrlFormatString="{0}.jpg" HeaderText="Budget">
          </asp:ImageField>
          <asp:BoundField DataField="Budget" HeaderText="Budget" SortExpression="Budget" />
        </Columns>
      </asp:GridView>

Do I keep the
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"
      ConnectionString="<%$ ConnectionStrings:NovumConnectionString %>"
      SelectCommand="TaskStatusPivot" SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>

or is this replaced by the binding GridView2

Is the top section code-behind for the pageload event or for the gridvew?

Also, what do I need to change to get the code to VB?
0
Bob LearnedCommented:
1) That code was in Page_Load event.

2) I couldn't determine your language of choice, so I guessed C#.

3) You can generate code in the code-behind to generate the columns (untested example).




        Dim dv As DataView = TryCast(Me.SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
 
        For Each column As DataColumn In dv.Table.Columns
 
            Dim field As New ImageField()
            field.DataImageUrlField = column.ColumnName
            field.DataImageUrlFormatString = "~/IMAGE/{0}.jpg"
 
            Me.GridView1.Columns.Add(field)
 
        Next column

Open in new window

0
ScamquistAuthor Commented:
When I paste the code into the Page_Load, DataView
Type 'DataView' is not defined
Type 'DataColumn' is not defined
0
Bob LearnedCommented:
Adding Imports statements (System.Data):


Imports System.Data
 
Partial Class _Default
    Inherits System.Web.UI.Page

Open in new window

0
ScamquistAuthor Commented:
Close.  I created a new page.  Added a single GridView.  Pointed the DataSource1 at my stored procedure and received the image below.

What's more, I added a new task with updating the same three weeks and re-ran the gridview.  It added the new column and the picutre in the proper order

Very close.
GridViewPivot.jpg
0
Bob LearnedCommented:
GridView.AutoGenerateColumns="False"
0
ScamquistAuthor Commented:
Where does this line go.

I tried a few places and receive the error:

Reference to a non-shared member requires an object reference
0
Bob LearnedCommented:
Sorry, got going a little too fast:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
0
ScamquistAuthor Commented:
I assume I am to use the sorce code if the GridView1 which is below:

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
    </asp:GridView>
&nbsp;
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
      ConnectionString="<%$ ConnectionStrings:NovumConnectionString %>"
      SelectCommand="TaskStatusPivot" SelectCommandType="StoredProcedure">
    </asp:SqlDataSource>

I replaced the first line with

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">

When I view the result in the browser, there is no gridview at all.
0
Bob LearnedCommented:
You erased the DataSourceID attribute.  I try to show you where you need to make the changes, not provide the entire details.  You just need to select the GridView in the designer, and find the AutoGenerateColumns property in the property grid, and change the value to false.  That will make the change in the HTML that is shown below.

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AutoGenerateColumns="False"
    </asp:GridView>
0
ScamquistAuthor Commented:
I changed the AutoGenerteColumns property to false.  Now, I lost the WeekEnd left vertical column and the header names.
GridView-LostWeekandHeader.jpg
0
Bob LearnedCommented:
You need to set the column's HeaderText value, and add a BoundField column for the WeekEnding:


       Dim boundField As New BoundField()
       boundField.HeaderText = "Week Ending"
       boundField.DataField = "WeekEnding"
 
       Me.GridView1.Columns.Add(boundField)
 
       For Each column As DataColumn In dv.Table.Columns
 
            Dim imgField As New ImageField()
            imgField.HeaderText = column.ColumnName
            imgField.DataImageUrlField = column.ColumnName
            imgField.DataImageUrlFormatString = "~/IMAGE/{0}.jpg"
 
            Me.GridView1.Columns.Add(imgField)
 
        Next column

Open in new window

0
ScamquistAuthor Commented:
TheLearnedOne:, one last thing.  Is there a way to hide the second column?  it is showing up with a header WeekEnding with missing image box.

I have searched the forum to an answer and could not find a solution that worked.
GridViewPivot-ExtraColumn.jpg
0
Bob LearnedCommented:
I would think that you could test to see if that column has values that would generate invalid file names, and not add the column.


       For Each column As DataColumn In dv.Table.Columns
 
            Dim row As DataRow = dv.Table.Rows(0)
 
            Dim imageUrl As String = Server.MapPath(String.Format("~/IMAGE/{0}.jpg", _
               row(column.ColumnName)))
 
            If System.IO.File.Exists(imageUrl) Then
                Dim imgField As New ImageField()
                imgField.HeaderText = column.ColumnName
                imgField.DataImageUrlField = column.ColumnName
                imgField.DataImageUrlFormatString = "~/IMAGE/{0}.jpg"
 
                Me.GridView1.Columns.Add(imgField)
            End If
        Next column

Open in new window

0
ScamquistAuthor Commented:
Now I lost the first column that provides the Week End date.  This colum will never be null and will always be a date.  is there a way to show the date but not show the missing image column?
0
ScamquistAuthor Commented:
Is there a way to test if the header test = "WeekEnding" hide the column.

I tried the following code, but it does not work.
    Dim dv As DataView = TryCast(Me.SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
 
    For Each column As DataColumn In dv.Table.Columns
 
      Dim row As DataRow = dv.Table.Rows(0)
 
      Dim imageUrl As String = Server.MapPath(String.Format("~/IMAGE/{0}.jpg", _
         row(column.ColumnName)))
 
      If System.IO.File.Exists(imageUrl) Then
        Dim imgField As New ImageField()
        If imgField.HeaderText = "WeekEnding" Then
          column(Visible = False)
        Else
          imgField.HeaderText = column.ColumnName
          imgField.DataImageUrlField = column.ColumnName
          imgField.DataImageUrlFormatString = "~/IMAGE/{0}.jpg"
 
          Me.GridView1.Columns.Add(imgField)
        End If
    Next column

Open in new window

0
Bob LearnedCommented:
Aaah, yes, you replaced all the code with what I showed, which was only a part of the code.


    Dim boundField As New BoundField()
    boundField.HeaderText = "Week Ending"
    boundField.DataField = "WeekEnding"
 
    Me.GridView1.Columns.Add(boundField)
 
    Dim dv As DataView = TryCast(Me.SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
 
    For Each column As DataColumn In dv.Table.Columns
 
      Dim row As DataRow = dv.Table.Rows(0)
 
      If column.ColumnName <> "WeekEnding" Then
          Dim imgField As New ImageField()
 
          imgField.HeaderText = column.ColumnName
          imgField.DataImageUrlField = column.ColumnName
          imgField.DataImageUrlFormatString = "~/IMAGE/{0}.jpg"
 
          Me.GridView1.Columns.Add(imgField)
        End If
    Next column

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScamquistAuthor Commented:
TheLearnedOne - Thank you so very much.  I have been playing with this for a couple of weeks.  Now I need to take this apart and try to understand all of the code components.  This is amazing.  Thank you again.

BTW, there is a post that is similar.
question 07/15/09 05:40 PM, ID: 24574092 | Points: 200
or search on this
Line 308: e.Row.Cells[2].Visible = false
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.