Convert Gridview column from int to text

Question

User534484856 posted

I have a gridview with 4 columns.  The gridview is connected to a sql database table. The 4th column header in the gridview is labeled "Jurisdiction" that has numbers 99, and 48 in it.  I want to convert those numbers into text so that in the gridview 99 would show as "Dispatch" and 48 would show as "Police".

This is my Source code:

   <div class="auto-style1">
                <center>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." GridLines="Vertical" Font-Size="Larger" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
                    <AlternatingRowStyle BackColor="#DCDCDC" />
                    <Columns>
                        <asp:BoundField DataField="Emp_Name" HeaderText="Employee Name" SortExpression="Emp_Name" />
                        <asp:BoundField DataField="Emp_ID" HeaderText="CAD ID #" SortExpression="Emp_ID" />
                        <asp:BoundField DataField="JurisdictionID" HeaderText="Jurisdiction" SortExpression="JurisdictionID" />
                        <asp:BoundField DataField="Pager" HeaderText="Cellphone #" SortExpression="Pager" />
                    </Columns>
                    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                    <SortedAscendingHeaderStyle BackColor="#0000A9" />
                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                    <SortedDescendingHeaderStyle BackColor="#000065" />
                </asp:GridView>
                </center>
            </div>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Reporting_SystemConnectionString1 %>" SelectCommand="SELECT [Emp_ID], [Emp_Name], [JurisdictionID], [Pager] FROM [Personnel] WHERE ([Emp_Name] LIKE '%' + @Emp_Name + '%') ORDER BY [Emp_Name]">
                <SelectParameters>
                    <asp:ControlParameter ControlID="TextBox1" Name="Emp_Name" PropertyName="Text" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>
        </p>

This is what I came up with so far for my CS code, but it's not changing the numbers to text.

namespace newemployee_new
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                //Cell#2 because your Role appears in that field (Index starts with 0)
                if (e.Row.Cells[2].Text.Equals(99))
                    e.Row.Cells[2].Text = "Dispatch";
                if (e.Row.Cells[2].Text.Equals(48))
                    e.Row.Cells[2].Text = "Fire";
            }
        }
    }
}

Answer 1

User-943250815 posted

You can do it on code behind or in place. In gridview bellow I placed 2 template columns, one to be changed by OnRowDataBound and another setting value in place. I mean using <# if() #>, this one is in VB, so just adapt to C# proper way

<Columns>
  <asp:BoundField DataField="Emp_Name" HeaderText="Employee Name" SortExpression="Emp_Name" />
  <asp:BoundField DataField="Emp_ID" HeaderText="CAD ID #" SortExpression="Emp_ID" />
  <asp:BoundField DataField="JurisdictionID" HeaderText="Jurisdiction" SortExpression="JurisdictionID" />
  <asp:BoundField DataField="Pager" HeaderText="Cellphone #" SortExpression="Pager" />
  <asp:TemplateField HeaderText="Test1">
    <ItemTemplate>
      <asp:Label ID="Label1" runat="server" Text=''></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="test2">
      <ItemTemplate>
        <asp:Label ID="Label2" runat="server" Text='<%# If(Eval("JurisdictionID") = "Dispatch",  If(Eval("JurisdictionID") = "48", "Fire", "Unknow")) %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
</Columns>

OnRowDataBoud use (converted from VB)

if (e.Row.RowType == DataControlRowType.DataRow)
    {
        Label myLabel = e.Row.Cells(4).FindControl("Label1");
        string myJurisdictionID = e.Row.Cells(2).Text;
        switch (myJurisdictionID ?? "")
        {
            case "99":
                {
                    myLabel.Text = "Dispatch";
                    break;
                }

            case "48":
                {
                    myLabel.Text = "Fire";
                    break;
                }

            default:
                {
                    myLabel.Text = "Unknow";
                    break;
                }
        }
    }

Answer 2

User534484856 posted

Is your second set of codes in cs or vb.  I am using cs and it doesn't like Label myLabel = e.Row.Cells(4).FindControl("Label1"); error: TableRow.Cells cannot be used like a method.

Answer 3

User-943250815 posted

In your Gridview replace your existing <Columns> by this one

<Columns>
  <asp:BoundField DataField="Emp_Name" HeaderText="Employee Name" SortExpression="Emp_Name" />
  <asp:BoundField DataField="Emp_ID" HeaderText="CAD ID #" SortExpression="Emp_ID" />
  <asp:BoundField DataField="JurisdictionID" HeaderText="Jurisdiction" SortExpression="JurisdictionID" />
  <asp:BoundField DataField="Pager" HeaderText="Cellphone #" SortExpression="Pager" />
  <asp:TemplateField HeaderText="Test1">
    <ItemTemplate>
      <asp:Label ID="Label1" runat="server" Text=''></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>
</Columns>

For code behind use same already posted. Is in C#.

Answer 4

User534484856 posted

No change in the error.  This is what I currently have

