Dec 31, 2010

Display column of different rows as column of a single row in SQL Server

 
Problem
Displaying column value of different rows in a single row with a separator comma, hyphen, semicolon etc.

Solution
In the below solution, refer the table "PRODUCT". In the table there are multiple products for a client and we are trying to display products as comma separated value for each client as a single row.

Table and Data Script
CREATE TABLE PRODUCT (
      ClientNumber    VARCHAR(50)
    , ClientName    VARCHAR(50)
    , Product        VARCHAR(50)
)

INSERT INTO PRODUCT
SELECT '100SON', 'Sony', 'TV' UNION ALL
SELECT '100SON','Sony', 'DVD Player' UNION ALL
SELECT '100SON','Sony', 'Cell Phone' UNION ALL
SELECT '200KEN','Kenmoore', 'Microwave' UNION ALL
SELECT '200KEN','Kenmoore', 'Dryer'
Table Data
Table Data

Now, lets write a query to display the data of product as a single row on the basis of Client Number and Client Name.

SELECT ClientNumber, ClientName
       , STUFF(( SELECT ',' + Product
                 FROM PRODUCT b
                 WHERE a.ClientNumber = b.ClientNumber
                 FOR XML PATH('')
         ), 1, 1, '') AS ProductList
FROM  PRODUCT a
GROUP BY ClientNumber, ClientName
OUTPUT
Output
redapple24

Dec 6, 2010

Cumulative Sum in Oracle

 
In this post, I am sharing the code to calculate Cumulative sum in Oracle

Table Sample Data

Table Sample Data

 Query to get Cumulative Sum of Amount on the basis of Type

