Format Currency without currency symbol

Posted: December 28, 2007 | Author: | Filed under: ASP.NET 2 | Leave a comment

Here is a way I came up with in a TemplateField: 

(my machine’s culture is US, so may need to change the Replace currency symbol if your culture is different)

<asp:TemplateField HeaderText="amount" SortExpression="amount">

<ItemTemplate>

<asp:Label ID="Label1" runat="server" Text=’<%# Convert.ToString(Eval("amount","{0:c4}")).Replace("$", "") %>‘ ></asp:Label>

</ItemTemplate>

</asp:TemplateField>

Add Another Footer to GridView

Posted: December 28, 2007 | Author: | Filed under: ASP.NET 2 | Leave a comment

I came across this link to find out another approach to add another Footer to the GridView(or not show Footer)

http://hi.baidu.com/myaspdotnet/blog/item/715437ddde7b73dd8c1029d3.html

 

protected void GridView1_OnPreRender(object sender, EventArgs e)
    {
        if (GridView1.Controls.Count > 0 && GridView1.Controls[0].Controls.Count > 1)
        {
 
// //public GridViewRow(
//    int rowIndex,
//    int dataItemIndex,
//    DataControlRowType rowType,
//    DataControlRowState rowState
//)


 

            GridViewRow gr = new GridViewRow(-1, -1, DataControlRowType.Footer, DataControlRowState.Normal);

            TableCell cell = new TableCell();

            cell.Text = "&lt;hr style='color:yellow; height: 5px' />";

            cell.Attributes["colspan"] = GridView1.Columns.Count.ToString(); //merge columns

            gr.Controls.Add(cell);

            //gr.Cells[0].HorizontalAlign = HorizontalAlign.Center;

            GridView1.Controls[0].Controls.AddAt(GridView1.Controls[0].Controls.Count - 1, gr);

        }

    } 

Merge columns in GridView footer and add a line

Posted: December 27, 2007 | Author: | Filed under: ASP.NET 2 | Leave a comment

Merge columns in the footer is not that straightforward. Here is another solution other than through CSS to put an <hr> in the footer:

<asp:GridView ID="GridView1" ShowFooter="true"  OnRowDataBound="GridView1_OnRowDataBound" …>

 

protected void GridView1_OnRowDataBound(object sender, System.Web.UI.WebControls.GridViewRowEventArgs e)
    {

        if (e.Row.RowType == DataControlRowType.Footer)
        {
            //remove all cells but one, so GridView1.Columns.Count-1
            for (int i = 0; i < GridView1.Columns.Count-1; i++)
            {
                e.Row.Cells.RemoveAt(0);
            }
           
           e.Row.Cells[0].ColumnSpan = GridView1.Columns.Count;
           e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Center;
           e.Row.Cells[0].Text = " <hr style='color:yellow; height: 5px' />";

        }
        
    }
 CSS way:

Add a bottom border style to your gridview(you may not need your footer):

  <asp:GridView ID="GridView1" CssClass="bottomBorder".......................>
<
head runat="server">
<title>Your Page</title>

 <style type="text/css">
.bottomBorder {border-bottom: black medium solid; }</style>

</head>

 

Selective Sorting with AutoGeneratedColumns in GridView

Posted: December 21, 2007 | Author: | Filed under: ASP.NET 2 | Leave a comment

