Visual Studio 2019 - Fix Error - Could not load file or assembly 'Microsoft.XmlEditor, Version=15.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

Fix Error 

Could not load file or assembly 'Microsoft.XmlEditor, Version=15.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.

watch on YouTube

 

 

Ensure you can edit 

C:\Program Files (x86)\Microsoft Visual Studio\2019\Preview\Common7\IDE\devenv.exe.config

Open

C:\Program Files (x86)\Microsoft Visual Studio\2019\Preview\Common7\IDE\devenv.exe.config

Search for 

urn:schemas-microsoft-com:asm.v1

Next to it add

<dependentAssembly>

<assemblyIdentity name="Microsoft.XmlEditor" publicKeyToken="b03f5f7f11d50a3a" culture="neutral"/>

 <bindingRedirect oldVersion="0.0.0.0-16.0.0.0" newVersion="16.0.0.0"/> 

</dependentAssembly>

 

 

MVC - Fix Error - Cannot save'...Models\Model1.edmx': Could not load file or assembly 'Microsoft.XmlEditor, Version=15.0.0.0, Culture=neutral, ...

watch solution on YouTube


to fix error in Visual Studio 2019
Cannot save'...Models\Model1.edmx': Could not load file or assembly 'Microsoft.XmlEditor, Version=15.0.0.0, Culture=neutral, ...

delete
%LOCALAPPDATA%\Microsoft\VisualStudio\16.0_45e1ed66\ComponentModelCache

and restart Visual Studio 2019

MVC - JSON - Fix Issue - Function in Controller not receiving data

Function in controller is called but not receiving data

Watch this example on YouTube




to fix it replace

  function AddUser(e) {
            var UserRec = {};
            UserRec.UserFirstName = $('#FirstName').val();
            UserRec.UserLastName = $('#LastName').val();
            $.ajax({
                url: '@Url.Action("SaveUser", "Home")',
                data: '{usr: ' + JSON.stringify(UserRec) + '}',
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (response) {
                    if (response.ErrorMessage != null) {
                        alert(response.ErrorMessage);
                    }
                    else {
                        window.location.href = response.Url;
                    }
                },
                error: function (xhr, status, error) {
                    var el = document.createElement('html');
                    el.innerHTML = xhr.responseText;
                    alert(el.getElementByTagName('title')[0].innerText);
                }
           
            });

with

  function AddUser(e) {
            var UserRec = {};
            UserRec.UserFirstName = $('#FirstName').val();
            UserRec.UserLastName = $('#LastName').val();
            $.ajax({
                url: '@Url.Action("SaveUser", "Home")',
                type: 'POST',
                data: '{usr: ' + JSON.stringify(UserRec) + '}',
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (response) {
                    if (response.ErrorMessage != null) {
                        alert(response.ErrorMessage);
                    }
                    else {
                        window.location.href = response.Url;
                    }
                },
                error: function (xhr, status, error) {
                    var el = document.createElement('html');
                    el.innerHTML = xhr.responseText;
                    alert(el.getElementByTagName('title')[0].innerText);
                }
           
            });

MVC - JSON - Fix Error - 0x800a1391 - JavaScript runtime error: 'Json' is undefined

Watch this example on YouTube


Unhandled exception at line 199, column 13 in http://localhost:50023/Home/Index

0x800a1391 - JavaScript runtime error: 'Json' is undefined

to fix it replace
 function DeleteUser(e) {
            var row = $(e).closest('tr');
            var UserID = row.find($("[id*=hid]")).val();
            $.ajax({
                url: '@Url.Action("DeleteUser", "Home")',
                type: 'POST',
                data: Json.stringify({ UserID: UserID }),
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (response) {
                    if (response.ErrorMessage != null) {
                        alert(response.ErrorMessage);
                    }
                    else {
                        window.location.href = response.Url;
                    }
                },
                error: function (xhr, status, error) {
                    var el = document.createElement('html');
                    el.innerHTML = xhr.responseText;
                    alert(el.getElementByTagName('title')[0].innerText);
                }
            });
        }
with 

function DeleteUser(e) {
            var row = $(e).closest('tr');
            var UserID = row.find($("[id*=hid]")).val();
            $.ajax({
                url: '@Url.Action("DeleteUser", "Home")',
                type: 'POST',
                data: JSON.stringify({ UserID: UserID }),
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (response) {
                    if (response.ErrorMessage != null) {
                        alert(response.ErrorMessage);
                    }
                    else {
                        window.location.href = response.Url;
                    }
                },
                error: function (xhr, status, error) {
                    var el = document.createElement('html');
                    el.innerHTML = xhr.responseText;
                    alert(el.getElementByTagName('title')[0].innerText);
                }
            });
        }

MVC - JSON - Fix issue where function in Controller is not called

Watch This example on YouTube


to fix it replace

function LoadUsers() {
            var users = {};
            users.url = '@Url.Action("GetUserList", "Home")';
            users.type = 'POST';
            users.dataType = 'json';
            users.contentType = 'application/json';
            users.success = function (Users) {
                if (Users.length > 0) {
                    $('#UserList').append('<table>');
                    $('#UserList').append('<tr><th>First Name</th><th>Last Name</th><th></th><th></th></tr>');
                    $.each(Users, function (i, User) {
                        $('#UserList').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>');

                    });

                    $('#UserList').append('</table>');
                }
            };
         
        }

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) {
                if (Users.length > 0) {
                    $('#UserList').append('<table>');
                    $('#UserList').append('<tr><th>First Name</th><th>Last Name</th><th></th><th></th></tr>');
                    $.each(Users, function (i, User) {
                        $('#UserList').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>');

                    });

                    $('#UserList').append('</table>');
                }
            };
            $.ajax(users);
        }

