Solved

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

Posted on 2008-06-23
14
2,612 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

19 Experts available now in Live!

Get 1:1 Help Now