[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ASP.net Formating Column data based on Column heading

Posted on 2006-05-11
9
Medium Priority
?
294 Views
Last Modified: 2010-04-07
Hello I would like to know how to format the data in a column based on it's heading.
For Example if the heading is Population I would like it formatted using "0:#,###" this format.

I am used to classic ASP and keeping running into not allowed errors in asp.net when I try to format the data grid dynamically.

Thank YOu
Mark D.
0
Comment
Question by:Mark400
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 16661618
This comes up all the time. I think (if i can remember) you have to set the HtmlEncode="False" in your data grid.
0
 
LVL 14

Expert Comment

by:CtrlAltDl
ID: 16661649
Yea, I looked at a GridView and the boundfield looks like this:
    <asp:BoundField HtmlEncode="false"
then your formatting will work.  I think this is a bug, but I think MS thinks this is by design for some reason.
0
 
LVL 2

Expert Comment

by:koolkraft
ID: 16661750
Hi

You can format your output in a few ways the first would be
<asp:BoundField DataField="FieldName"  HtmlEncode="false" DataFormatString="{0:#,###}" />

Or when you use templated columns you woud write something like this : <%# Eval("FieldName","0:#,###") %>
There is no need to set the html encoding on a templated field

if you want to do some conditional formatting you can do it in the onitemdatabound or rowdatabound event of the grid.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Mark400
ID: 16670733
Thanks but I still don't seem to be able to get it to work dynamically I have no used to ASP.net before.

The datagrid doesn't seem to allow me to use any variables at all unless I am doing something wrong.
Example:

Dim formatToUse as String
formatToUse=="{0:#,###}"

 <asp:BoundColumn DataField="<%=Request.Form('txtColumnToBind')%>"

HeaderText="<%=Trim(Request.Form('txtHeaderToDisplay'))/>"

DataFormatString="<%=FormatToUSe%>"

Thanks
Mark
 
0
 
LVL 2

Accepted Solution

by:
koolkraft earned 1500 total points
ID: 16670869
What is exactly that you want to do ? because it doesn't look like you're databinding

This is not ASP this is asp.net lot's of the stuff get's done for you.

Could you please send the code for your page and tell me what you want the page to do ?
Just an explanation would be already a great help

I'm not sure but i don't think <%= ...... %> epressions are allowed in databound columns.

You could try to make the variable formatToUse a protected variable and then you can access it with the syntax <%# Eval(formatToUse) %>
The datafield needs to be bound to a column from a list object ie. datatable but I'm very sure you can't put it there with a response.write expression (<%=....%>)

A databind expression is formatted <%# Eval("fieldname") %> in asp.net 2.0 in 1.1 it's <%# Databinder.Eval(Container.DataItem,"fieldname") %>

If this didn't clear up some stuff please send me your code and an explanation of what you want the page to do. Plus in which version of asp.net you're working.
If it's not asp.net 2.0 is there a reason for it ? Because you have great free tools for developping good websites with asp.net 2.0 unless you're developping for an enterprise application those should be sufficient

Cheers,
Ivan
0
 
LVL 1

Author Comment

by:Mark400
ID: 16679480
Ok Ivan here goes

What I want to do is have excel files uploaded to the web server and have the columns properly formated. Right now it works fine but there is no comma after the 3rd digit in large numbers, percentages are displayed as .32345563 instead of 32%  plus other formatting problems such as column headings displayed as F2 when it is supposed to be the year 2001 as it is in the excel workbook.  All I want is to have the same formatting as is in the excel workbook.

The code is working but I can't seem to find an easy way to format the data properly. I thought using a worksheet containing column headings mapped to the proper format would work as format variables that I could use to properly display the data. I could grab these variables and use a conditional statement to properly format all my datagrids IE as below  But I would do this with a function that would lookup the DataformatString by looping through an array which gets it value from an excel range that contains the headings mapped to the column's formatting the code below is just to give you the idea
****
 IF mycolumnname.DataField = "population" then
  mycolumnname.DataFormatString = "{0:#,#.}"
End IF


Not knowing much about dot net I don't if I am missing something very simple. And not sure how to get this to work.

.  Since the column headings will be changing with each new column upload I cannot hard code in the formats.

There may be an easy way to do this and knowing almost nothing about .net it has been very difficult for me to find an easy way to do this. By the way I already tried formating the excel sheet differently and so far it has not given me the results I want in the datagrid.

THanks
Mark D.

There are many lines of code and it depends on an exel file to get the data so I will just post part of the code.


*****

<%@ Page Language="VB" Debug="true" %>


<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">


      Sub Page_Load(sender as Object, e as EventArgs)

            ' Only fill the DataGrid with data from the Excel file on first run
            If Not Page.IsPostBack Then
                  Dim myConnectionString As String
                  Dim myConnection       As OleDbConnection
                  Dim myCommand          As OleDbCommand
                  Dim myDataReader       As OleDbDataReader
                  
                  
            '*** added number 2
                  Dim myCommand2          As OleDbCommand
                  Dim myDataReader2       As OleDbDataReader
                  

                  ' Set the connection string.  Data file is "workbook1.xls" in the
                  ' current directory.
            myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
             & "Data Source=" & Server.MapPath("workbook1.xls") & ";" _
             & "Extended Properties=Excel 8.0;"

                  ' Create the connection object using the connection string above.
                  myConnection = New OleDbConnection(myConnectionString)

                  ' Create a Command that selects the data from the Excel file.
                  ' Note that City_Population is a named range in the Excel file
                  myCommand = New OleDbCommand("SELECT * FROM City_Population;", myConnection)
                  
            

                  ' Open the connection
                  myConnection.Open()

                  ' Use the ExecuteReader method of the Command object to execute
                  ' our query and return the results via a DataReader
                  
                  myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
                  
            

                  ' Specify the DataReader as the source of the data for our DataGrid
                  ' and then DataBind to display the data in our DataGrid.
                  dgExcelData.DataSource = myDataReader
                  dgExcelData.DataBind()
                  
                              ' Close our DataReader and Connection
                  myDataReader.Close()
                  myConnection.Close()
                  
                        '**** add number 2
                  myCommand2 = New OleDbCommand("SELECT * FROM City2_Population;", myConnection)
                  ' Open the connection again
                  myConnection.Open()
                  
                  ' Again Use the ExecuteReader method of the Command object to execute
                  ' the query and return the results via a DataReader

                  
                  '**** added number 2
                  '**** added number 2
                  myDataReader2 = myCommand2.ExecuteReader(CommandBehavior.CloseConnection)
                  dgExcelData2.DataSource = myDataReader      
                  dgExcelData2.DataBind()
                  '**** added number 2
                  myDataReader2.Close()
                  myConnection.Close()      
                  
            End If
      End Sub
      
' Create a sub to add roll over effects on each row
      Sub dgExcelData2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs)
      If e.Item.ItemType = ListItemType.Item Then
      e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor='Silver'")            
      e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='#eeeeee'")
      Else if e.Item.ItemType = ListItemType.AlternatingItem then
      '---------------------------------------------------        
      ' Add the OnMouseOver and OnMouseOut method to the Row of DataGrid        
      '---------------------------------------------------        
            e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor='Silver'")
      e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='white'")
                  End If  
                  