MVC - JSON - Simple CRUD DB First Stored Procedures with no extra component installed

Watch this example on YouTube



1. SQL Table

CREATE TABLE [dbo].[Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserFirstName] [nvarchar](50) NULL,
    [UserLastName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
    [UserID] 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. Stored Procedures

CREATE PROCEDURE [dbo].[Users_Update]
    @UserID int,
    @UserFirstName nvarchar(50),
    @UserLastName nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Update Users Set UserFirstName = @UserFirstName, UserLastName = @UserLastName
        Where UserId = @UserID


END

CREATE PROCEDURE [dbo].[Users_SelectByID]
    @UserID Int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT * FROM USERS WHERE UserID = @UserID
END

CREATE PROCEDURE [dbo].[Users_SelectAll]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT * FROM Users
END

CREATE PROCEDURE [dbo].[Users_Insert]
    @UserFirstName nvarchar(50),
    @UserLastName nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Users(UserFirstName, UserLastName) Values(@UserFirstName, @UserLastName)
    Return 0
END

CREATE PROCEDURE [dbo].[Users_Delete]
    @UserID int
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM Users WHERE UserID = @UserID
END

3. Layout

<!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>&copy; @DateTime.Now.Year - My ASP.NET Application</p>
        </footer>
    </div>

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

4. Controller
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication2.Models;

namespace WebApplication2.Controllers
{
    public class HomeController : Controller
    {
        private CompanyEntities db = new CompanyEntities();
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }

        public JsonResult GetUserList()
        {
            IEnumerable<Users_SelectAll_Result> model = db.Users_SelectAll();
            return Json(model);
        }
        public JsonResult SaveUser(Users_SelectByID_Result usr)
        {
            db.Users_Insert(usr.UserFirstName, usr.UserLastName);
            var redirectUrl = new UrlHelper(Request.RequestContext).Action("Index", "Home");
            return Json(new { Url = redirectUrl });
        }
        public JsonResult UpdateUser(Users_SelectByID_Result usr)
        {
            db.Users_Update(usr.UserID, usr.UserFirstName, usr.UserLastName);
            var redirectUrl = new UrlHelper(Request.RequestContext).Action("Index", "Home");
            return Json(new { Url = redirectUrl });
        }
        public JsonResult DeleteUser(int UserID)
        {
            db.Users_Delete(UserID);
            var redirectUrl = new UrlHelper(Request.RequestContext).Action("Index", "Home");
            return Json(new { Url = redirectUrl });
        }

    }
}
5. Index.chtml


