Solved

Can't get bound datagrid column to format currency

Posted on 2009-04-07
10
410 Views
Last Modified: 2012-05-06
I have a datagrid with a column bound to a dataset.  the dataset column is a string, and I want to show the data in the grid as currency.   I have tried the {0:c} and formatcurrency(oject,2) and nothing changes it.  Even tried in the databound_event and could not get it to change.  I have heard grid wont format a text field to currency but there as to be a way.

thanks
0
Comment
Question by:mgmhicks
  • 6
  • 4
10 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 24092575
Is it a gridview ? Have you defined the htmlencode property to false ?
0
 

Author Comment

by:mgmhicks
ID: 24092682
original it was a dataview, but I switched to datagrid hoping the problem would go away.  I did change the htmlencode property to false.  That didnt work either.  
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24094518
You have to show some code/html
0
 

Author Comment

by:mgmhicks
ID: 24096690
Here is the html and code-behind


Code BeHind

Private Function GetOpenItems() As DataSet
        Dim ConStr As String
        Dim strPath As String
        Dim myResident As New Leasing.Leasing
        ' Dim myLease As New Leasing.Leasing
        Dim ds As New DataSet("Leasing")
        Dim dt As New DataTable("GetSingleResident")
        'Dim DT2 As DataTable
        Dim r As DataRow
        Dim myStr As String = ""
        ' first need to get information for the GetSingleResident WebService
        ConStr = System.Configuration.ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString
        Dim myConn As SqlConnection = New SqlConnection(ConStr)
        '        Dim myCMD As SqlCommand
        '       myCMD = New SqlCommand
        Try
            strPath = HttpRuntime.AppDomainAppPath
            ds.Tables.Add(dt)
            dt.Columns.Add("firstname")
            dt.Columns.Add("lastname")
            dt.Columns.Add("ssn4")
            dt.Columns.Add("dateofbirth")
            dt.Columns.Add("strict")
            dt.Columns.Add("renewals")

            r = dt.NewRow
            With dt
                r(0) = Session("vFName")
                r(1) = Session("vLName")
                r(2) = Session("vSSNum")
                r(3) = Session("vBirthDay")
                r(4) = "1"
                r(5) = "1"
            End With
            dt.Rows.Add(r)

            Dim MS As New MemoryStream
            ' Dim strXML As String = ""

            ds.WriteXml(MS, XmlWriteMode.IgnoreSchema)
            MS.Position = 0
            'to get the XML as a string we need to use a streamreader

            Dim SR As New StreamReader(MS)
            Dim strXML As String = SR.ReadToEnd()    'now the string of the XML is stored in strXML
            '   Session.Add("GSR", strXML)
            Dim x As New XmlDataDocument
            x.LoadXml(myResident.GetSingleResident("me", "XXXXXX", "production", strXML))

            '  Dim dsNew As New DataSet

            GetOpenItems = New DataSet
            GetOpenItems.ReadXml(New XmlTextReader(New StringReader(x.InnerXml)))
            Return GetOpenItems
        Catch ex As Exception
            MsgBox(ex.Message)
            GetOpenItems = New DataSet
        End Try

    End Function

If GetUserDetails() Then
                Dim ds As New DataSet
                ds = GetOpenItems()
                GridOpenItems.DataSource = ds.Tables(2)
                GridOpenItems.DataBind()
                'FormatCurrency(GridOpenItems.Rows(1).Cells(3).Text, 2)
                FormatCurrency(CDbl(Val(GridOpenItems.Rows(1).Cells(3).Text)), 2)

                lblBalance.Text = FormatCurrency(CDbl(Val(ds.Tables(0).Rows(0).Item(5).ToString)), 2)
                lblMoveIn.Text = FormatDateTime(ds.Tables(0).Rows(0).Item(8).ToString)
                GridOccupants.DataSource = ds.Tables(1)
                GridOccupants.DataBind()
                Dim i As Integer = 0
                For i = 0 To ds.Tables(1).Rows.Count - 1
                Next
            Else

            End If
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Resident_Main.aspx.vb" Inherits="Resident_Main" %>
 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

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

<head>

<script type="text/javascript" src="http://www.theapartmentgallery.com/Webpages/Flash/flashobject.js"></script>

<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />

<title>The Apartment Gallery</title>

<style type="text/css">

a {

	color: #CC0000;

	

}

a {

	color: #CC0000;
 

}

a {

	color: #0000FF;

	text-align: center;

}

body {

	background: #000000 url('../../Newweb/images/Background/bg.jpg') repeat-x fixed left 50%;

	font-family: Verdana, Arial, Helvetica, sans-serif;

	font-size: small;

}

