Thursday 14 December 2017

MSSQL - Fix Error - Incorrect syntax near while inserting records into table

Watch solution on YouTube

to fix it replace
INSERT INTO [dbo].[Customers]
           ([FisrtName]
           ,[LastName])
     VALUES
           ('frank', 'sinatra')
           ('john', 'lennon')
GO

with
INSERT INTO [dbo].[Customers]
           ([FisrtName]
           ,[LastName])
     VALUES
           ('frank', 'sinatra'),
           ('john', 'lennon')
GO

MSSQL - Fix Error - Invalid column name

Msg 207, Level 16, State 1, Line 8
Invalid column name 'frank'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'sinatra'.



Watch solution on YouTube

To fix it replace
INSERT INTO [dbo].[Customers]
           ([FisrtName]
           ,[LastName])
     VALUES
           ("frank", "sinatra")
GO

with
INSERT INTO [dbo].[Customers]
           ([FisrtName]
           ,[LastName])
     VALUES
           ('frank', 'sinatra')
GO

Wednesday 13 December 2017

MSSQL - Fix Error - An object or column name is missing or empty. For SELECT INTO

Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.



watch this example on YouTube



To fix it replace
 SELECT FisrtName + N'' '' + LastName
  FROM [Company].[dbo].[Customers]

with
SELECT FisrtName + N' ' + LastName
  FROM [Company].[dbo].[Customers]

Thursday 7 December 2017

MVC - Custom Validation - ensure Display name is shown in error message

Validation - Get Display Name of the filed (property) in error message
also get display name of the filed in both client and server code

Watch this tutorial on YouTube

1. Layout File

<!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.10.2.js")
        @Scripts.Render("~/Scripts/jquery.validate.js")
        @Scripts.Render("~/Scripts/jquery.validate.unobtrusive.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/jqueryval")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>

2. Customer class

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

namespace MvcCustomValidation2.Models
{
    public class Customer
    {
        [Display(Name = "First Name")]
        [ValidateCorrespondingRequiredFields(new string[] { "LastName", "FirstName" })]
        public string FirstName { get; set; }

        [Display(Name = "Last Name")]
        [ValidateCorrespondingRequiredFields(new string[] { "FirstName", "LastName" })]
        public string LastName { get; set; }
        public int Age { get; set; }
        public int Salary { get; set; }
    }
}

3. Validation file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.Text.RegularExpressions;
using System.Web.Mvc;
using System.Reflection;

namespace MvcCustomValidation2.Models
{
    public class ValidateCorrespondingRequiredFieldsAttribute: ValidationAttribute, IClientValidatable
    {
        private readonly string[] _fields;

        public ValidateCorrespondingRequiredFieldsAttribute(string[] fields)
        {
            _fields = fields;
        }

        protected override ValidationResult IsValid(object value, ValidationContext validationContext)
        {
            PropertyInfo propertyFirst = validationContext.ObjectType.GetProperty(_fields[0]);
            PropertyInfo propertySecond = validationContext.ObjectType.GetProperty(_fields[1]);
            var valueFirst = propertyFirst.GetValue(validationContext.ObjectInstance, null);
            var valueSecond = propertySecond.GetValue(validationContext.ObjectInstance, null);
            if(valueFirst != null && valueSecond == null)
            {
                return new ValidationResult(validationContext.DisplayName + " cannot be empty");
            }
            return null;
        }

        public IEnumerable<ModelClientValidationRule> GetClientValidationRules(ModelMetadata metadata, ControllerContext context)
        {
            var rule = new ModelClientValidationRule
            {
                ValidationType = "validatecorrespondingrequiredfields",
                ErrorMessage = metadata.GetDisplayName() + " cannot be empty"
            };
            rule.ValidationParameters.Add("fields", string.Join(",", _fields));
            yield return rule;
        }
    }
}
4. Home controller after modifications
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcCustomValidation2.Models;

namespace MvcCustomValidation2.Controllers
{
  
