Solved

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

Posted on 2008-06-23
14
2,645 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
ID: 21850947
What type database you are using?  Microsoft SQL server or Access Database?
0
 
LVL 7

Expert Comment

by:60MXG
ID: 21850980
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
ID: 21850988
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:crickpaolo
ID: 21852412
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
ID: 21852516
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
ID: 21852532
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
 

Author Comment

by:crickpaolo
ID: 21857120
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
ID: 21857144
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
ID: 21857437
You need to convert the integer value to string first before use Chandan's example.
0
 

Author Comment

by:crickpaolo
ID: 21857703
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
ID: 21857957
attachment...
ExportPage.txt
0
 

Author Comment

by:crickpaolo
ID: 21860748
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
ID: 21860803
I am splitting the points between your guys for the effort.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 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