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

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
crickpaoloAsked:
Who is Participating?
 
60MXGConnect With a Mentor Commented:
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
 
60MXGCommented:
What type database you are using?  Microsoft SQL server or Access Database?
0
 
60MXGCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
crickpaoloAuthor Commented:
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
 
Chandan_GowdaCommented:
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
 
Chandan_GowdaConnect With a Mentor Commented:
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
 
crickpaoloAuthor Commented:
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
 
60MXGCommented:
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
 
60MXGCommented:
You need to convert the integer value to string first before use Chandan's example.
0
 
crickpaoloAuthor Commented:
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
 
crickpaoloAuthor Commented:
attachment...
ExportPage.txt
0
 
crickpaoloAuthor Commented:
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
 
crickpaoloAuthor Commented:
I am splitting the points between your guys for the effort.
0
All Courses

From novice to tech pro — start learning today.