body {

	margin: 0;

	padding: 0;

	border: 0;

}

body {

	background: #000000 url('../../images/Background/bg.jpg') repeat-x fixed left 50%;

	font-family: Verdana, Arial, Helvetica, sans-serif;

	font-size: small;

}

body {

	margin: 0;

	padding: 0;

	border: 0;

}

body {

	background: #000000 url('../../images/Background/bg.jpg') repeat-x fixed left 50%;

	font-family: Verdana, Arial, Helvetica, sans-serif;

	font-size: small;

}

#tech {

	float: none;

	width: 505px;

	margin-left: 0px;

	text-align: center;

	height: 295px;

	margin-right: 0px;

}

#tech1 {

	height: 169px;

	width: 290px;

	float: left;

	text-align: center;

}

.style4 {

	background-color: #FFFFFF;

}

.style2 {

	border: 20px solid #000000;

	background-image: url('');

	background-color: #EAE9CE;

}

.style5 {

	text-align: center;

}

    .style8

    {

        width: 217px;

        font-size: xx-large;

    }

    .style9

    {

        width: 789px;

        height: 406px;

    }

    .style10

    {

    }

    .style11

    {

        width: 174px;

    }

    .style12

    {

        width: 46px;

    }

    .style14

    {

        width: 23px;

    }

</style>

</head>
 

<body style="background-color: #DAD5A7; background-image: url('../../images/Background/bg234567.jpg'); background-repeat: repeat-x; background-position: center; margin-bottom: 50px; margin-top: 20px">
 
 

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

<table style="width: 845px" class="style4" cellspacing="0" cellpadding="7" align="center">

	<tr>

		<td>
 
 

<table style="width: 840px; height: 100px; background-attachment: inherit; background-repeat: repeat-y;" align="center" cellspacing="0" cellpadding="0" class="style2">

	<tbody class="style5">

	<tr>

		<th style="text-align: center; background-color: #000000; white-space: nowrap;">

		<p>

		<img src="../../images/Top%20of%20the%20Page%20Banner/APTGALL-header-black2.jpg" width="549" height="73" />&nbsp;</p>

		</th>

	</tr>

	<tr>

	<td><div id="macro-style_id" ></div>

<script type="text/javascript">

var fo = new FlashObject("http://www.theapartmentgallery.com/Webpages/Flash/Menu3.swf","macro-style","840","65","6","#928047");

    fo.addParam("menu","false");

    fo.addParam("quality","best");

    fo.addParam("salign","LT");

    fo.addParam("scale","noscale");

    fo.addParam("wmode","window");

    fo.write("macro-style_id");

</script>

		<table style="width: 82%" cellspacing="0" cellpadding="10">

			<tr>

		<td style="text-align: left">
 

<script type="text/javascript">

var fo = new FlashObject("http://www.theapartmentgallery.com/Webpages/Flash/Menu3.swf","macro-style","840","65","6","#928047");

    fo.addParam("menu","false");

    fo.addParam("quality","best");

    fo.addParam("salign","LT");

    fo.addParam("scale","noscale");

    fo.addParam("wmode","window");

    fo.write("macro-style_id");

</script>

		<p style="font-weight: 700; text-decoration: none; margin-top: 0px; margin-bottom: 0px" 

                class="style5">
 

            <span class="style8" lang="en-us">Resident Tools</span></p>