'The following is one of my many attempts to format the colunms data but it didn't seem to work except for the align right part.        
Dim n As Integer

 For n = 1 To e.Item.Cells.Count - 1
                e.Item.Cells(n).Attributes.Add("align", "right")

            e.Item.Cells(n).Text() = Format("0:#,###", e.Item.Cells(n).Text)
             
        Next
     
                   End Sub
                  
                    Sub dgExcelData_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs)
      If e.Item.ItemType = ListItemType.Item Then
      e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor='Silver'")            
      e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='#eeeeee'")
      
      
      Else if e.Item.ItemType = ListItemType.AlternatingItem then
      '---------------------------------------------------        
      ' Add the OnMouseOver and OnMouseOut method to the Row of DataGrid        
      '---------------------------------------------------        
      e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor='Silver'")            
      e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='white'")
                  End If              
   
                   End Sub    
                  
</script>
0
 
LVL 2

Expert Comment

by:koolkraft
ID: 16679580
I don't know how to write it in VB. But i can read it that's no problem I hope that if I do have to give you code you can read C# well enough to translate my few lines to VB.  I didn't write it in VS or compile it so there might be spelling mistakes but it should give you an idea

have you tried in your form designer. <%# Databinder.Eval(Container.DataItem,"fieldname","#,##0") %>  ?

you're definitely on the right track, it has to be done in the itemdatabound event in the code behind if you want to do it from code.
I believe you can access the text in the datagrid column like this : e.Item.Cells(n).Controls(0)  The control you get then is a label control if you're not in edit mode and that gives you access to the value.

