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

Friday, 30 November 2018

MVC - Visual Studio 2015 and 2017 - Fix Error - 0x800a01b6 - JavaScript runtime error: Object doesn't support property or method 'dialog'

0x800a01b6 - JavaScript runtime error: Object doesn't support property or method 'dialog'

Watch this on YouTube

1. Install nuget jQuery.UI.Combined.
2. Modify layout.chtml file so it has at the top:

    <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.js")

and at the bottom:

    @Styles.Render("~/Content/themes/base/jquery-ui.css")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>

Tuesday, 27 November 2018

MVC - JSON - fix errror where no .success nor .error are called

watch this example on YouTube

in my case ensure    users.url is there

to fix it replace

        function LoadUsers() {
            var users = {};
            users = '@Url.Action("GetUserList", "Home")';
            users.type = "POST";
            users.dataType = "json";
            users.contentType = "application/json";
            users.success = function (Users) {
                alert('i am here');
                if (Users.length > 0) {
                    $('#UserTable').append('<table>');
                    $('#UserTable').append('<tr><th>First Name</th><th>Last Name</th><th></th><th></th></tr>');
                    $.each(Users, function (i, User) {
                        $('#UserTable').append('<tr><td class="FirstName">' + User.UserFirstName +
                            '</td><td class="LastName">' + User.UserLastName +
                            '</td><td> <input type="hidden" id="hid" value="' + User.UserID +
                            '" /> <input type="submit" value="EDIT" name="btn" id="btnEdit" onClick="EditUser(this);" </td><td><a href="#" id="btnDelete" onclick="DeleteUser(this);">DELETE</a></td></tr>');
                    });
                    $('#UserTable').append('</table>');
                }
            },
            users.error = function (xhr, status, error) {

                var el = document.createElement('html');
            }
            $.ajax(users);
        }

with

        function LoadUsers() {
            var users = {};
            users.url = '@Url.Action("GetUserList", "Home")';
            users.type = "POST";
            users.dataType = "json";
            users.contentType = "application/json";
            users.success = function (Users) {
                alert('i am here');
                if (Users.length > 0) {
                    $('#UserTable').append('<table>');
                    $('#UserTable').append('<tr><th>First Name</th><th>Last Name</th><th></th><th></th></tr>');
                    $.each(Users, function (i, User) {
                        $('#UserTable').append('<tr><td class="FirstName">' + User.UserFirstName +
                            '</td><td class="LastName">' + User.UserLastName +
                            '</td><td> <input type="hidden" id="hid" value="' + User.UserID +
                            '" /> <input type="submit" value="EDIT" name="btn" id="btnEdit" onClick="EditUser(this);" </td><td><a href="#" id="btnDelete" onclick="DeleteUser(this);">DELETE</a></td></tr>');
                    });
                    $('#UserTable').append('</table>');
                }
            },
            users.error = function (xhr, status, error) {

                var el = document.createElement('html');
            }
            $.ajax(users);
        }

MVC - Entity Framework - JSON - Fix Error - Enumeration yielded no results

Watch this example on YouTube

To fix it replace

       public JsonResult GetUserList()
        {
            IEnumerable<Users_SelectAll_Result> model = db.Users_SelectAll();
            return Json(model);
        }

with

       public JsonResult GetUserList()
        {
            IEnumerable<Users_SelectAll_Result> model = db.Users_SelectAll().ToList();
            return Json(model);
        }

Monday, 26 November 2018

MSSQL - Max function in SQL server that takes two values like Math.Max in .NET

Watch this example on YouTube

1. Table

CREATE TABLE [dbo].[NumberTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Num1] [int] NULL,
    [Num2] [nchar](10) NULL,
CONSTRAINT [PK_NumberTest] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

2. Query 1

(Select Max(val) From (Values (1002), (20)) as res(val))

3. Query 2

Select m.ID,
(Select Max(val) From (Values (m.Num1),(m.Num2)) as res(val)) From NumberTest m