<table cellspacing="0" cellpadding="10" class="style9">

	<tr>

		<td class="style10">

            &nbsp;</td>

		<td class="style11">

            &nbsp;</td>

		<td class="style12">&nbsp;</td>

		<td align="right">
 

            

            <asp:LoginStatus ID="LoginStatus1" runat="server" Font-Size="Smaller" />

            </td>

	</tr>

	<tr  style="border-style: double">

		<td colspan="4" style="border-style: solid">

            <asp:Label ID="Label1" runat="server" Text="User Name:" 

                style="font-weight: 700"></asp:Label>

            <span lang="en-us">&nbsp;&nbsp; </span>

            <asp:LoginName ID="LoginName1" runat="server" />

            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

            <asp:Label ID="Label3" runat="server" Text="Move In Date:" 

                style="font-weight: 700"></asp:Label>

            &nbsp;&nbsp;

            <asp:Label ID="lblMoveIn" runat="server" Text="Movein"></asp:Label>

            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span>

            <asp:Label ID="Label2" runat="server" Text="Balance:" style="font-weight: 700"></asp:Label>

            &nbsp;&nbsp;

            <asp:Label ID="lblBalance" runat="server" Text="Bal"></asp:Label>

            &nbsp;</td>

	</tr>

	<tr>

		<td colspan="2" align="left" valign="top">

            <asp:GridView ID="GridOccupants" runat="server" AutoGenerateColumns="False" 

                BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" 

                CellPadding="4" ForeColor="Black" GridLines="Vertical" Height="16px" 

                Width="327px">

                <FooterStyle BackColor="#CCCC99" />

                <RowStyle BackColor="#F7F7DE" />

                <Columns>

                    <asp:BoundField DataField="ResiID" HeaderText="Res. ID" />

                    <asp:BoundField DataField="OccuFirstName" HeaderText="First Name" />

                    <asp:BoundField DataField="OccuLastName" HeaderText="Last Name" />

                    <asp:BoundField DataField="ResponsibleFlag" HeaderText="Flag" />

                </Columns>

                <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

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

                <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

                <AlternatingRowStyle BackColor="White" />

            </asp:GridView>

        </td>

		<td colspan="2" align="center" valign="top">

            <asp:GridView ID="GridOpenItems" runat="server" BackColor="White" 

                BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="3px" CellPadding="4" 

                GridLines="Vertical" Height="61px" Width="420px" 

                AutoGenerateColumns="False" ForeColor="Black" Enabled="False">

                <FooterStyle BackColor="#CCCC99" />

                <RowStyle BackColor="#F7F7DE" />

                <Columns>

                    <asp:BoundField HeaderText="Inc Code" DataField="IncCode" >

                        <ItemStyle Wrap="False" />

                    </asp:BoundField>

                    <asp:BoundField DataField="IncCodeDesc" HeaderText="Descrip." >

                        <ItemStyle Wrap="False" />

                    </asp:BoundField>

                    <asp:BoundField DataField="ItemDueDate" HeaderText="Due Date" 

                        DataFormatString="{0:d}" >

                        <ItemStyle Wrap="False" />

                    </asp:BoundField>

                    <asp:BoundField DataField="BalDue" HeaderText="Balance Due" 

                        DataFormatString="{0:c}" HtmlEncode="False" >

                        <ItemStyle Wrap="False" />

                    </asp:BoundField>

                </Columns>

                <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />

                <EmptyDataTemplate>

                    <span lang="en-us">this is a test</span>

                </EmptyDataTemplate>

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

                <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />

                <AlternatingRowStyle BackColor="White" />

            </asp:GridView>

        </td>

	</tr>

	<tr>

		<td colspan="2" align="left" valign="top">

            &nbsp;</td>

		<td colspan="2" align="center" valign="top">

            &nbsp;</td>

Open in new window

0
 

Author Comment

by:mgmhicks
ID: 24096701
Its the openitems grid that is the problem.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 48

Accepted Solution

by:
jpaulino earned 500 total points
ID: 24097484
Try to change the column type to this:

dt.Columns.Add("renewals", GetType(Decimal))
0
 

Author Comment

by:mgmhicks
ID: 24097848
jpaulino,

Will this add another column, or do I want to change the column "Baldue" to this type
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24098524
That was an example and you should apply to your currency columns (all). For datatables it's always better to define the column type (datetime, integer, decimal, etc)
0
 

Author Comment

by:mgmhicks
ID: 24153976
Here is what I ended up doing because apparently once a dataset is created you can not change the column datatype.  So the following creates a datatable from the dataset, then changes the column in the datatable, then  imports the dataset.tables.rows into the datatable.  Then bind the datatable to the grid.  

 Dim ds As New DataSet
                Dim dt As DataTable
                Dim mdatarow As DataRow
                Dim t As Integer


                ds = GetOpenItems()
                dt = ds.Tables(2).Clone
                dt.Columns(6).DataType = System.Type.GetType("System.Double")
                For t = 0 To ds.Tables(2).Rows.Count - 1
                    mdatarow = ds.Tables(2).Rows(t)
                    dt.ImportRow(mdatarow)
                Next


                '       GridOpenItems.DataSource = ds.Tables(2)
                GridOpenItems.DataSource = dt
                GridOpenItems.DataBind()
0
 

Author Closing Comment

by:mgmhicks
ID: 31567798
Really this got me on the right track.  You cant change the dataset, but you can change the datatable.  thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Whatever be the reason, if you are working on web development side,  you will need day-today validation codes like email validation, date validation , IP address validation, phone validation on any of the edit page or say at the time of registration…
As most anyone who uses or has come across them can attest to, regular expressions (regex) are a complicated bit of magic. Packed so succinctly within their cryptic syntax lies a great deal of power. It's not the "take over the world" kind of power,…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

708 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

12 Experts available now in Live!

Get 1:1 Help Now