ASP.NET WITH C#: January 2013

Tuesday 15 January 2013

Sql Cursor

DECLARE @ProductID decimal
DECLARE @getProductID cursor
SET @getProductID = CURSOR FOR
SELECT AddTaxRate
FROM salesnew
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID

Bind Accrodian Dynamically

<html>
<head><Title>Dynamically bind Accrodian</title>
</head>
<body>
<tr style="vertical-align: top;">
                    <td valign="top" style="width: 5%;">
                    </td>
                    <td valign="top" style="width: 90%;" align="center">
                        <cc1:Accordion ID="Accordion1" runat="server" CssClass="accordion" Width="100%" HeaderCssClass="accordionHeader"
                            Height="100%" HeaderSelectedCssClass="accordionHeaderSelected" ContentCssClass="accordionContent">
                        </cc1:Accordion>
                    </td>
                    <td valign="top" style="width: 5%;">
                    </td>
                </tr>
</head>
</body>

void bindData()
    {
        dt = new DataTable();
        cmd = new SqlCommand("selectEachCust_TablesID", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MainID", MainID);
        con.Open();
        da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        con.Close();
        if (dt.Rows.Count > 0)
        {
            bindAccordian(dt);
        }
    }

    void bindAccordian(DataTable dt1)
    {
        int i = 0;
        for (int j = 0; j < dt1.Columns.Count; j++)
        {
            if ((dt1.Rows[0][j].ToString()!="") && (Convert.ToInt64(dt1.Rows[0][j].ToString()) > 0))
            {
                AccordionPane p1 = new AccordionPane();

                p1.Style.Add("overflow", "scroll");
                //p1.Style.Add("Height", "100%");
                p1.ID = "AccordionPane" + i;
                Label lblHeader = new Label();
                lblHeader.ID = "lblHeader" + i;
                HiddenField hdnFld_tblName = new HiddenField();
                hdnFld_tblName.ID = "hdnFld_tblName" + i;
                if (dt1.Columns.Count > 1 && (tablename == "Sales" || tablename == "Service"))
                {
                    if (j == 0)
                    {
                        lblHeader.Text = "BSO Sales Order";
                        hdnFld_tblName.Value = "tblBsoSalesOrder";
                    }
                    else if (j == 1)
                    {
                        lblHeader.Text = "Billing Address";
                        hdnFld_tblName.Value = "tblBsoBillingAddress";
                    }
                    else if (j == 2)
                    {
                        lblHeader.Text = "Delivery Address";
                        hdnFld_tblName.Value = "tblBsoDeliveryAddress";
                    }
                    else if (j == 3)
                    {
                        lblHeader.Text = "Product Details";
                        hdnFld_tblName.Value = "tblBsoProducts";
                    }
                    else if (j == 4)
                    {
                        lblHeader.Text = "Spare Details";
                        hdnFld_tblName.Value = "tblBsoSpares";
                    }
                    else if (j == 5)
                    {
                        lblHeader.Text = "Charges";
                        hdnFld_tblName.Value = "tblBsoCharges";
                    }


                }
                else
                {
                    if (j == 0 && tablename == "AMC")
                    {
                        lblHeader.Text = "AMC Details";
                        hdnFld_tblName.Value = "tblAmc";
                    }
                    if (j == 1 && tablename == "AMC")
                    {
                        lblHeader.Text = "All AMC Details";
                        hdnFld_tblName.Value = "tblAmcDetails";
                    }
                    else if (j == 0 && tablename == "Query")
                    {
                        lblHeader.Text = "Query Details";
                        hdnFld_tblName.Value = "tblQuery";
                    }
                }
                p1.HeaderContainer.Controls.Add(lblHeader);
                DataTable dt2 = new DataTable();
                cmd = new SqlCommand("selectEachTableDetails", con);
                cmd.CommandType = CommandType.StoredProcedure;
                if (j != 3 && j != 4)
                    cmd.Parameters.AddWithValue("@MainID", dt1.Rows[0][j].ToString());
                else
                    cmd.Parameters.AddWithValue("@MainID", MainID);
                cmd.Parameters.AddWithValue("@TableName", hdnFld_tblName.Value.ToString());
                con.Open();
                da = new SqlDataAdapter(cmd);
                da.Fill(dt2);
                con.Close();
                if (hdnFld_tblName.Value == "tblBsoProducts")
                {
                    dt3 = dt2;
                    grv_Product.DataSource = dt2;
                    grv_Product.DataBind();
                    grv_Product.Visible = true;
                    p1.ContentContainer.Controls.Add(grv_Product);
                }
                else if (hdnFld_tblName.Value == "tblBsoSpares")
                {
                    dt4 = dt2;
                    grv_Spare.DataSource = dt2;
                    grv_Spare.DataBind();
                    grv_Spare.Visible = true;
                    p1.ContentContainer.Controls.Add(grv_Spare);
                }

                else if (dt2.Rows.Count < 2)
                {
                    dt6 = dt2;
                    GridView grid_AmcNew = new GridView();

                    grid_AmcNew.DataSource = dt2;
                    grid_AmcNew.DataBind();
                    grid_AmcNew.Visible = true;
                    CssClass_GridView(grid_AmcNew);
                    p1.ContentContainer.Controls.Add(grid_AmcNew);
                }
                Accordion1.Panes.Add(p1);
                i++;
            }
        }

Tuesday 8 January 2013

Onkeypress textbox and value show in label

Onkeypress on textbox and value show in label

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<link class="jsbin" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" />
<script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.js"></script>
<script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.js"></script>
<meta charset=utf-8 />
<title>JS Bin</title>

<style>
article, aside, figure, footer, header, hgroup,
menu, nav, section { display: block; }
</style>
<script type="text/javascript">
    $(function () {
        $('#txt1').keyup(function () {
         $('#output1').text($(this).val());
         $('#p1').text($(this).val());
        });
        $('#txt2').keyup(function () {
            $('#output2').text($(this).val());
            $('#p2').text($(this).val());
        });
        $('#txt3').keyup(function () {
            $('#output3').text($(this).val());
            $('#p3').text($(this).val());
        });
    });

</script>
</head>
<body>
<form id="form1" runat="server">
<%-- <input id="txt1" type="text" />--%>
<asp:TextBox ID="txt1" runat="server"></asp:TextBox><br />
<asp:Label ID="output1" runat="server"></asp:Label><br />
   <asp:TextBox ID="txt2" runat="server"></asp:TextBox><br />
<asp:Label ID="output2" runat="server"></asp:Label><br />
   <asp:TextBox ID="txt3" runat="server"></asp:TextBox><br />
<asp:Label ID="output3" runat="server"></asp:Label>
<br /><br />

   <div>
   <p id="p1"></p><br />
    <p id="p2"></p><br />
     <p id="p3"></p>
   </div>

</form>
</body>
</html>

Wednesday 2 January 2013

Procedure for Calculate Age

alter procedure pn_getDatedifference
(
@startdate datetime
)
as
begin

declare @monthToShow int
declare @dayToShow int
declare @enddate datetime
--set @startdate='01/21/1934'
set @enddate=getdate()

if(DAY(@startdate) > DAY(@enddate))
begin
set @dayToShow=0

if (month(@startdate) > month(@enddate))
begin
set @monthToShow= (12-month(@startdate)+ month(@enddate)-1)
end
else if (month(@startdate) < month(@enddate))
begin
set @monthToShow= ((month(@enddate)-month(@startdate))-1)
end
else
begin
set @monthToShow= 11
end
set @monthToShow= convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))-1
if(@monthToShow<0)
begin
set @monthToShow=0
end