<div class="auto-style1">
                <center>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." GridLines="Vertical" Font-Size="Larger" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
                    <AlternatingRowStyle BackColor="#DCDCDC" />
                    <Columns>
                        <asp:BoundField DataField="Emp_Name" HeaderText="Employee Name" SortExpression="Emp_Name" />
                        <asp:BoundField DataField="Emp_ID" HeaderText="CAD ID #" SortExpression="Emp_ID" />
                        <asp:BoundField DataField="JurisdictionID" HeaderText="Jurisdiction" SortExpression="JurisdictionID" />
                        <asp:BoundField DataField="Pager" HeaderText="Cellphone #" SortExpression="Pager" />
                        <asp:TemplateField HeaderText="Test1">
                            <ItemTemplate>
                                <asp:Label ID="Label1" runat="server" Text=''></asp:Label>
                            </ItemTemplate>
                            </asp:TemplateField>
                    </Columns>
                    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                    <SortedAscendingHeaderStyle BackColor="#0000A9" />
                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                    <SortedDescendingHeaderStyle BackColor="#000065" />
                </asp:GridView>
                </center>
            </div>

code behind

 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                Label myLabel = e.Row.Cells(4).FindControl("Label1");
                string myJurisdictionID = e.Row.Cells(2).Text;
                switch (myJurisdictionID ?? "")
                {
                    case "99":
                        {
                            myLabel.Text = "Dispatch";
                            break;
                        }

                    case "48":
                        {
                            myLabel.Text = "Fire";
                            break;
                        }

                    default:
                        {
                            myLabel.Text = "Unknow";
                            break;
                        }
                }
            }
        }
    }

Still getting errors on TableRow.Cells cannot be used like a method.

Answer 5

User-943250815 posted

Change

Label myLabel = e.Row.Cells(4).FindControl("Label1");

To

Label myLabel = e.Row.Cells[4].FindControl("Label1");

Answer 6

User1535942433 posted

Hi Walleroo01,

Accroding to your codes,there are two methods.

1.Using TemplateField with e.Row.FindControl.

Just like this:

 <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." GridLines="Vertical" Font-Size="Larger" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" OnRowDataBound="GridView1_RowDataBound" DataKeyNames="Emp_ID">
                    <AlternatingRowStyle BackColor="#DCDCDC" />
                    <Columns>
                        <asp:TemplateField HeaderText="Emp_ID">
                            <ItemTemplate>
                                <asp:Label ID="lbID" runat="server" Text='<%# Eval("Emp_ID") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Emp_Name">
                            <ItemTemplate>
                                <asp:Label ID="lbName" runat="server" Text='<%# Eval("Emp_Name") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="JurisdictionID">
                            <ItemTemplate>
                                <asp:Label ID="lbJurisdictionID" runat="server" Text='<%# Eval("JurisdictionID") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Pager">
                            <ItemTemplate>
                                <asp:Label ID="lbPager" runat="server" Text='<%# Eval("Pager") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                    <SortedAscendingHeaderStyle BackColor="#0000A9" />
                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                    <SortedDescendingHeaderStyle BackColor="#000065" />
                </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542 %>" SelectCommand="SELECT * FROM [Personnel]"></asp:SqlDataSource>
        </div>

Code-Behind:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                Label JurisdictionID = (Label)e.Row.FindControl("lbJurisdictionID");
                if (JurisdictionID.Text == "99")
                {
                    JurisdictionID.Text = "Dispatch";
                }
                if (JurisdictionID.Text == "48")
                {
                    JurisdictionID.Text = "Fire";
                }
            }
        }

2.Using BoundField with e.Row.Cells.Just like this:

 <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." GridLines="Vertical" Font-Size="Larger" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" OnRowDataBound="GridView1_RowDataBound" DataKeyNames="Emp_ID">
                    <AlternatingRowStyle BackColor="#DCDCDC" />
                    <Columns>
                        <asp:BoundField DataField="Emp_ID" HeaderText="Emp_ID" SortExpression="Emp_ID" ReadOnly="True" />
                        <asp:BoundField DataField="Emp_Name" HeaderText="Emp_Name" SortExpression="Emp_Name" />
                        <asp:BoundField DataField="JurisdictionID" HeaderText="JurisdictionID" SortExpression="JurisdictionID" />
                        <asp:BoundField DataField="Pager" HeaderText="Pager" SortExpression="Pager" />
                    </Columns>
                    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                    <SortedAscendingHeaderStyle BackColor="#0000A9" />
                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                    <SortedDescendingHeaderStyle BackColor="#000065" />
                </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542 %>" SelectCommand="SELECT * FROM [Personnel]"></asp:SqlDataSource>
        </div>

Code-Behind:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                if (e.Row.Cells[2].Text == "99")
                {
                    e.Row.Cells[2].Text = "Dispatch";
                }
                if (e.Row.Cells[2].Text == "48")
                {
                    e.Row.Cells[2].Text = "Fire";
                }

            }
        }

Result:

Best regards,

Yijing Sun

Answer 7

User534484856 posted

Neither one worked.  This is what I currently have using TemplateField.  I am showing the entire code in case I am missing something.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="newemployee_new.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">

