How to do - MS SQL C# Excel Access: February 2018

Wednesday 28 February 2018

MVC - jQuery - Simple tabs example

Watch this example on YouTube

1. Install jQuery UI(Combined Library) through NuGet
2. Layout.chtml

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - My ASP.NET Application</title>
    @Styles.Render("~/Content/css")
    @Scripts.Render("~/bundles/modernizr")
    @Scripts.Render("~/Scripts/jquery-1.12.4.min.js")
    @Scripts.Render("~/Scripts/jquery-ui-1.12.1.min.js")

</head>
<body>
    <div class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
                @Html.ActionLink("Application name", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
            </div>
            <div class="navbar-collapse collapse">
                <ul class="nav navbar-nav">
                    <li>@Html.ActionLink("Home", "Index", "Home")</li>
                    <li>@Html.ActionLink("About", "About", "Home")</li>
                    <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
                </ul>
                @Html.Partial("_LoginPartial")
            </div>
        </div>
    </div>
    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <p>© @DateTime.Now.Year - My ASP.NET Application</p>
        </footer>
    </div>

    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/jqueryui")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>

3. Index.chtml

<div class="container">
    <ul class="tabs">
        <li class="tab-link current" data-tab="tab-1">Tab One</li>
        <li class="tab-link" data-tab="tab-2">Tab Two</li>
        <li class="tab-link" data-tab="tab-3">Tab Three</li>
        <li class="tab-link" data-tab="tab-4">Tab Four</li>
    </ul>

    <div id="tab-1" class="tab-content current">Some Text 1<br /> </div>
    <div id="tab-2" class="tab-content">Some Text 2<br />very important</div>
    <div id="tab-3" class="tab-content">Some Text 3<br />garbage</div>
    <div id="tab-4" class="tab-content">Some Text 4<br />something in the way...</div>
<script>
    $(document).ready(function () {
        $('ul.tabs li').click(function () {
            var tab_id = $(this).attr('data-tab');
            $('ul.tabs li').removeClass('current');
            $('.tab-content').removeClass('current');

            $(this).addClass('current');
            $("#" + tab_id).addClass('current');
        });
        $('ul.tabs li').mouseover(function () {
            var tab_id = $(this).attr('data-tab');
            $('ul.tabs li').removeClass('current');
            $('.tab-content').removeClass('current');

            $(this).addClass('current');
            $("#" + tab_id).addClass('current');
        });
    });
</script>

4. Add to Site.css

ul.tabs{
    margin:0px;
    padding:opx;
    list-style: none;
    background-color:brown;
}
ul.tabs li{
    background: none;
    color:red;
    display:inline-block;
    padding: 10px 15px;
    background-color:green;
    border-top-left-radius:3px;
    border-top-right-radius:3px;
}
ul.tabs li.current{
    background: white;
    color: blue;
    background-color: yellow;
}
.tab-content{
    display: none;
    background: white;
    padding: 15px;
}
.tab-content.current{
    display: inherit;
    border:1px;
    border-color:green;
}

Wednesday 21 February 2018

MSSQL - Rebuild all indexes in seconds

Watch on YouTube

Exec sp_MSforeachtable @command1 = "print '?' DBCC DBREINDEX ('?', ' ' , 80)"
GO

Exec sp_updatestats
GO

MSSQL - List all stored procedures related to specified table

Watch on YouTube

Select sys.sysobjects.name, sys.sysobjects.xtype FROM

sys.syscomments inner join sys.sysobjects on sys.syscomments.id = sys.sysobjects.id
where sys.syscomments.text like '%TABLE_NAME%' and sys.sysobjects.xtype = 'P'

MSSQL - Fix Error - Incorrect syntax near 'OUTPUT'.

Watch this example on YouTube

To fix it replace

Declare @SomeValue int = 0 OUTPUT
Exec spTest3 @SomeValue

with

Declare @SomeValue int = 0
Exec spTest3 @SomeValue OUTPUT

where stored procedure looks like:

CREATE PROCEDURE spTest3
    @SomeValue int = 0 OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET @SomeValue = 10;
    Return @SomeValue
END
GO

Saturday 17 February 2018

MSSQL - execute stored procedure every second but don't display the results

Watch this example on YouTube

while (1=1)
begin
    begin try
        SET FMTONLY ON
        Exec spTest
        SET FMTONLY OFF

        waitfor delay '00:00:01'
    end try
    begin catch
        select 'some error'
    end catch
end

MSSQL - Execute stored procedure every second

Watch this example on YouTube

while (1=1)
https://www.blogger.com/blogger.g?blogID=3931774540462211731#editor/target=post;postID=5769642921812394595;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=link begin
    begin try
        exec spTest
        waitfor delay '00:00:01'
    end try
    begin catch
        select ' error ' + cast(getdate() as varchar)
    end catch
end

MSSQL - Execute Stored Procedure but don't display the results

Watch this example on YouTube

Replace

EXEC [dbo].[spTest]

with

SET FMTONLY ON
EXEC [dbo].[spTest]
SET FMTONLY OFF

MSSQL - Convert to Absolute number

Watch this example on YouTube

Declare @Amount as Decimal = -1

Select ABS(@Amount)

MSSQL - How to get last day of the month

Watch this example on YouTube

this query will get last day of the month

Declare @dt as DateTime = '2/1/2018'
Declare @LastDayOfTheMonth as DateTime = dateAdd(day, -1, dateAdd(month, 1,
dateAdd(day, 1 - day(@dt), @dt)))

Select @LastDayOfTheMonth