Main Menu
Knowledge Base
Product Registration
Log an Incident
Request a Feature
Search Incidents/Bug Reports


Search KB

Please note: In an effort to better serve you, we are in the process of restructuring DevCenter. In the process, we have moved many items that you may be used to finding in DevCenter over to the Main Site. If you are having trouble locating something, please try looking at the following places:

Knowledge Base Article: KB03448

HOWTO:Cell Formatting


The information in this article applies to:
UltraWebGrid (v1.0.6007)
  Article Created: 
3/7/2003

Last Updated:
9/18/2006

Article Type
How To
  
Page Options
Average Rating:
1 out of 10

Rate this page
Print this page
E-mail this page
Add to Favorites

Summary

The WebGrid uses the standard framework format method when formatting data into a column, thus you can use format strings such as c for currency and d for date, but be aware that if your grid is editable by the user that they may not understand these built-in formats. One way to avoid confusion is to use the long format.
If the built-in format property of the cell does not meet the needs of the application, take a look at Column Templates. This allows you to place any web control in the cell, which provides almost unlimited formatting and editing capabilities.

Additional Information

Questions
How do I set the format of a cell to display currency and date values?
Solution
Use the Format property of the column to tell WebGrid how to format cell values.

Step-By-Step Example

This project retrieves a joined dataset from the Northwind database in an SQL Server and displays the data with the currency and date columns formatted.

Perform the following steps to create this sample project.
Start a new Web Forms project.
From Toolbox, Data drag an OleDbDataAdapter to the WebForm1.aspx designer and complete the Data Adapter Configuration Wizard as follows:
On Choose Your Data Connection: select a known SQL Server data connection or click New Connection and complete the Data Link Properties dialog to create a connection to a Northwind database on SQL Server.


Note This sample project will not run using an Access database. The Access data engine cannot handle the SQL command used.

Choose a Query Type: User SQL statements
Generate the SQL Statements: Click Query Builder, Add Orders and click Close, click (All Columns) and click OK.
Click Finish.
From the main Menu select Data, Generate Dataset and click OK.
In Solution Explorer, Double-click DataSet1.xsd and select the XML tab. Change the XML to read as follows:
Note The required changes are to remove the key constraint section and change the xs.element section. The remainder should be the same. You may wish to cut and paste the XML code below.

<?xml version="1.0" standalone="yes" ?>
<xs:schema id="DataSet1" targetNamespace="http://www.tempuri.org/DataSet1.xsd" xmlns:mstns="http://www.tempuri.org/DataSet1.xsd" xmlns="http://www.tempuri.org/DataSet1.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
  <xs:element name="DataSet1" msdata:IsDataSet="true">
    <xs:complexType>
      <xs:choice maxOccurs="unbounded">
        <xs:element name="Orders">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="CustomerID" type="xs:string" minOccurs="0" />
              <xs:element name="OrderID" type="xs:int" minOccurs="0" />
              <xs:element name="OrderDate" type="xs:dateTime" minOccurs="0" />
              <xs:element name="Amount" type="xs:decimal" minOccurs="0" />
              <xs:element name="CompanyName" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

From the menu select Build, Rebuild Solution go get Visual Studio synchronized after making changes to DataSet1.xsd.
In Solution Explorer, double-click WebForm1.aspx and the designer should display.
From the Toolbox, drag an UltraWebGrid to the WebForm1.aspx designer, position and size as desired.
In the Solution Explorer expand the References node. Look for a reference to Infragistics.WebUI.Shared. If the reference is not included, right-click the References node, select Add Reference, and add the reference from the .NET tab.
Click the Infragistics.WebUI.Shared reference and set the following properties:

CopyLocal = True
Click on the WebGrid and set the following properties:

DataSource = DataSet11
DataMember = Customers
Notice the column names in the grid reflect the columns in DataSet1.xsd.
Add the following code to the page load event to fill the dataset and bind it to the WebGrid:

In Visual Basic:

If Me.IsPostBack = False Then
    ' build SQL statement to retrieve dataset
    Dim sbSQL As New System.Text.StringBuilder()
    sbSQL.Append(" SELECT o.CustomerID, c.CompanyName, o.OrderID, o.OrderDate,")
    sbSQL.Append(" cast(sum(d.UnitPrice * d.quantity * (1.0 - d.Discount))")
    sbSQL.Append(" as decimal (10,2)) as Amount FROM Orders as o")
    sbSQL.Append(vbCrLf)
    sbSQL.Append(" right outer join [Order Details] as d on o.OrderID = d.OrderID")
    sbSQL.Append(vbCrLf)
    sbSQL.Append(" right outer join Customers as c on o.CustomerID = c.CustomerID")
    sbSQL.Append(vbCrLf)
    sbSQL.Append(" WHERE o.CustomerID IS NOT NULL")
    sbSQL.Append(vbCrLf)
    sbSQL.Append(" GROUP BY o.CustomerID, c.CompanyName, o.OrderID, o.OrderDate")
    
    ' apply the new sql statement to the select command
    Me.OleDbSelectCommand1.CommandText = sbSQL.ToString
    
    ' fill the dataset and bind it to the grid
    Me.OleDbDataAdapter1.Fill(DataSet11)
    Me.UltraWebGrid1.DataBind()

End If

Run the project.


Observe the OrderDate shows both the date and time and would be friendlier if it just displayed the date. Also, the amount column would be much more usable if it was formatted as a currency column and right aligned.
To make these changes, Click on the WebGrid on the WebForm1.aspx designer and:
From the Properties dialog select the Columns Collection Editor.
Click on the OrderDate column and set the Format property to date or (MM/dd/yyyy). You can use any of the built-in format codes like d, but if you are allowing for edits in the grid you should use the longer form since the client-side handles this format well.
Click on the Amount column and set the Format property to currency or ($ ###,###,##0.00), and the CellStyle.HorizontalAlign property to Right.
Run the project.


Review
This project shows how to connect to an SQL Server hosted Northwind database, extract and summarize data from the Orders, Order Details and Customers table, format the date and amount columns and display the table in a WebGrid. Notice the strongly typed dataset which reflects the results of the multiple outer-joined SQL command.


How would you rate the quality of this content?
Poor -----------------------------------------> Outstanding

Tell us why you rated the content this way. (optional)