void dgExcelData_ItemDataBound(Object sender , System.Web.UI.WebControls.DataGridItemEventArgs e ){
    if(e.Item.ItemType==ListItemType.Item||e.Item.ItemType==ListItemType.AlternatingItem){
         for(int i=0;i<e.Item.Cells.Count;i++){
                 Label lbl = e.Item.Cells[i].Controls[0] as Label;
                 lbl.Text = string.Format("{0:#,##0}",lbl.Text);
       }
   }
}

I hope this helps you out
0
 
LVL 2

Expert Comment

by:koolkraft
ID: 16679582
<%# Databinder.Eval(Container.DataItem,"fieldname","#,##0") %> should be <%# Databinder.Eval(Container.DataItem,"fieldname","0:#,##0") %>
I think.
0
 
LVL 1

Author Comment

by:Mark400
ID: 16711012
Thanks Everyone for your ideas. You got me on the right track. I have leaned that ASP.net and ASP are very different. I was very fustrated not even being able to use variables inside a datagrid seems crazy to me. In any case I have it all working now not exactly as I hoped but the most important parts are working.

It was very important to have the ablilty to change the excel column heading and then just upload the new workbook without having to change any code in the web app. and that is working fine.

To sum up I the columns are formated and created dynamically the column names can be changed without making any code changes.

I would appreciate any ideas on making the code a little more effecient.


I am going to award the points to Ivan as he was of the most help.


Thank you for all your ideas.


Below is a portion of the most relevent code that is working.

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">

    Public FourColumnNamearray(4) As String
      Sub Page_Load(sender as Object, e as EventArgs)

            ' Only fill the DataGrid with data from the Excel file on first run
            If Not Page.IsPostBack Then
                  Dim myConnectionString As String
                  Dim myConnection       As OleDbConnection
            Dim myCommand As OleDbCommand
       
            Dim myDataReader As OleDbDataReader
                  
                  myConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                        & "Data Source=" & Server.MapPath("CommunityProfiles.xls") & ";" _
                        & "Extended Properties=Excel 8.0;"

            myConnection = New OleDbConnection(myConnectionString)
           
            ' ******************** END OF CREATING CONNECTION ***********************
           
            '***********   Start Of Getting Column Headings For Grid #1     *******
           
            Dim intField As Integer             ' Current field in row.
            Dim intColumn As Integer            ' Current column name.
            Dim blnColumns As Boolean = False   ' Whether column have names
            Dim objReader As OleDbDataReader 'This is the reader to read the column headings
           
            ' Open the connection
            myConnection.Open()
            myCommand = New OleDbCommand("SELECT * FROM Windsor_Population;", myConnection)
            objReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
           
           
            With objReader
                Do While .Read = True

                    For intField = 0 To .FieldCount - 1

                        ' List the column names first.
                        If blnColumns = False Then

                            For intColumn = 0 To .FieldCount - 1

                                If intColumn = .FieldCount - 1 Then
                                    'Response.Write(.GetName(intColumn))
                                    FourColumnNamearray(intColumn) = .GetName(intColumn)
                                   
                                Else
                                    'Response.Write(.GetName(intColumn))
                                    FourColumnNamearray(intColumn) = .GetName(intColumn)
                                End If

                            Next intColumn

                            ' Only list column names once.
                            blnColumns = True

                        End If

                    Next intField

                Loop

            End With

            objReader.Close()
            myConnection.Close()
                 
            '********************** End of Getting Table One Column Name ********
           
           
           
            '*********************** START OF GRID #1 *********************************

                  myCommand = New OleDbCommand("SELECT * FROM Windsor_Population;", myConnection)
                  
                  myConnection.Open()

            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
           
            '********* Add First Column
            Dim mycolumnname0 As New BoundColumn()
       
            mycolumnname0.DataField = FourColumnNamearray(0)
       
            mycolumnname0.HeaderText = FourColumnNamearray(0)
       
            mycolumnname0.HeaderStyle.HorizontalAlign = HorizontalAlign.Left
       
            mycolumnname0.ItemStyle.HorizontalAlign = HorizontalAlign.Left
            'mycolumnname0.DataFormatString = "{0:##.}"
       
            dgExcelData1.Columns.Add(mycolumnname0)


'Thanks to everyone for your suggestions.
'Mark D.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Loops Section Overview
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month20 days, 3 hours left to enroll

872 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