@{
    ViewBag.Title = "Home Page";
}

<div id="UserList" onload="LoadUsers();">

</div>
<div class="AddNewUser">
    @Html.Label("Add new user")
</div>
<div id="AddUser" style="display:none;">
    @Html.Label("First Name")
    @Html.TextBox("FirstName", null)
    <br />
    @Html.Label("Last Name")
    @Html.TextBox("LastName", null)
    <br />
    <input type="submit" onclick="AddUser(event)" value="SAVE" id="btnSaveUser" />
</div>

<div id="EditUser" style="display:none;">
    @Html.Label("First Name")
    @Html.TextBox("FirstNameEdit", null)
    <br />
    @Html.Label("Last Name")
    @Html.TextBox("LastNameEdit", null)
    <br />
    @Html.Hidden("UserIDEdit")
    <input type="submit" onclick="UpdateUser(event)" value="SAVE" id="btnSaveUser" />
</div>

@section Scripts{
    <script type="text/javascript">
        $(document).ready(function () {
            LoadUsers();
            $('#AddUser').dialog({
                autoOpen: false,
                height: 200,
                width: 500,
                modal: false
            });
            $(".AddNewUser").click(function (e) {
                e.stopPropagation();
                $('#AddUser').dialog('option', 'title', 'Add User');
                $('#AddUser').dialog('open');
            });
            $('#EditUser').dialog({
                autoOpen: false,
                height: 200,
                width: 500,
                modal: false
            });
        });

        function LoadUsers() {
            var users = {};
            users.url = '@Url.Action("GetUserList", "Home")';
            users.type = 'POST';
            users.dataType = 'json';
            users.contentType = 'application/json';
            users.success = function (Users) {
                if (Users.length > 0) {
                    $('#UserList').append('<table>');
                    $('#UserList').append('<tr><th>First Name</th><th>Last Name</th><th></th><th></th></tr>');
                    $.each(Users, function (i, User) {
                        $('#UserList').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>');

                    });

                    $('#UserList').append('</table>');
                }
            };
            $.ajax(users);
        }
        function AddUser(e) {
            var UserRec = {};
            UserRec.UserFirstName = $('#FirstName').val();
            UserRec.UserLastName = $('#LastName').val();
            $.ajax({
                url: '@Url.Action("SaveUser", "Home")',
                type: 'POST',
                data: '{usr: ' + JSON.stringify(UserRec) + '}',
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (response) {
                    if (response.ErrorMessage != null) {
                        alert(response.ErrorMessage);
                    }
                    else {
                        window.location.href = response.Url;
                    }
                },
                error: function (xhr, status, error) {
                    var el = document.createElement('html');
                    el.innerHTML = xhr.responseText;
                    alert(el.getElementByTagName('title')[0].innerText);
                }
           
            });
        }
        function EditUser(e) {
            var row = $(e).closest('tr');
            var UserID = row.find($("[id*=hid]")).val();
            var FirstName = row.find(".FirstName").text();
            var LastName = row.find(".LastName").text();
            $('#FirstNameEdit').val(FirstName);
            $('#LastNameEdit').val(LastName);
            $('#UserIDEdit').val(UserID);
            $('#EditUser').dialog('option', 'title', 'Edit User');
            $('#EditUser').dialog('open');
        }
        function UpdateUser(e) {
            var UserRec = {};
            UserRec.UserFirstName = $('#FirstNameEdit').val();
            UserRec.UserLastName = $('#LastNameEdit').val();
            UserRec.UserID = $('#UserIDEdit').val();
            $.ajax({
                url: '@Url.Action("UpdateUser", "Home")',
                type: 'POST',
                data: '{usr: ' + JSON.stringify(UserRec) + '}',
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (response) {
                    if (response.ErrorMessage != null) {
                        alert(response.ErrorMessage);
                    }
                    else {
                        window.location.href = response.Url;
                    }
                },
                error: function (xhr, status, error) {
                    var el = document.createElement('html');
                    el.innerHTML = xhr.responseText;
                    alert(el.getElementByTagName('title')[0].innerText);
                }
            });
        }
        function DeleteUser(e) {
            var row = $(e).closest('tr');
            var UserID = row.find($("[id*=hid]")).val();
            $.ajax({
                url: '@Url.Action("DeleteUser", "Home")',
                type: 'POST',
                data: JSON.stringify({ UserID: UserID }),
                contentType: 'application/json; charset=utf-8',
                dataType: 'json',
                success: function (response) {
                    if (response.ErrorMessage != null) {
                        alert(response.ErrorMessage);
                    }
                    else {
                        window.location.href = response.Url;
                    }
                },
                error: function (xhr, status, error) {
                    var el = document.createElement('html');
                    el.innerHTML = xhr.responseText;
                    alert(el.getElementByTagName('title')[0].innerText);
                }
            });
        }
    </script>   
}

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>

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);
        }

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,'')

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>
    }

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);

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;

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

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

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

