Md. Sumonur Rahman [Sumon]

Monday, March 6, 2017

Looping in SQL Server

DECLARE @cnt INT = 1;
WHILE @cnt < (select max(id) FROM [Item])
BEGIN

update [Stock]
set [PurchaseQty] = (select [SaleQty] + [BalanceQty] FROM [Stock] where ItemId = @cnt)
where ItemId = @cnt

SET @cnt = @cnt + 1;

END;

Saturday, January 14, 2017

Timer in Web Form

Process-1 (Using C# in ASP.Net Page):

<asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>

            <asp:Timer ID="Timer1" runat="server" Interval="100" ontick="Timer1_Tick"> </asp:Timer>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">

                <ContentTemplate>

              <table style="height:30px; width: 100%; font-size: small;">
                <tr>
                    <td bgcolor="#40541C"
                        style= "color: #FFFFFF; text-align: right; width: 250px;">
                        <asp:Label ID="lblTimer" runat="server"></asp:Label>
                    </td>

                </tr>
            </table>

                    </ContentTemplate>

                <Triggers>

                <asp:AsyncPostBackTrigger ControlID="Timer1" EventName="Tick"></asp:AsyncPostBackTrigger>

            </Triggers>

         </asp:UpdatePanel>

protected void Timer1_Tick(object sender, EventArgs e)
        {
            lblTimer.Text = "Date Time: " + DateTime.Now.ToString("dd.MM.yyyy, hh:mm:ss tt");
        }

Process-2 (Using JavaScript):

<script type="text/javascript">
        function ShowCurrentTime() {
            var dt = new Date();
            //var date =
            document.getElementById("lblTime").innerHTML = dt.toDateString() + ', ' + dt.toLocaleTimeString();
            window.setTimeout("ShowCurrentTime()", 1000); // Here 1000(milliseconds) means one 1 Sec
        }
</script>

<body onload="ShowCurrentTime()"> </body>

Thursday, December 22, 2011

Execute JavaScript from C# in update panel

Place the code before closing the update panel

<Triggers> <asp:PostBackTrigger ControlID = "btnSave" />
</Triggers>

so that it would recognize the control under where the javascript written from c#

Tuesday, December 20, 2011

JS Alert Message by C# Code

Normally:
public static void ShowMsgBox(string message)
        {
            Page page = HttpContext.Current.CurrentHandler as Page;
            page.ClientScript.RegisterStartupScript(typeof(Page), "abc", "<script type='text/javascript'>alert('" + message + "');</script>");
        }

        public static void ShowMsgBoxByPageHide(string message)
        {
            // Cleans the message to allow single quotation marks
            string cleanMessage = message.Replace("'", "\'");
            string script = "<script type='text/javascript'>alert('" + message + "');</script>";

            // Gets the executing web page
            Page page = HttpContext.Current.CurrentHandler as Page;

            // Checks if the handler is a Page and that the script isn't allready on the Page
            if (page != null && !page.ClientScript.IsClientScriptBlockRegistered("alert"))
            {
                page.ClientScript.RegisterClientScriptBlock(typeof(Page), "alert", script);
            }
        }

For in Update Panel:
public static void ShowMsgBoxInUpdatePanel(string message)
        {
            // Cleans the message to allow single quotation marks
            string cleanMessage = message.Replace("'", "");
            string alertScript = "javascript: alert('" + cleanMessage + "')";
            Page page = HttpContext.Current.CurrentHandler as Page;
            ScriptManager.RegisterStartupScript(page, page.GetType(), "alertScript", alertScript, true);
        }

Thursday, February 10, 2011

Allowing only Numeric in web TextBox using JS

Monday, January 31, 2011

Directly print any div of web page using JavaScript & HTML

Sample Div that is to print:

<br />

<asp:Label ID="lblDetails" runat="server" Font-Bold="True" Font- Size="Medium">

</asp:Label>

<br />

<asp:GridView ID="gvSample" runat="server">

</asp:GridView>

</div>

JavaScript Code:

function CallPrint(strid) {

var prtContent = document.getElementById(strid);

var width = 600;

var height = 500;

var left = (screen.width - width) / 2;

var top = (screen.height - height) / 2;

var WinPrint = window.open('', '', 'letf=' + left + ',top=' + top + ',width=' + width + ',height=' + height + ',toolbar=0,scrollbars=0,status=0');

WinPrint.document.write(prtContent.innerHTML);

WinPrint.document.close();

WinPrint.focus();

WinPrint.print();

WinPrint.close();

}

</script>

Print Button:

Wednesday, January 5, 2011

Pivoting Query

Actual Table:
Year Quarter Amount

1990	1	1.1
1990	2	1.2
1990	3	1.3
1990	4	1.4
1991	1	2.1
1991	2	2.2
1991	3	2.3
1991	4	2.4
1992	4	2.4

Desired Output:
(Here Q for Quarter)
Year       Q-1       Q-2      Q-3       Q-4
1990        1.1        1.2        1.3    1.4
1991        2.1      2.2        2.3    2.4
1992        0.0        0.0    0.0      2.4
Query:
Use Northwind
GO
CREATE TABLE Pivot
( Year      SMALLINT,
Quarter   TINYINT,
Amount      DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
INSERT INTO Pivot VALUES (1992, 4, 2.4)
GO
SELECT * FROM Pivot
GO
SELECT Year,
    SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
    SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
    SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
    SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
Another Output:
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
             SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
             SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
             SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
             SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
     FROM Pivot AS P
     GROUP BY P.Year) AS P1
GO