declare @amonthbefore integer
set @amonthbefore=Month(@enddate)-1
if(@amonthbefore=0)
begin
set @amonthbefore=12
end

if(@amonthbefore in(1,3,5,7,8,10,12))
begin
set @dayToShow=31-DAY(@startdate)+DAY(@enddate)
end
if(@amonthbefore=2)
begin
IF (YEAR( @enddate ) % 4 = 0 AND YEAR( @enddate ) % 100 != 0) OR YEAR( @enddate ) % 400 = 0
begin
set @dayToShow=29-DAY(@startdate)+DAY(@enddate)
end
else
begin
set @dayToShow=28-DAY(@startdate)+DAY(@enddate)
end
end
if(@amonthbefore in (4,6,9,11))
begin
set @dayToShow=30-DAY(@startdate)+DAY(@enddate)
end

end
else
begin
set @monthToShow=convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))
if(month(@enddate)< month(@startdate))
begin
set @monthToShow=12+(month(@enddate)-month(@startdate))
end
else
begin
set @monthToShow= (month(@enddate)-month(@startdate))
end

set @dayToShow=DAY(@enddate)-DAY(@startdate)
end

SELECT
--FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25) as [yearToShow],
--@monthToShow as monthToShow ,@dayToShow as dayToShow ,
convert(varchar,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25)) +' Year ' + convert(varchar,@monthToShow) +' months '+convert(varchar,@dayToShow)+' days ' as age
end