.auto-style1 {
	text-align: center;
}
.auto-style2 {
	font-size: x-large;
}
.auto-style7 {
            text-align: center;
            font-size: xx-large;
            color: #008080;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <p class="auto-style1" style="height: 34px">
                <em><strong><font size="7">Union County On Call Website</font></strong></em></p>
            <p class="auto-style1">
                <em><strong><span class="auto-style2"><a href="file:///E:/exwoncall/on_call_pages.html">On Call Page </a>&nbsp;&nbsp;&nbsp; <a href="file:///E:/exwoncall/default.aspx">Home Page</a>&nbsp;&nbsp;&nbsp;&nbsp; <a href="file:///E:/exwoncall/procedure_manual.html">Dispatch Procedure Page</a></span></strong></em></p>
            <p class="auto-style7">
                <em><strong>CAD Personnel ID And Cellphone Search</strong></em></p>
            <p class="auto-style7">
                <asp:TextBox ID="TextBox1" runat="server" Font-Size="Medium" Height="23px" Width="335px"></asp:TextBox>
&nbsp;
                <asp:Button ID="Button1" runat="server" Font-Size="Medium" Height="28px" Text="Search" Width="113px" />
            </p>
        </div>
            <div class="auto-style1">
                <center>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." GridLines="Vertical" Font-Size="Larger" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
                    <AlternatingRowStyle BackColor="#DCDCDC" />
                   <Columns>
                        <asp:TemplateField HeaderText="CAD ID#">
                            <ItemTemplate>
                                <asp:Label ID="lbID" runat="server" Text='<%# Eval("Emp_ID") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Employee Name">
                            <ItemTemplate>
                                <asp:Label ID="lbName" runat="server" Text='<%# Eval("Emp_Name") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Jurisdiction">
                            <ItemTemplate>
                                <asp:Label ID="lbJurisdictionID" runat="server" Text='<%# Eval("JurisdictionID") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Cell Phone">
                            <ItemTemplate>
                                <asp:Label ID="lbPager" runat="server" Text='<%# Eval("Pager") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                    <SortedAscendingHeaderStyle BackColor="#0000A9" />
                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                    <SortedDescendingHeaderStyle BackColor="#000065" />
                </asp:GridView>
                </center>
            </div>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Reporting_SystemConnectionString1 %>" SelectCommand="SELECT [Emp_ID], [Emp_Name], [JurisdictionID], [Pager] FROM [Personnel] WHERE ([Emp_Name] LIKE '%' + @Emp_Name + '%') ORDER BY [Emp_Name]">
                <SelectParameters>
                    <asp:ControlParameter ControlID="TextBox1" Name="Emp_Name" PropertyName="Text" Type="String" />
                </SelectParameters>
            </asp:SqlDataSource>
        </p>
    </form>
</body>
</html>

Code Behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace newemployee_new
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                Label JurisdictionID = (Label)e.Row.FindControl("lbJurisdictionID");
                if (JurisdictionID.Text == "99")
                {
                    JurisdictionID.Text = "Dispatch";
                }
                if (JurisdictionID.Text == "48")
                {
                    JurisdictionID.Text = "Fire";
                }
            }
        }
    }
}

Answer 8

User534484856 posted

I tried that change and it didn't help.  I attached my entire code below, maybe I messed something else up.

Answer 9

User534484856 posted

Maybe its because its an issue with the Sql table.  The column in question "JurisdictionID" is an "int" Data Type.  It is column #28 in the table.

Answer 10

User-943250815 posted

I reviewed gridview control proposed in my first post, to use just "inline expression", it is easy and faster way to achieve what you looking for, no need code-behind.

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
      <Columns>
        <asp:BoundField DataField="name" HeaderText="Employee Name" SortExpression="name" />
        <asp:BoundField DataField="Id" HeaderText="CAD ID #" SortExpression="Id" />
        <asp:BoundField DataField="JurisdictionID" HeaderText="Jurisdiction" SortExpression="JurisdictionID" />
        <asp:BoundField DataField="Pager" HeaderText="Cellphone #" SortExpression="Pager" />
          <asp:TemplateField HeaderText="test2">
            <ItemTemplate>
              <asp:Label ID="Label2" runat="server" Text='<%# Eval("JurisdictionID").ToString() == "99" ? "Dispatch" :  Eval("JurisdictionID").ToString() == "48" ? "Fire" : "Unknow" %>'></asp:Label>
            </ItemTemplate>
          </asp:TemplateField>
      </Columns>
    </asp:GridView>

Samples proposed should work.
Does not matter if JurisditionID is a string or integer, everything on a page is a string.
What is not working?

Answer 11

User534484856 posted

That did it.  Thanks!

Answer 12

User534484856 posted

One more question if I can,  I have 138 Jurisdictions to add.  Is there a way I can use the Jurisdiction table that has the JurisdictionID numbers with the name of the Jurisdictions instead of adding them all manually?  The table I am using to get the personnel name has the JurisdictionID number and pager numbers in it, but the Jurisdiction Table has the name of the Jurisdictions in it.

Answer 13

User-943250815 posted

I suggest you step back, and look for more info about SQL Server and all related, before go ahead, there are many articles out there that will help you to achieve your needs