One user from ASP.NET forum was asking how to do this. (http://forums.asp.net/t/1191176.aspx)

Here is the solution for that. 

protected void GridView1_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {    if (e.Row.RowType == DataControlRowType.Header)

{
    Control ctl0 = e.Row.Controls[2];// column 3
    Control ctl = ctl0.Controls[0]; //first control in column 3

    if (ctl is LinkButton)
    {
     ctl0.Controls.Remove(ctl);
     ctl0.Controls.AddAt(0, new LiteralControl((ctl as LinkButton).Text));
    }
}

} 

GridView1:

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" DataKeyNames="id" AllowSorting="true"
            DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_OnRowDataBound"></asp:GridView> 

Update from a DropDownList in ItemTemplate of GridView

Posted: December 20, 2007 | Author: | Filed under: ASP.NET 2 | Leave a comment 
protected void changeStatus(object sender, EventArgs e)
    {
        GridViewRow row = ((Control)sender).NamingContainer as GridViewRow;

        if (row != null)
        {
          
            DataKey keys = GridView1.DataKeys[row.RowIndex];
            SqlDataSource1.UpdateParameters.Add("SubID", keys.Values[0].ToString());

            DropDownList myddlStatus = (DropDownList)row.FindControl("ddlStatus");           
            SqlDataSource1.UpdateParameters.Add("Status", myddlStatus.SelectedValue.ToString());

            SqlDataSource1.Update();          

        }
 


 This is the markup of the GridView1:


 


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SubID" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="SubID" HeaderText="SubID" ReadOnly="True" SortExpression="SubID" />
                <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
                <asp:BoundField DataField="SubName" HeaderText="SubName" SortExpression="SubName" />
                <asp:TemplateField HeaderText="Status" >                   
                    <ItemTemplate>
                   <asp:DropDownList ID="ddlStatus" runat="server" Selectedvalue='<%#Bind("Status") %>' AutoPostBack="true" OnSelectedIndexChanged="changeStatus">

<asp:ListItem Text="Make Active" Value="Make Active"></asp:ListItem> 
<asp:ListItem Text="Permanently Delete" Value="Permanently Delete"></asp:ListItem>
<asp:ListItem Text="Archive" Value="Archive"></asp:ListItem>

</asp:DropDownList>

                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
                      SelectCommand="SELECT [SubID], [Status], [SubName] FROM [CatSubTable2]" 
                      UpdateCommand="UPDATE [CatSubTable2] SET [Status] = @Status WHERE [SubID] = @SubID">


</asp:SqlDataSource>



  

A Sample for TextBox’s OnTextChanged event fired from Repeater for subTotal and Total

Posted: December 18, 2007 | Author: | Filed under: ASP.NET 2 | Leave a comment

This sample includes how to access control in Footertemple of Repeater and hot to get item totals.

Here is one way to access the label in the footer:

Label lblTotal = (Label)Repeater1.Controls[Repeater1.Controls.Count-1].FindControl("lblTotal");

 

protected void PriceTextBox_OnTextChanged(object sender, EventArgs e)

{

TextBox tb0 = ((TextBox)(sender));

RepeaterItem rp1 = ((RepeaterItem)(tb0.NamingContainer));

TextBox tb1 = (TextBox)rp1.FindControl("priceTextBox");

Label lbl2 = (Label)rp1.FindControl("lblprice2");

Label lbl3 = (Label)rp1.FindControl("lblprice3");

Label lbl4 = (Label)rp1.FindControl("lblSubtotal");

lbl2.Text =

Convert.ToString(Convert.ToDouble(tb1.Text) * 0.2);

lbl3.Text = Convert.ToString(Convert.ToDouble(tb1.Text) * 1.2);

lbl4.Text =

Convert.ToString(Convert.ToDouble(tb1.Text) + Convert.ToDouble(lbl2.Text) + Convert.ToDouble(lbl3.Text));

 

gTotal = 0;

foreach (RepeaterItem Item in Repeater1.Items)

{

double subtotal = Convert.ToDouble(((Label)Item.FindControl("lblSubtotal")).Text);

gTotal += subtotal;

}

Label lblTotal = (Label)Repeater1.Controls[Repeater1.Controls.Count-1].FindControl("lblTotal");

lblTotal.Text = gTotal.ToString();  

}

double gTotal = 0.0;

protected void Repeater1_ItemDataBound(object sender, System.Web.UI.WebControls.RepeaterItemEventArgs e)

{

 

RepeaterItem item = e.Item;

if ((item.ItemType == ListItemType.Item) ||

(item.ItemType == ListItemType.AlternatingItem))

{

Label tbsubTotal = (Label)e.Item.FindControl("lblSubtotal");

 

DataRowView drv = (DataRowView)item.DataItem;

double p1;

double p2;

double p3;

if (drv["price"] == DBNull.Value)

{

p1 = 0;

}

else

{

p1 =

Convert.ToDouble(drv["price"]);

}

if (drv["price2"] == DBNull.Value)

{

p2 = 0;

}

else

{

p2 =

Convert.ToDouble(drv["price2"]);

}

if (drv["price3"] == DBNull.Value)

{

p3 = 0;

}

else

{

p3 =

Convert.ToDouble(drv["price3"]);

}

double pTotal = p1 + p2 + p3;

 

 

tbsubTotal.Text = pTotal.ToString();

 

double subtotal = Convert.ToDouble(((Label)e.Item.FindControl("lblSubtotal")).Text);

gTotal += subtotal;

 

}

if (item.ItemType == ListItemType.Footer)

{

Label tbTotal = (Label)e.Item.FindControl("lblTotal");

tbTotal.Text = gTotal.ToString();

}

 }

<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1" OnItemDataBound="Repeater1_ItemDataBound" >

<ItemTemplate>

<table><tr><td>myindex1:<%# Container.ItemIndex %> </td>

<td><asp:Label ID="IDLabel1" runat="server" Text=’<%# Eval("ID") %>‘></asp:Label></td>

<td> price:

<asp:TextBox ID="priceTextBox" runat="server" Text=’<%# Bind("price") %>‘ AutoPostBack="true" OnTextChanged="PriceTextBox_OnTextChanged"></asp:TextBox>

</td>

<td> price2:

<asp:Label ID="lblprice2" runat="server" Text=’<%# Bind("price2") %>‘ />

</td>

<td> price3:

<asp:Label ID="lblprice3" runat="server" Text=’<%# Bind("price3") %>‘ />

</td>

<td> >>>: <asp:Label ID="lblSubtotal" runat="server" /> </td>

</tr>

</

ItemTemplate>

<

FooterTemplate>

<tr><td colspan="6" align="right">Total:<asp:Label ID="lblTotal" runat="server" /></td></tr>

</table>

 

</

FooterTemplate>

</asp:Repeater>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"

SelectCommand="SELECT [ID],[price], [price2], [price3] FROM [money1]" >

</asp:SqlDataSource>

Alternate color by group of rows in a gridview

Posted: December 18, 2007 | Author: | Filed under: ASP.NET 2 | 2 Comments

Here is a sample to alternate color by group of rows in a gridview. The grouping is based on a column named "CatName" in my sample. You can use whatever column you want in your code with your gridview for grouping.

  string strGroup = "";
    Int32 t = 0; 
  
    protected void GridView1_OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {

            DataRowView drv = (DataRowView)e.Row.DataItem;

            if (!(drv["CatName"].ToString() == strGroup))
            {
             
                strGroup = drv["CatName"].ToString();            
                t += 1;
            }
            if (t % 2 == 0)
            {
                e.Row.BackColor = System.Drawing.Color.Azure;
            }
            else
            {
                e.Row.BackColor = System.Drawing.Color.GhostWhite;
            }

        
        }
    }

Export GridView/DataGrid to Excel with money format for some columns

Posted: December 14, 2007 | Author: | Filed under: ASP.NET | Leave a comment 

For DataGrid: 
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim style As String = "<style> .myMoney { mso-number-format:022$022\#\,\#\#0\.00; } </style> "

        Response.Clear()
        Response.Charset = ""
        Response.AddHeader("content-disposition", "attachment;filename=DataGridmyMoneyExcelFile.xls")
        Response.ContentType = "application/excel"

        Dim stringWrite As New System.IO.StringWriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'Dim dg As New System.Web.UI.WebControls.DataGrid
        'dg.DataSource = objDataTable
        AddHandler DataGrid1.ItemDataBound, AddressOf Me.DataGrid1_ItemDataBound
        'dg.DataBind()
        DataGrid1.RenderControl(htmlWrite)

        'add style
        Response.Write(style)


        Response.Write(stringWrite.ToString())
        Response.End()

    End Sub

    Protected Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound

        If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then

            'four columns in my sample: id	 col1	AmountDeductible	AmountPaid

            e.Item.Cells(3).Attributes.Add("class", "myMoney")
            e.Item.Cells(2).Attributes.Add("class", "myMoney")
        End If

    End Sub
For GridView:
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


        Dim style As String = "<style> .myMoney { mso-number-format:022$022\#\,\#\#0\.00; } </style> "

        Response.ClearContent()
        Response.AddHeader("content-disposition", "attachment;filename=myMoneyExcelFile.xls")
        Response.ContentType = "application/excel"

        Dim sw As StringWriter = New StringWriter()
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)

        GridView1.RenderControl(htw)
        'add style
        Response.Write(style)

        Response.Write(sw.ToString())
        Response.End()

    End Sub

    Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound

        If e.Row.RowType = DataControlRowType.DataRow Then