SELECT NUM
       , DECODE(TYPE, 'DR', AMOUNT)
       , DECODE(TYPE, 'CR', AMOUNT)
       , SUM (DECODE(TYPE, 'CR', AMOUNT, -1 * AMOUNT))
         OVER (ORDER BY NUM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
         AS BALANCE
FROM TBL
GROUP BY NUM, TYPE, AMOUNT
Output
Output

Nov 24, 2010

GridView inside GridView in ASP.Net

 
In this post, we will see how to use gridview inside gridview and where it is required?

Common scenario to used GridView inside GridView is to display Header and Detail Relationship Data in asp.net web page where
  • Outer GridView will display Header Data 
  • Inner GridView will display detail data.
Now lets take a example to see how it works

Lets take two Tables for Header and Detail Data
  • Header Table stores header information of the sale like Invoice Date, Customer Name & Total Amount
  • Detail Table stores details information of the sale like Item, Qty, Price, Amount where Amount is a computed column (Qty*Price)

 Lets create the tables and populate some data
CREATE TABLE dbo.tblSalesH(
     InvoiceNo        VARCHAR(10)
     , InvoiceDate    DATETIME
     , CustomerName   VARCHAR(50)
     , TotalAmount    NUMERIC(10,2)     
)     

CREATE TABLE dbo.tblSalesD(
     InvoiceNo        VARCHAR(10)
     , Item           VARCHAR(10)
     , Qty            INT
     , Price          NUMERIC(10,2)
     , Amount AS (Qty*Price)
)     

INSERT INTO dbo.tblSalesH  (InvoiceNo, InvoiceDate, CustomerName, TotalAmount)
SELECT 'INV0000001', '11/01/2010', 'Ajay Sharma', 1000 UNION ALL
SELECT 'INV0000002', '11/02/2010', 'Sandeep Mittal', 800 UNION ALL
SELECT 'INV0000003', '11/03/2010', 'Abhay Kumar', 650

INSERT INTO dbo.tblSalesD (InvoiceNo, Item, Qty, Price)
SELECT 'INV0000001', 'Item - 1', 10, 50 UNION ALL
SELECT 'INV0000001', 'Item - 2', 20, 25 UNION ALL
SELECT 'INV0000002', 'Item - 1', 10, 40 UNION ALL
SELECT 'INV0000002', 'Item - 2', 20, 20 UNION ALL
SELECT 'INV0000003', 'Item - 1', 7, 50 UNION ALL
SELECT 'INV0000003', 'Item - 2', 10, 30
Below is the code to display the relationship of Header and Detail data information in gridview. 

Additionally, you need to create an image folder in your website and place two gif files plus.gif and minus.gif that can be saved by clicking on the links

Relation.aspx
@ Page Language="C#" AutoEventWireup="true" CodeFile="Relation.aspx.cs" Inherits="Relation" %>
<head runat="server">
    <title>Sales Report</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="gvParent" runat="server" AutoGenerateColumns="False" CellPadding="4" Width="100%" OnRowDataBound="gvParent_RowDataBound" OnRowCommand="gvParent_RowCommand" ForeColor="Black" BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellSpacing="2">
            <FooterStyle BackColor="#CCCCCC" />
            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:ImageButton ID="btnImage" runat="server" ImageUrl="~/Images/plus.gif" CommandName="expand" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="InvoiceNo" HeaderText="Invoice No."></asp:BoundField>
                <asp:BoundField DataField="InvoiceDate" HeaderText="Invoice Date"></asp:BoundField>
                <asp:BoundField DataField="CustomerName" HeaderText="Customer Name"></asp:BoundField>
                <asp:BoundField DataField="TotalAmount" HeaderText="Total Amount"></asp:BoundField>
                <asp:TemplateField>
                    <ItemTemplate>
                        </td></tr>
                        <tr>
                            <td>
                            </td>
                            <td colspan="4">
                                <asp:GridView ID="gvChild" runat="server" Width="100%" Visible="False" AutoGenerateColumns="False" DataSource='<%# GetChildRelation(Container.DataItem,"Relation") %>' BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" ForeColor="Black" GridLines="Vertical">
                                    <AlternatingRowStyle BackColor="#CCCCCC" />
                                    <Columns>
                                        <asp:BoundField DataField="Item" HeaderText="Item"></asp:BoundField>
                                        <asp:BoundField DataField="Qty" HeaderText="Quantity"></asp:BoundField>
                                        <asp:BoundField DataField="Price" HeaderText="Price"></asp:BoundField>
                                        <asp:BoundField DataField="Amount" HeaderText="Total"></asp:BoundField>
                                    </Columns>
                                    <FooterStyle BackColor="#CCCCCC" />
                                    <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                                    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                                    <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                                    <SortedAscendingCellStyle BackColor="#F1F1F1" />
                                    <SortedAscendingHeaderStyle BackColor="#808080" />
                                    <SortedDescendingCellStyle BackColor="#CAC9C9" />
                                    <SortedDescendingHeaderStyle BackColor="#383838" />
                                </asp:GridView>
                            </td>
                        </tr>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
            <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
            <RowStyle BackColor="White" />
            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#808080" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#383838" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>
Relation.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

public partial class Relation : System.Web.UI.Page
{        
    private string ConnectionString 
    { 
        get {return "Data Source=SERVERNAME;Initial Catalog=DBNAME;User Id=UserName;Password=Pwd;";}        
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataSet ds = new DataSet();            
            ds = GetData();
            ds.Tables[0].TableName = "Parent";
            ds.Tables[1].TableName = "Child";
            DataRelation dr = new DataRelation("Relation", ds.Tables["Parent"].Columns["InvoiceNo"], ds.Tables["Child"].Columns["InvoiceNo"], false);
            dr.Nested = true;
            ds.Relations.Add(dr);
            gvParent.DataSource = ds;
            gvParent.DataMember = "Parent";
            gvParent.DataBind();
        }
    }

    protected DataView GetChildRelation(object pDataItem, string pRelation)
    {
        DataRowView pvoDataRowView;
        pvoDataRowView = (DataRowView)pDataItem;
        if (pvoDataRowView != null)
            return pvoDataRowView.CreateChildView(pRelation);
        else
            return null;
    }

    protected void gvParent_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "expand")
        {
            int pviIndex;
            pviIndex = Convert.ToInt16(e.CommandArgument.ToString());
            ImageButton btn = new ImageButton();
            btn = (ImageButton)gvParent.Rows[pviIndex].FindControl("btnImage");
            if (btn.ImageUrl.ToLower().Contains("plus.gif"))
            {
                btn.ImageUrl = "images/minus.gif";
                ((GridView)gvParent.Rows[pviIndex].FindControl("gvChild")).Visible = true;
            }
            else
            {
                btn.ImageUrl = "images/plus.gif";
                ((GridView)gvParent.Rows[pviIndex].FindControl("gvChild")).Visible = false;
            }
        }
    }

    protected void gvParent_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            ImageButton btn = new ImageButton();
            btn = (ImageButton)e.Row.Cells[0].FindControl("btnImage");
            btn.CommandArgument = e.Row.RowIndex.ToString();
        }
    }

    private DataSet GetData()
    {
        DataSet ds = new DataSet();
        SqlConnection con = new SqlConnection(ConnectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = @"BEGIN SELECT InvoiceNo, CONVERT(VARCHAR, InvoiceDate, 106) AS InvoiceDate, CustomerName, TotalAmount from tblSalesH; SELECT InvoiceNo, Item, Qty, Price, Amount FROM tblSalesD END";
        cmd.CommandType = CommandType.Text;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        con.Close();
        return ds;
    }    
}
OUTPUT
Output

