Introduction:
In this article we'll see how to create a Column which displays the calculation of more than one field in the database table. The calculation could be addition, multiplication or even filtering rows.
We'll consider the Northwind Database and Products Table.We'll multiply the UnitPrice to UnitsInStock to be displayed in the new column Total.
Solution:
Create a webform with a DataGrid control. We'll add the BoundColumn tag to display column Total.
<asp:Datagrid id= "Datagrid1" AutoGenerateColumns= false style="Z-INDEX: 101; LEFT: 6px; POSITION: absolute; TOP: 14px" runat="server"><Columns>
<asp:BoundColumn DataField="Productid" HeaderText="ProductiD">
</asp:BoundColumn>
<asp:BoundColumn DataField="ProductName" HeaderText="ProductName">
</asp:BoundColumn>
<asp:BoundColumn DataField="Total" HeaderText="Total" ataFormatString="{0:c}">
</asp:BoundColumn>
</Columns>
</asp:DataGrid>
In the code behind write the code which calculates Total =UnitPrice * UnitsInStock. To achieve this we have made use of the Expression Property of the DataColumn.
The Expression Property : Gets or sets the expression used to filter rows, calculate the values in a column, or create an aggregate column. In our case we are using it to create calculated column.
Here goes the code:
Dim myconnection As SqlConnection
Dim myda As SqlDataAdapter
Dim ds As DataSet Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to
initializethepageheremyconnection=NewSqlConnection
"Server=localhost;uid=sa;password=;database=northwind;")
myda = New SqlDataAdapter("Select * from Products", myconnection)
ds = New DataSet()
myda.Fill(ds, "AllTables")
Dim dc As DataColumn
dc = New DataColumn("Total", Type.GetType("System.Double"))
dc.Expression = "UnitPrice * UnitsInStock"
ds.Tables(0).Columns.Add(dc)
DataGrid1.DataSource = ds
DataGrid1.DataBind()
End Sub