Solved

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

Posted on 2009-07-14
27
532 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:Scamquist
  • 14
  • 13
27 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24859649
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24859796
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24859920
So, you want to replace "Green Up", "Yellow Up" with an image file name?
0
 
LVL 1

Author Comment

by:Scamquist
ID: 24860005
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24860025
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24860263
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24860872
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24861418
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24861677
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24862238
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24862781
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24862869
When I paste the code into the Page_Load, DataView
Type 'DataView' is not defined
Type 'DataColumn' is not defined
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24862879
Adding Imports statements (System.Data):


Imports System.Data
 

Partial Class _Default

    Inherits System.Web.UI.Page

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:Scamquist
ID: 24863463
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24863482
GridView.AutoGenerateColumns="False"
0
 
LVL 1

Author Comment

by:Scamquist
ID: 24863630
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24863671
Sorry, got going a little too fast:

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

Author Comment

by:Scamquist
ID: 24863845
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24863880
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24866029
I changed the AutoGenerteColumns property to false.  Now, I lost the WeekEnd left vertical column and the header names.
GridView-LostWeekandHeader.jpg
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24868720
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24871794
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24871844
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24872078
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
 
LVL 1

Author Comment

by:Scamquist
ID: 24872387
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
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 24872771
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
 
LVL 1

Author Closing Comment

by:Scamquist
ID: 31604385
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now