Nov 21, 2010

Cross Page Posting in ASP.Net

 
In this post, we will discuss about Cross Page Posting in ASP.Net and how it works.

In ASP.Net Web Forms page, when you click a button or any event which result in post-back posts the webpage and it's control values to the same page by default. Under some circumstances you might want to post one page to another page, In that case you can configure control of a page to post on some other page. This is referred as Cross-Page Posting.

Cross-Page Posting is generally implemented when you need to access some information from the Source Page i.e Previous page in the Target Page i.e Next Page

Let's take a small example of how to implement Cross Page Posting. In the example, we will configure the button of Source Page to post to some other page and will access the value of Textbox of Source Page on Target Page.

PreviousPage.aspx code
<html>
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="_textBox" runat="server"></asp:TextBox>
        <asp:Button ID="btnPost" runat="server" Text="Post" PostBackUrl="~/NextPage.aspx"
            OnClick="btnPost_Click" />
    </div>
    </form>
</body>
</html>

In Previous Page, we have set PostBackUrl property of button to NextPage.aspx. This will result in post-back to NextPage.aspx instead of self.

NextPage.aspx code
<html>
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="_textBox" runat="server"></asp:TextBox>
    </div>
    </form>
</body>
</html>

NextPage.aspx.cs code
protected void Page_Load(object sender, EventArgs e)
{
    _textBox.Text = ((TextBox)PreviousPage.FindControl("_textBox")).Text;
}

In Next Page, we are accessing the value of Previous Page text-box using FindControl Method of PreviousPage Property which is under Page Class.

Disable ASP.Net Validator with Javascript

 
ASP.Net validators like RequireField Validator, Compare Validator, RangeValidator etc. works on server-side. but sometime you have the requirement where you have some customized validation on client-side and depending on some logic you want to enable/disable ASP.Net Validator on client-side.

Let's have an example with code to disable ASP.Net Validator using JavaScript

Scenario:
We have a checkbox Password Required on web-form where depending on check/un-check we have to enable/disable requiredfield validator for Password Textbox field.

Below are the two JavaScript methods to perform the same. You can use any of the method.

function disableValidator()
{
  var myVal = document.getElementById('myValidatorClientID');
  ValidatorEnable(myVal, false);
}

function disableValidator()
{
  var myval = document.getElememtById('validator.ClientId');
  myval.style.cssText="";
  myval.style.display='none';
  myval.style.accelerator=true;
}
© Copyright 2021 IT Developer Zone. Designed by Bloggertheme9 | Distributed By Gooyaabi Templates. Powered by Blogger.