MSSQL - Check how many times stored procedure was executed in last 100 seconds

Watch this example on YouTube

Declare @Count int = 0
While (@Count < 100)
Begin
    Begin Try
        Declare @dt DateTime
        Select @dt = dep.last_execution_time
        From sys.dm_exec_procedure_stats as dep inner join
        sys.objects as o on dep.object_id = o.object_id
        Where (dep.last_execution_time >= DateAdd(mi, -1, getdate())) and (o.name = N'spTest')
        Order by dep.last_execution_time desc

        Select 'Executed at: ' + (Convert(varchar(max), @dt, 121))
        Set @Count = @Count + 1
        waitfor delay '00:00:01'
    End Try
    Begin Catch
        print 'Error'
    End Catch
End

MSSQL - Assign result of dynamic sql to a variable

Watch this example on YouTube

1. Table

CREATE TABLE [dbo].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [SomeNumber] [int] NULL,
    [test] [bit] NOT NULL,
    [isFinalSale] [bit] NOT NULL,
    [isThisFinalSale] [bit] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC,
    [test] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

2. Query

Declare @SelectedIds varchar(max) = '1,2,10'
Declare @Sql nvarchar(max) = 'Select @res = Count(*) from Product Where ProductID in (' + @SelectedIDs + ')'
Declare @Result int
Exec sp_executesql @Sql, N'@res int out', @Result out
Select @Result

MSSQL - Display multiple results in comma separated field

Watch this example on YouTube

Table

CREATE TABLE [dbo].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [SomeNumber] [int] NULL,
    [test] [bit] NOT NULL,
    [isFinalSale] [bit] NOT NULL,
    [isThisFinalSale] [bit] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
    [ProductID] ASC,
    [test] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

Query

Select Stuff((Select ',' + Name From Product
Group by Name
For XML Path(''), Type).value('.', 'varchar(max)'),1,1,'')

Saturday, 24 November 2018

MVC - jQuery - Create Table Dynamically

Watch this example on YouTube

<div id="TestTable"></div>
@section Scripts{
    <script type="text/javascript">
        $(document).ready(function () {
            $('#TestTable').append("<table>");
            $('#TestTable').append("<tr><th>Col 1</th><th>Col 2</th></tr>");

            for (i = 0; i < 10; i++) {
                $('#TestTable').append('<tr><td> Field ' + i + '</td><td> Field ' + i + '</td></tr>');
            }
            $('#TestTable').append("</table>");
        });
    </script>
    }

Thursday, 22 November 2018

MSSQL - Fix Error - Cannot drop database "CompanyBackup" because it is currently in use.

Msg 3702, Level 16, State 3, Line 1
Cannot drop database "CompanyBackup" because it is currently in use.

watch solution on YouTube

To fix it replace

Drop Database Test
Go

with

Exec msdb.dbo.sp_delete_database_backuphistory @database_name = N'test'
Go
Use [master]
go
Alter Database Test Set Single_User with Rollback Immediate
Go
Drop Database Test
Go

MSSQL - Query to list all users with type of authentication

Watch this on YouTube

Select name, type_desc
From sys.server_principals
Where type = 'U'

MSSQL - Add multiple columns to table in one sql statement

Watch this example on YouTube

Alter Table Orders
Add
Column1 bit,
Column2 int,
Column3 varchar(30);

Wednesday, 21 November 2018

MSSQL - How to excecute stored procedure inside another stored procedure and assign result to a sql variable

Watch this example on YouTube

1. First stored proc

CREATE PROCEDURE Sproc1
    @ID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Count(*) From Product Where ProductID > @ID
END
GO

2. Second stored procedure

Alter PROCEDURE SProc2
AS
BEGIN
    SET NOCOUNT ON;
    Declare @MyID int = 2
    Declare @Result int
    Exec @Result = SProc1 @MyID
END