MSSQL - Fix Error - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Watch this example on YouTube

 

To fix it replace
Select Convert(datetime, '06122018')

with

Select Convert(datetime, Right('06122018', 4) + Left('06122018', 2) +
  Substring('06122018', 3, 2))

MSSQL - FIx Error - Divide by zero error encountered

Watch this example on YouTube


Replace

Declare @Number1 int = 1
Declare @Number2 int = 0
Select @Number1 / @Number2

with

Declare @Number1 int = 1
Declare @Number2 int = 0
Select @Number1 / nullif( @Number2, 0)

MSSQL - Create temp table from select query result

Watch this example on YouTube



Select *
Into #TempProduct
From Product

Select * From #TempProduct

MSSQL - Fix Error - Incorrect syntax near while updating table using Inner join

Watch this example on YouTube



To fix this error replace


Update Product p Inner Join Orders o on p.ProductID = o.ProductID set o.Qty = 25 where o.Qty = 20

with

Update  o Set o.Qty = 25
From Product p
 Inner Join Orders o on p.ProductID = o.ProductID  where o.Qty = 20

MSSQL - Fix Error - Incorrect syntax near the keyword 'Exec'.

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

Watch this example on YouTube



I am trying to insert records from results of stored procedure

 to fix it replace

select * into #TemplTable
from Exec [SelectProducts]
select * FROM #TemplTable

with


IF OBJECT_ID('tempdb..#TemplTable') IS NOT NULL
DROP TABLE #TemplTable

CREATE TABLE #TemplTable (ProductID int, ProdName varchar(max))
Insert Into #TemplTable
Exec [SelectProducts]

select * FROM #TemplTable

IF OBJECT_ID('tempdb..#TemplTable') IS NOT NULL
DROP TABLE #TemplTable

MSSQL - Sql server - insert into table if record doesn't exist

Watch on YouTube


Select * From Orders

If not exists (Select * From Orders where OrderID = 10)
Begin
    Insert Into Orders (OrderID, ProductID, Qty, test) Values(10, 1, 20,1)
End

Select * From Orders

MSSQL - Insert results from stored procedure into temp table

Watch this example on YouTube





Create Table #TemplTable (ProductID int, ProdName varchar(max))
Insert into #TemplTable
Exec [SelectProducts]

select * FROM #TemplTable


stored procedure looks like this:
ALTER PROCEDURE [dbo].[SelectProducts]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT ProductID, Name FROM Product
END

MSSQL - Fix Error - No item by the name of 'schema.Orders' could be found in the current database 'Company', given that @itemtype was input as '(null)'.