    public class HomeController : Controller
    {
        public ActionResult Test()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Test(Customer c)
        {
            if (ModelState.IsValid)
            {
                // do something here
            }
            return View(c);
        }

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

5. View

@model MvcCustomValidation2.Models.Customer

@{ Html.EnableClientValidation();}
@using (Html.BeginForm(null, null, FormMethod.Post))
{
    @Html.AntiForgeryToken()

    @Html.LabelFor(x => x.FirstName)
    @Html.TextBoxFor(x => x.FirstName)
    <br />

    @Html.LabelFor(x => x.LastName)
    @Html.TextBoxFor(x => x.LastName)
    <br />

    @Html.LabelFor(x => x.Salary)
    @Html.TextBoxFor(x => x.Salary)
    <br />

    @Html.ValidationSummary(String.Empty)
    <input type="submit" value="Click Me" />
}

@section Scripts{
    <script type="text/javascript">
        $.validator.unobtrusive.adapters.add("validatecorrespondingrequiredfields", ["fields"], function (options) {
            var element = options.element;
            var otherNames = options.params.fields.split(',');
            var fields = [];
            $.each(otherNames, function (index, item) {
                fields.push(ElementDetails.getElementDetails(element, item))
            });
            options.rules['validatecorrespondingrequiredfields'] = { fields: fields };
            options.messages['validatecorrespondingrequiredfields'] = options.message;
        });
       
        $.validator.addMethod("validatecorrespondingrequiredfields", function (value, element, params) {
            if ($(element).val() != '') {
                return true;
            }
            var isValid = true;
            $.each(params.fields, function (index, item) {
                if ($(this).val() != '') {
                    isValid = false;
                }
            });
            return isValid;
        });

        ElementDetails = {
            getElementDetails: function (validationElement, item) {
                var retElement = $('#' + item);
                if (retElement.length === 1) {
                    return retElement;
                }
                var name = validationElement.name;
                var index = name.lastIndexOf(".") + 1;
                var id = (name.substr(0, index) + item).replace(/[\.\[\]]/g, "_");
                retElement = $('#' + id);
                if (retElement.length === 1) {
                    return retElement;
                }
                return null;
            }
        }
    </script>   
}


Tuesday 5 December 2017

MVC - Fix Error - Warning: No message defined for - in custom validaiton


watch solution on YouTube

in my case it was typo
to fix it replace
 options.messages['validatecorrespondingrequiredfield'] = options.messsage;
with
 options.messages['validatecorrespondingrequiredfield'] = options.message;

Monday 4 December 2017

MSSQL - Stored procedure inside stored procedure


Watch this example on YouTube



CREATE PROCEDURE GetOrders
AS
BEGIN
    SET NOCOUNT ON;

    Declare @now datetime = getdate()
    Exec SetUpdateRecord @now
    Select * from Orders
END
GO

MVC - Fix Error - An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code Additional information: Input string was not in a correct format.

An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code

Additional information: Input string was not in a correct format.

watch solution on YouTube


to fix it replace

 private string DisplayedErrorMessage = "Either {0] or {1} is required";

with

 private string DisplayedErrorMessage = "Either {0} or {1} is required";

Saturday 2 December 2017

MSSQL - Fix Error - Column, parameter, or variable #7: Cannot find data type BOOL.

error Column, parameter, or variable #7: Cannot find data type BOOL.

watch solution on YouTube

To fix it replace
ALTER TABLE Product

ADD thisIsFinalSale BOOL NOT NULL DEFAULT(0)

GO

with

ALTER TABLE Product

ADD thisIsFinalSale BIT NOT NULL DEFAULT(0)

GO

MSSQL - Fix Error - The name "false" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Error

The name "false" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Watch solution on YouTube


To fix it replace

ALTER TABLE Product
ADD isFinalSale bit NOT NULL DEFAULT(false)
GO

with

ALTER TABLE Product
ADD isFinalSale bit NOT NULL DEFAULT(0)
GO