3. Execute stored procedure

Exec SProc2

MSSQL - How to update top 2 records in table (SQL SERVER)

Watch this example on YouTube

; with sth as (
    Select Top 2 *
    From Product
    Order by ProductID
)
Update sth SET Name = 'Something Else'

MSSQL - Fix Error - The size (5000) given to the parameter '@test' exceeds the maximum allowed (4000).

Msg 2717, Level 16, State 2, Line 1
The size (5000) given to the parameter '@test' exceeds the maximum allowed (4000).

watch solution on YouTube

To Fix it replace
Declare @test nvarchar(5000);
Set @test = N'very long string';
Select @test;

with

Declare @test nvarchar(max);
Set @test = N'very long string';
Select @test;

Tuesday, 20 November 2018

MSSQL - Fix Error - Incorrect syntax near the keyword 'Inner'. when deleting record

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Inner'.

watch solution on YouTube

Replace

Delete From Parent Inner Join Child On Parent.ParentID = Child.ParentID
Where Child.ChildID > 0

With

Delete p From Parent p Inner Join Child c On p.ParentID = c.ParentID
Where c.ChildID > 0

PARENT TABLE

/****** Object: Table [dbo].[Parent]    Script Date: 2018-11-20 7:34:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Parent](
    [ParentID] [int] IDENTITY(1,1) NOT NULL,
    [ParentName] [varchar](50) NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
    [ParentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CHILD TABLE

/****** Object: Table [dbo].[Child]    Script Date: 2018-11-20 7:35:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Child](
    [ChildID] [int] IDENTITY(1,1) NOT NULL,
    [ChildName] [varchar](50) NULL,
    [ParentID] [nchar](10) NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
    [ChildID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

MSSQL - how to return only date from sql server DateTime datatype

Watch this example on YouTube

Select Convert(date, getdate())

MSSQL - Select a random row from database table

watch this example on YouTube

Select Top 1 * From TABLE_NAME
Order by NewID()

MSSQL - Count values in comma separated string

Watch this example on YouTube

Declare @SomeValues varchar(max) = '1,2,3, 4, 4,4,44';
Select Len(@SomeValues) - Len(Replace(@SomeValues, ',', ''))+1

MSSQL - Execute stored procedure multiple times

watch this example on YouTube

Exec spProcedureName
Go 10

Monday, 19 November 2018

Execute the same query multiple times

select getdate()
GO 10

MS SQL - Execute query using shortcuts

ALT + X

MS SQL - Execute Insert Query Every Second

Watch this example on YouTube

~~1. Table:~~
/****** Object: Table [dbo].[DateTable]    Script Date: 2018-11-19 7:31:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DateTable](
    [DateField] [datetime] NULL
) ON [PRIMARY]

GO

2. Insert Query
while(1 = 1)
begin
    begin try
    Insert Into DateTable(DateField) values (GetDate())
    waitfor delay '00:00:01'
    end try
    begin catch
        select 'some error ' + cast(getdate() as varchar)
    end catch
end

3. Check (select query)
select * from DateTable order by DateField desc

Saturday, 17 November 2018

MSSQL - Fix Error - Conversion failed when converting date and/or time from character string.

watch this example on YouTube

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

To fix it replace

Select Convert(date, '07122018')

with

Select Convert(date, Right('07122018', 4) + LEFT('07122018', 2) + SUBSTRING('07122018', 3, 2))

MS SQL - Fix Error - Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Watch this example on YouTube

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

To fix it replace

Declare @SomeText varchar(max) = '1,2,3'
Declare @SQL varchar(max) = 'Select Count (*) from TableTest Where ID in (' + @SomeText + ')'
Exec sp_executesql @SQL

with

Declare @SomeText varchar(max) = '1,2,3'
Declare @SQL nvarchar(max) = 'Select Count (*) from TableTest Where ID in (' + @SomeText + ')'
Exec sp_executesql @SQL