Watch this example on YouTube


Msg 15225, Level 11, State 1, Procedure sp_rename, Line 418 [Batch Start Line 0]
No item by the name of 'schema.Orders' could be found in the current database 'Company', given that @itemtype was input as '(null)'.


To Fix is replace

exec sp_rename 'schema.Orders', 'Orders2'

With

exec sp_rename 'Orders', 'Orders2'

MVC - json - convert /Date(1262322000000)/ to short date string

watch this example on You Tube

 json - convert /Date(1262322000000)/ to short date string
Convert Json DateFormat to simple date format
Convert ISO Date to short date string
Pass and convert date from controller to view (jSon)

1. Stored Procedure
USE [Company]
GO
/****** Object:  StoredProcedure [dbo].[Users_SelectAll]    Script Date: 2018-03-27 1:55:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Users_SelectAll]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT * FROM Users
END


2. Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication2.Models;

namespace WebApplication2.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }

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

3. View

<div>
    <table id="CustomerTable"></table>
</div>
@section Scripts{
    <script type="text/javascript">
        $(document).ready(function () {
            LoadCustomers();
        });
        function LoadCustomers() {
            var cust = {};
            cust.url = '@Url.Action("GetCustomers", "Home")';
            cust.type = "POST";
            cust.dataType = "json";
            cust.contentType = "application/json";
            cust.success = function (Customers) {
                if (Customers.length > 0) {
                    $('#CustomerTable').append('<tr><th>Customer First Name</th><th>Start Date</th></tr>')
                    $.each(Customers, function (i, Customer) {
                        var dt = new Date(parseInt(Customer.StartDate.replace('/Date(', '')))
                        var dtFinal = AddLeadingZeros(dt.getFullYear(), 4) + '/' +
                            AddLeadingZeros(dt.getMonth() + 1, 2) + '/' +
                            AddLeadingZeros(dt.getDate(), 2)
                        $('#CustomerTable').append('<tr><td>' + Customer.FirstName +
                            '</td><td>' + dtFinal + '</td></tr>')
                    });
                }
            };
            function AddLeadingZeros(number, size) {
                var s = "0000" + number;
                return s.substr(s.length - size);
            }
            $.ajax(cust);
        }
    </script>   
}

MSSQL - Fix Error - Syntaxx error in pattern

Watch solution on YouTube





I am getting this error while searching for something - in my search I have "@" sign - remove it and error is gone.

MVC - json - Create table dynamically

Watch this example on YouTube


 
1. Stored procedure

USE [Company]
GO
/****** Object:  StoredProcedure [dbo].[Users_SelectAll]    Script Date: 2018-03-14 8:03:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Users_SelectAll]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT * FROM Users
END

2.  Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVCJsonT.Models;

namespace MVCJsonT.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }

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

3. View

<div id="UserList"></div>

@section Scripts{
    <script>
        $(document).ready(function () {
            LoadUsers();
        });

        function LoadUsers() {
            var users = {};
            users.url = '@Url.Action("GetUsers", "Home")';
            users.type = "POST";
            users.dataType = "json";
            users.contentType = "application/json";
            users.success = function (Users) {
                if (Users.length > 0) {
                    $('#UserList').append('<tr><th>First Name</th><th>Last Name</th></tr>')
                    $.each(Users, function (i, User) {
                        $('#UserList').append('<tr><td>' + User.FirstName + '</td><td>' + User.LastName + '</td></tr>')
                    });
                }
            };
            $.ajax(users);
        }
    </script>   
}

MVC - jQuery - Tabs - Validate - Show tab with error message on Submit

Watch this tutorial on YouTube


 

1. _Layout
<!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.validate.unobtrusive-ajax.js")
    @Scripts.Render("~/Scripts/jquery-ui-1.12.1.js")
    @Scripts.Render("~/Scripts/jquery.validate.js")
    @Scripts.Render("~/Scripts/jquery.validate.unobtrusive.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>&copy; @DateTime.Now.Year - My ASP.NET Application</p>
        </footer>
    </div>

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

2. Model - Customer

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace MVCJQueryTabs.Models
{
    public class Customer
    {
        [StringLength(5, MinimumLength =2, ErrorMessage ="{0} must be 2 to 5 chars long")]
        public string FristName { get; set; }

        [StringLength(5, MinimumLength = 2, ErrorMessage = "{0} must be 2 to 5 chars long")]
        public string LastName { get; set; }

        [StringLength(5, MinimumLength = 2, ErrorMessage = "{0} must be 2 to 5 chars long")]
        public string Address { get; set; }

        [StringLength(5, MinimumLength = 2, ErrorMessage = "{0} must be 2 to 5 chars long")]
        public string Country { get; set; }
    }
}

3. Controller - Home
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using MVCJQueryTabs.Models;

namespace MVCJQueryTabs.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            Customer c = new Customer();
            return View(c);
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

4. View - Index

@model MVCJQueryTabs.Models.Customer

<form>
    <div class="container">
        <ul class="tabs">
            <li class="tab-link tab-1 current" data-tab="tab-1">TAB ONE</li>
            <li class="tab-link tab-2" data-tab="tab-2">TAB TWO</li>
            <li class="tab-link tab-3" data-tab="tab-3">TAB THREE</li>
            <li class="tab-link tab-4" data-tab="tab-4">TAB FOUR</li>
        </ul>
        <div id="tab-1" class="tab-content current">
            @Html.LabelFor(x => x.FristName)
            @Html.TextBoxFor(x => x.FristName)
            @Html.ValidationMessageFor(x => x.FristName)
        </div>
        <div id="tab-2" class="tab-content">
            @Html.LabelFor(x => x.LastName)
            @Html.TextBoxFor(x => x.LastName)
            @Html.ValidationMessageFor(x => x.LastName)
        </div>
        <div id="tab-3" class="tab-content">
            @Html.LabelFor(x => x.Address)
            @Html.TextBoxFor(x => x.Address)
            @Html.ValidationMessageFor(x => x.Address)
        </div>
        <div id="tab-4" class="tab-content">
            @Html.LabelFor(x => x.Country)
            @Html.TextBoxFor(x => x.Country)
            @Html.ValidationMessageFor(x => x.Country)
        </div>
    </div>
    <br />
    <input type="submit" value="save" id="btnSave" />
</form>

<script>

    $(document).ready(function () {
        $("#btnSave").click(function () {
            $("form").data("validator").settings.ignore = "";

            var validator = $("form").validate();
            var isValid = $("form").valid();
            if (!isValid) {
                var tab_id = jQuery(validator.errorList[0].element).closest(".tab-content").attr('id');
                $('ul.tabs li').removeClass('current');
                $('.tab-content').removeClass('current');
                $("." + tab_id).addClass('current');
                $("#" + tab_id).addClass('current');
            }
        });

        $('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>

5. CSS

body {
    padding-top: 50px;
    padding-bottom: 20px;
}

/* Set padding to keep content from hitting the edges */
.body-content {
    padding-left: 15px;
    padding-right: 15px;
}

/* Override the default bootstrap behavior where horizontal description lists
   will truncate terms that are too long to fit in the left column
*/
.dl-horizontal dt {
    white-space: normal;
}

/* Set width on the form input elements since they're 100% wide by default */
input,
select,
textarea {
    max-width: 280px;
}


ul.tabs{
    width: 15%;
    float: left;
    margin: 0px;
    padding: 0px;
    list-style: none;
}
ul.tabs li{
    width: 100%;
    background: none;
    color: red;
    display: inline-block;
    padding: 10px 0px;
    background-color: green;
}
ul.tabs li.current{
    color: blue;
    background-color: yellow;
}
.tab-content{
    width: 60%;
    height: 100%;
    float: left;
    display:none;
    background: white;
    padding: 15px;
}
.tab-content.current{
    display: inherit;
}