'four columns in my sample: id col1 AmountDeductible AmountPaid
            e.Row.Cells(3).Attributes.Add("class", "myMoney")
            e.Row.Cells(2).Attributes.Add("class", "myMoney")
        End If
    End Sub

 

A sample with UNPIVOT and CTE

Posted: December 14, 2007 | Author: | Filed under: SQL Server 2005 | Leave a comment
Here is a sample for UNPIVOT function along with CTE to find the occurance from an Excel imported table.

There is a solution by using SQL Server 2005 UNPIVOT function and CTE.

CREATE TABLE [dbo].[tableData](

[a] [int]

NULL,

[b] [int]

NULL,

[c] [int] NULL,

[d] [int]

NULL,[id] [int] IDENTITY(1,1) NOT NULL

)

END

GO

SET IDENTITY_INSERT [dbo].[tableData] ON

INSERT

[dbo].[tableData] ([a], [b], [c], [d], [id]) VALUES (0, 1, 2, 3, 1)

INSERT

[dbo].[tableData] ([a], [b], [c], [d], [id]) VALUES (1, 1, 2, 2, 2)

INSERT

[dbo].[tableData] ([a], [b], [c], [d], [id]) VALUES (2, 2, 0, 3, 3)

SET

IDENTITY_INSERT [dbo].[tableData] OFF

