Solved

Render Boolean Column Bound to a Gridview as "Yes/No" instead of s checkbox

Posted on 2008-06-23
14
2,598 Views
Last Modified: 2013-11-07
Hi Experts,

I am binding a DataTable to a Gridview (AutoGenerateColumns=true), and the columns which are defined as boolean (bit), are being rendered as checkbxes.

Is there a way to have them rendered on the Gridview as "True" or "False", instead of a checkbox?

I am using VB.NET (.NET Framework 2.0).

Thanks,
Paolo
0
Comment
Question by:crickpaolo
  • 6
  • 5
  • 2
14 Comments
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
What type database you are using?  Microsoft SQL server or Access Database?
0
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
you can use the case if you are using Microsoft SQL server.  See the example here

this section of the code is written in Microsoft SQL Server's Stored Procedure
-------------------------------
select
          Id,
          case when PurchaseYesNo='True' then 'Yes'
           else 'No' end as PurchaseYesNo,
          ItemPurchaseId
from Inventory
----------------------------------        
0
 
LVL 7

Accepted Solution

by:
60MXG earned 250 total points
Comment Utility
So in your case it would be something like this

select
          Id,
          case when PurchaseYesNo=1 then 'True'
           else 'False' end as PurchaseYesNo,
          ItemPurchaseId
from Inventory
0
 

Author Comment

by:crickpaolo
Comment Utility
Thanks 60MXG.  Actually, my datatable is dynamic. It is populated via a SQL string that is passed on to the page, so I have not control over the SELECT statement.

Is there any gridview property that I can set, to render boolean fields  to text ("True" or "False") instead of checkboxes?
0
 
LVL 7

Expert Comment

by:Chandan_Gowda
Comment Utility
if your query is dynamic.then you have manually set your columns to true or false after databind

find the column in the code behind and then replace the values by true or false.
0
 
LVL 7

Assisted Solution

by:Chandan_Gowda
Chandan_Gowda earned 250 total points
Comment Utility
please find the attached code
 foreach (DataGridItem dgItem in datagrid.Items)

        {

                    //change the cell index as per u r grid 

                    string strVal = dgItem.Cells[0].Text;

                    if (strVal=="1")

                    {

                        dgItem.Cells[0].Text="True";

                    }

                    else

                    {

                        dgItem.Cells[0].Text="False";

                    }

            }

        }

Open in new window

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:crickpaolo
Comment Utility
Thanks Chandan. However, I don't know which columns are boolean beforehand, so I cannot hardcode the cell numbers.  Also, some none-boolean columns actually contain 1's and 0's (integer values) so the logic from your code won't work.

Is there an easier way to to this, like a gridview property or something that I can just set, to render boolean fields as text?
0
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
You can use Chandan Gowda's example here.  It should work.  However I find out that it is slightly faster for the servers to execute the code if I used stored procedure.  Both examples here should work on your problem.
0
 
LVL 7

Expert Comment

by:60MXG
Comment Utility
You need to convert the integer value to string first before use Chandan's example.
0
 

Author Comment

by:crickpaolo
Comment Utility
Not sure I follow...
Please find attached my code. It is a page that takes in a SQL statement, and exports it to Excel.  

The gridMaster.RenderControl(hw) statements bombs out when I get boolean data types on the gridview. Which is why I need to convert booleans to text.
0
 

Author Comment

by:crickpaolo
Comment Utility
attachment...
ExportPage.txt
0
 

Author Comment

by:crickpaolo
Comment Utility
Never mind. I've figured out how to do it. Please see attached code...
<!-- Imported .NET Namespaces -->

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SQLClient" %>

<%@ Import Namespace="System.IO.StringWriter" %>

<%@ Import Namespace="System.Web.UI.HtmlTextWriter" %>
 

<%@ Page Language="VB" %>
 

<script runat="server">

    'Global variables

    Dim conn As SqlConnection

    Dim daMaster As SqlDataAdapter

    Dim dsMaster As DataSet = New DataSet()

  

    Sub Page_Load()

      

        conn = New SqlConnection(ConfigurationManager.ConnectionStrings("EmployeeDB").ConnectionString)

  

        ' Export to Excel

        Response.Clear()

        Response.AddHeader("content-disposition", "attachment;filename=Export.xls")

       

        Response.ContentType = "application/vnd.ms-excel"

        Response.Charset = ""

            

        If Not Page.IsPostBack Then

            Call Load_Master_Grid()

            'Call Prepare_Grid_For_Export(gridMaster)

        End If

    End Sub

  

    Sub Load_Master_Grid()

        Dim strSQL As String

        strSQL = Session("ExportSQL")

    

        'create the temporary Datatable

        daMaster = New SqlDataAdapter(strSQL, conn)

        daMaster.Fill(dsMaster, "dtMaster")

        

        gridMaster.DataSource = dsMaster.Tables("dtMaster")

        gridMaster.DataBind()

        

       

        'delete the datatable

        If dsMaster.Tables.CanRemove(dsMaster.Tables("dtMaster")) Then

            dsMaster.Tables.Remove("dtMaster")

        End If

   

        Dim sw As System.IO.StringWriter = New System.IO.StringWriter

        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)

        

        '  Create a form to contain the grid

        '* NOTE: for some reason, ASP.NET does not like it when the gridMaster.RenderControl(htw)

        '  command when the SQL has a boolean field. The only workaround I've found

        '  is to copy the grid into a Table, then do a table.RenderControl(htw)

        Dim table As Table = New Table

        table.GridLines = gridMaster.GridLines

        

        '  add the header row to the table

        If (Not (gridMaster.HeaderRow) Is Nothing) Then

            Call Prepare_Grid_For_Export(gridMaster.HeaderRow)

            table.Rows.Add(gridMaster.HeaderRow)

        End If

        

        '  add each of the data rows to the table

        For Each row As GridViewRow In gridMaster.Rows

            Call Prepare_Grid_For_Export(row)

            table.Rows.Add(row)

        Next

        

        '  add the footer row to the table

        If (Not (gridMaster.FooterRow) Is Nothing) Then

            Prepare_Grid_For_Export(gridMaster.FooterRow)

            table.Rows.Add(gridMaster.FooterRow)

        End If

        

        'render the table into the htmlwriter

        table.RenderControl(htw)

        'render the htmlwriter into the response

        Response.Write(sw.ToString)

        Response.End()

   

    End Sub

  

   

    

    ' Replace any of the contained controls with literals

    Sub Prepare_Grid_For_Export(ByRef control As Control)

        Dim i As Integer = 0

        Do While (i < control.Controls.Count)

            Dim current As Control = control.Controls(i)

            If (TypeOf current Is CheckBox) Then

                control.Controls.Remove(current)

                control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))

            End If

            If current.HasControls Then

                Call Prepare_Grid_For_Export(current) 'recursive call

            End If

            i = (i + 1)

        Loop

    End Sub

 

    Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

        ' Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time. 

    End Sub

  

    

</script>
 
 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

    <title>Export to Excel</title>

</head>

<body>

    <form id="form1" runat="server">

        <asp:GridView ID="gridMaster" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="5" Font-Names="Tahoma" Font-Size="11px" ForeColor="Black" GridLines="Both">

            <FooterStyle BackColor="white" />

            <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />

            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />

            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />

            <AlternatingRowStyle BackColor="white" />

        </asp:GridView>

    </form>

</body>

</html>

Open in new window

0
 

Author Comment

by:crickpaolo
Comment Utility
I am splitting the points between your guys for the effort.
0

Featured Post

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

Join & Write a Comment

Suggested Solutions

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

15 Experts available now in Live!

Get 1:1 Help Now