Solved

Can't get bound datagrid column to format currency

Posted on 2009-04-07
10
412 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
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: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
 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Regex - Ignoring Spaces 7 26
Close form "before" open 3 40
How to parse the string and split the sub directory names in asp.net 7 41
VB.NET 2008 Publish Error 2 27
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
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…
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…

770 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