–Here is the script you can modify to hold more columns

with

mycte

AS

(

SELECT id, col, val FROM tableData

UNPIVOT

(

val for col IN ([a], [b], [c], [d]) ) –you can add more columns here

AS

unpvt

)

SELECT

 id, SUM(CASE WHEN val=1 THEN 1 ELSE 0 END) as [no of 1’s],

SUM

(CASE WHEN val=2 THEN 1 ELSE 0 END) as [no of 2’s],

SUM

(CASE WHEN val=3 THEN 1 ELSE 0 END) as [no of 3’s]

from

mycte

group

by id

Change the size of ASP:CheckBox through CSS

Posted: December 13, 2007 | Author: | Filed under: ASP.NET 2 | Leave a comment

We can use an html control checkbox  along with the style to change the size of a html CheckBox’s size.

We can alse change the size of ASP:CheckBox’s size through a style definition. Since the ASP:CheckBox will be wrapped inside a SPAN tag when it is emitted to HTNL, we need to define the style to this span’ input element:

(active is a boolean column)

<style type="text/css">.mycheckBig input {width:25px; height:25px;}

.mycheckSmall input {width:10px; height:10px;}

</style>

 <asp:TemplateField HeaderText="activeSmall" >                 
<ItemTemplate><asp:CheckBox ID="CheckBoxSmall" runat="server"  Checked=‘<%# Convert.ToBoolean(Eval("active")) %>‘ AutoPostBack="False" CssClass="mycheckSmall"  />
</ItemTemplate>
</asp:TemplateField>
               
<asp:TemplateField HeaderText="activeBig" >
<ItemTemplate> <asp:CheckBox ID="CheckBoxBig" runat="server"  Checked=‘<%# Convert.ToBoolean(Eval("active")) %>‘ AutoPostBack="False" CssClass="mycheckBig"  />
</ItemTemplate>
</asp:TemplateField>

← Older Entries