SQL Server - Pass multiple strings (varchars) to single parameter in stored procedure

Watch this example on YouTube



1. here is my stored procedure
ALTER PROCEDURE spPassStrings
    @SelectedNames Varchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @SQL Varchar(max)
    Set @Sql = 'SELECT * FROM Customers WHERE FisrtName IN (' + @SelectedNames + ')'
    Exec (@SQL)
END
GO

2. and here is how to call it
Declare @FNames Varchar(Max) = '''Mark'', ''Frank'''

Exec spPassStrings @FNames

SQL Server - how to pass multiple integers to single parameter in stored procedure

Watch this example on YouTube


1. here is my stored procedure
CREATE PROCEDURE spPassIntegers
    @SelectedIDs Varchar(Max) = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @SQL AS NVarchar(MAX);

    SET @SQL = 'SELECT * FROM Customers WHERE ID IN (' + @SelectedIDs + ')'
    Exec (@SQL)
END
GO

2. here I'm passing multiple integers to parameter

Declare @IDs Varchar(Max) = '1,2, 1003'
Exec spPassIntegers @IDs

MVC - Fix Error - Compiler Error Message: CS0019: Operator '<' cannot be applied to operands of type 'int' and 'method group'

Watch on YouTube

Compilation Error

Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: CS0019: Operator '<' cannot be applied to operands of type 'int' and 'method group'


to fix it replace
@for (int i = 0; i < Model.tblList.Count; i++)
{
    @Html.Display("some text")
}

with 
@for (int i = 0; i < Model.tblList.Count(); i++)
{
    @Html.Display("some text")
}

MSSQL - Convert to Bit in Select Statement

Watch on YouTube



Select Cast(0 as Bit) as IsSelected

MVC - Fix issue with publishing - where some dll is missing

Watch on YouTube;



1. in solution explorer expend References and right click on desired dll
2. go to properties and set Copy Local to True

MSSQL - Fix Error - Could not find stored procedure 'SELECT * FROM Customers'.

Watch this example on YouTube

Error - Could not find stored procedure 'SELECT * FROM Customers'.

To fix it replace

Declare @Test as varchar(max) = 'SELECT * FROM Customers'
Exec @Test

with

Declare @Test as varchar(max) = 'SELECT * FROM Customers'
Exec (@Test)

MVC - How to freeze columns in table

Watch this example on YouTube



1. MODEL

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

namespace MVCTblScroll.Models
{
    public class CustomTable
    {
        public IEnumerable<GetTableTest_Result> tblList { get; set; }
    }
}

2. CONTROLLER

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

namespace MVCTblScroll.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();
        public ActionResult Index()
        {
            CustomTable model = new CustomTable();
            model.tblList = db.GetTableTest().ToList();
            return View(model);
        }

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

            return View();
        }

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

            return View();
        }
    }
}


3. VIEW

@model MVCTblScroll.Models.CustomTable

@using (Html.BeginForm(null, null, FormMethod.Post))
{
    if(Model.tblList != null)
    {
        <div id="table-container">
            <div class="table-section">
                <table>
                    <tr>
                        <th>
                            @Html.DisplayFor(model=>model.tblList.First().col1)
                        </th>
                        <th>
                            @Html.DisplayFor(model => model.tblList.First().col2)
                        </th>
                    </tr>
                    @foreach (var item in Model.tblList.ToList())
                    {
                        <tr>
                            <td>
                                <div class="one-line">@Html.DisplayFor(modelItem => item.col1)</div>
                            </td>
                            <td>
                                <div class="one-line">@Html.DisplayFor(modelItem => item.col2)</div>
                            </td>
                        </tr>
                    }
                </table>
            </div>
            <div class="table-section">
                <div class="outer">
                    <div class="inner">
                        <table>
                            <tr>
                                <th>
                                    @Html.DisplayNameFor(model=>model.tblList.First().col3)
                                </th>
                                <th>
                                    @Html.DisplayNameFor(model => model.tblList.First().col4)
                                </th>
                                <th>
                                    @Html.DisplayNameFor(model => model.tblList.First().col5)
                                </th>
                                <th>
                                    @Html.DisplayNameFor(model => model.tblList.First().col6)
                                </th>
                                <th>
                                    @Html.DisplayNameFor(model => model.tblList.First().col7)
                                </th>
                            </tr>
                            @foreach(var item in Model.tblList.ToList())
                            {
                                <tr>
                                    <td>
                                        @Html.DisplayFor(modelItem=>item.col3)
                                    </td>
                                    <td>
                                        @Html.DisplayFor(modelItem => item.col4)
                                    </td>
                                    <td>
                                        @Html.DisplayFor(modelItem => item.col5)
                                    </td>
                                    <td>
                                        @Html.DisplayFor(modelItem => item.col6)
                                    </td>
                                    <td>
                                        @Html.DisplayFor(modelItem => item.col7)
                                    </td>
                                </tr>
                            }
                        </table>
                    </div>
                </div>
            </div>
        </div>
    }
}


4. CSS
.outer{
    position:center;
}
.inner{
    overflow-x: auto;
    overflow-y:visible;
    width:400px;
}
#table-container{
    text-align:center;
    float:left;
}
.table-section{
    display:table-cell;
}
.header-three-rows{
    white-space:nowrap;
}
.one-line{
    word-break:keep-all;
    white-space:nowrap;
}
td{
    width:25px;
}
th{
    width:25px;
}
5. STORED PROCEDURE

USE [Company]
GO
/****** Object:  StoredProcedure [dbo].[GetTableTest]    Script Date: 2017-02-23 9:00:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetTableTest]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Select * FROM TableTest
END

HTML - Table - freeze multiple columns in table

Watch this example on youtube;




this code will produce simple table with first 2 column frozen while scrolling

<!DOCTYPE html>
<html>

<style type="text/css">
    th, td{
        border:3px solid black;
    }
    div{
        width: 20px;
        overflow-x: scroll;
        margin-left: 15em;
    }
    .col1{
        position: absolute;
        width: 10em;
        left: 0em;
        top: auto;
    }
    .col2{
        position: absolute;
        width: 5em;
        left: 10em;
        top: auto;
    }
</style>
<body>
    <div>
    <table>
        <tr>
            <th class="col1">aaaa</th><th class="col2">bbb</th><td>ccccc</td><td>ddddd</td></tr>
            <tr><th class="col1">aaaa</th><th class="col2">bbb</th><td>ccccc</td><td>ddddd</td></tr>
            <tr><th class="col1">aaaa</th><th class="col2">bbb</th><td>ccccc</td><td>ddddd</td></tr>
            <tr><th class="col1">aaaa</th><th class="col2">bbb</th><td>ccccc</td><td>ddddd</td></tr>
            <tr><th class="col1">aaaa</th><th class="col2">bbb</th><td>ccccc</td><td>ddddd</td></tr>
            <tr><th class="col1">aaaa</th><th class="col2">bbb</th><td>ccccc</td><td>ddddd</td></tr>
       
    </table>
    </div>
</body>
</html>

Fix error - Uncaught ReferenceError: auto is not defined

watch this example on youtube

To fix it replace
    <script type="text/javascript">
        $(document).ready(function () {
            $("btnSearch").css({
                top: auto, left: auto,
                "background-color": "yellow"
            }).add('body');

with 

    <script type="text/javascript">
        $(document).ready(function () {
            $("btnSearch").css({
                top: 'auto', left: 'auto',
                "background-color": "yellow"
            }).add('body');

MVC - Display all values from table as chechboxes

Watch on YouTube




1. My Stored Procedure

ALTER PROCEDURE GetEmployeeStatus
AS
BEGIN
    SET NOCOUNT ON;
    SELECT        StatusID As EmployeeStatusID, Name As EmployeeStatusName, Cast(0 As Bit) as IsSelected
FROM            EmployeeStatus
END
GO

2. Model
ALTER PROCEDURE GetEmployeeStatus
AS
BEGIN
    SET NOCOUNT ON;
    SELECT        StatusID As EmployeeStatusID, Name As EmployeeStatusName, Cast(0 As Bit) as IsSelected
FROM            EmployeeStatus
END
GO

3.View
@model MVCChkBxTest.Models.CustomReport

@using (Html.BeginForm(null, null, FormMethod.Post))
{
    @Html.HiddenFor(x => x.SelectedCheckboxes, Model.SelectedCheckboxes)

    if(Model.statusList != null)
    {
        foreach(var item in Model.statusList)
        {
            @Html.HiddenFor(modelItem => item.EmployeeStatusID)
            @Html.HiddenFor(modelItem => item.IsSelected)
            @Html.CheckBoxFor(modelItem => item.IsSelected.Value,
                    new { @id = item.EmployeeStatusID, @class = "checkboxClass"})
            @Html.DisplayFor(modelItem => item.EmployeeStatusName)
        }
        <input type="submit" value="Search" name="btn" id="btnSearch" />
    }
}

@section Scripts{
    <script type="text/javascript">
        $(document).ready(function () {
            $("#btnSearch").click(function (evt) {
                var checkedVals = $('.checkboxClass:checkbox:checked').map(function () {
                    return this.id;
                }).get();
                $("#SelectedCheckboxes").val((checkedVals.join(',')));
            });
        });
    </script>   
}

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

namespace MVCChkBxTest.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();
        public ActionResult Index()
        {
            CustomReport r = new CustomReport
            {
                statusList = db.GetEmployeeStatus()
            };
            return View(r);
        }

        [HttpPost]
        public ActionResult Index(CustomReport model, FormCollection c)
        {
            if (ModelState.IsValid)
            {
                int i = 0;
                int SelectedID = 0;
                bool isCheckboxSelected = false;

                var id = c.GetValues("item.EmployeeStatusID");
                var isSelected = c.GetValues("item.IsSelected");
                var selVal = c.GetValues("item.IsSelected.Value");
                for(i = 0; i < id.Count(); i++)
                {
                    SelectedID = Convert.ToInt32(id[i]);
                    isCheckboxSelected = Convert.ToBoolean(selVal[i]);
                }
                model.statusList = db.GetEmployeeStatus().ToList();
                if(model.SelectedCheckboxes != string.Empty)
                {
                    int[] nums = Array.ConvertAll(model.SelectedCheckboxes.Split(','),
                        int.Parse);
                    model.statusList = model.statusList.ToList();
                    List<GetEmployeeStatus_Result> res = (
                            from a in model.statusList.ToList()
                            join b in nums on a.EmployeeStatusID equals b
                            select a).ToList();
                    foreach(GetEmployeeStatus_Result r in res)
                    {
                        r.IsSelected = true;
                    }
                }
            }
            return View(model);
        }

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

            return View();
        }

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

            return View();
        }
    }
}

MVC - Fix Error - An exception of type 'System.ArgumentNullException' occurred in System.Web.Mvc.dll but was not handled in user code

Watch solution on YouTube



To Fix it replace
    @Html.Label(Model.SelectedCompanyName)

With
    @Html.Label(Model.SelectedCompanyName ?? "")

MVC - jQuery - How to get selected text and ID from DropDownList in jQuery

Watch on YouTube


here is my view
@model MVCDDLTest.Models.CustomerModel

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


    @Html.DropDownListFor(Model =>Model.SelectedCompanyID,
        new SelectList(Model.Comp, "CompanyID", "CompanyName"), "default value", new { @id = "myID" })
    <input type="submit" value="Save" />
}

@section Scripts{
    <script type="text/javascript">
        $(document).ready(function () {
            $("#myID").change(function (evt) {
                alert($("#myID").val());   //Display Selected ID
                alert($("#myID option:selected").text()) //Display Selected Text
            });
        });
    </script>   
}

mvc get selected value and text from drop down list in controller

Watch on YouTUbe



Controller
        CompanyEntities db = new CompanyEntities();

        public ActionResult testDDL()
        {
            CustomerModel c;
            c = new CustomerModel
            {
                Comp = db.GetCompanies()
            };
            return View(c);
        }

        [HttpPost]
        public ActionResult testDDL(CustomerModel model)
        {
            int? SelectedID = model.SelectedCompanyID;
            string SelectedText = model.SelectedCompanyName;
            model.Comp = db.GetCompanies();
            return View(model);
        }


View
@model MVCDDLTest.Models.CustomerModel

@using(Html.BeginForm(null, null, FormMethod.Post))
{
    @Html.AntiForgeryToken();
    @Html.Hidden("SelectedCompany", Model.SelectedCompanyName)

    @Html.DropDownListFor(Model => Model.SelectedCompanyID, new SelectList(Model.Comp, "CompanyID", "CompanyName"), "Default Value", new { @id = "SelID" })
    <input type="submit" vaue="submit" />
}

@section Scripts{
   
    <script type="text/javascript">
        $(document).ready(function () {
            $("#SelID").change(function (evt) {
                $("#SelectedCompany").val($("#SelID option:selected").text().trim());
            });
        });
    </script>   
}

Model

     public class CustomerModel
    {
        public IEnumerable<GetCompanies_Result> Comp {get;set;}
        public int SelectedCompanyID { get; set; }
        public string SelectedCompanyName { get; set; }
        public string Test { get; set; }
    }

VB.NET - ASP.NET - Fix Error - Additional information: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.

Watch solution on YouTube


To Fix it replace
Response.Write(String.Format("Text1 '{0}' Text2 {1}", "ABC"))

With

Response.Write(String.Format("Text1 '{0}' Text2 {1}", "ABC", "BBC"))

VB.NET - ASP.NET - Fix Erro - Expression expected

Watch solution on YouTube


To fix it replace
    Dim a As Integer = 0
        Dim b As Integer = 3
        Response.Write(IIf(a == 0, 0, b / a).ToString)

with

    Dim a As Integer = 0
        Dim b As Integer = 3
        Response.Write(IIf(a = 0, 0, b / a).ToString)

MVC - Modify Width of TextBoxFor

Watch on You Tube


In css ensure max width is not higher than actual width
input,
select,
textarea {
    /*max-width: 280px;*/
}


add new class in CSS
.width500{
    width:500px;
}

in View
@Html.TextBoxFor(Model =>Model.Test, new { @class = "width500" })

MSSQL - Fix Error - Must declare the scalar variable

Watch on YouTube


To fix it replace
Select Replace(LastName, @ReplaceText, 'ggggggg') as p from Customers

with
Declare @ReplaceText varchar(1) = 'a'
Select Replace(LastName, @ReplaceText, 'ggggggg') as p from Customers

MVC - jQuery - Get text and value of selected item in DropDownList

Watch on YouTube


@using (Html.BeginForm(null, null, FormMethod.Post))
{
    @Html.AntiForgeryToken()
    @Html.DropDownListFor(Model =>Model.CompanyID, new SelectList(Model.Comp, "CompanyID", "CompanyName"), "Default", new { @id ="CopmID"})
    <input type="submit" value="Save" />
}

@section Scripts{
    <script type="text/javascript">
        $(document).ready(function () {
            $("#CopmID").change(function (evt) {
                alert($("#CopmID").val()); //Value
                alert($("#CopmID option:selected").text());  //Text
            });
        });
    </script>

    }

MSSQL - Get all job names and job id's

Watch on YouTube





Select name, job_id from msdb.dbo.sysjobs

MSSQL - Query to list all jobs with steps

Watch this example on YouTube

select * from msdb.dbo.sysjobs as j
inner join msdb.dbo.sysjobsteps as s on
j.job_id = s.job_id

MSSQL - Fix Error - Divide by zero error encountered.

Watch this example on YouTube

To fix it replace

Declare @x As Integer = 1
Declare @y As Integer = 0
Select @x/@y

With

Declare @x As Integer = 1
Declare @y As Integer = 0
Select Case When @y != 0 Then Convert(Varchar,  @x/@y ) Else 'Cannot devide by 0' END

MSSQL - Fix Error - Conversion failed when converting the varchar value 'cannot devide by 0' to data type int.

Conversion failed when converting the varchar value 'cannot devide by 0' to data type int.

Watch solution on YouTube

To Fix it Replace
Declare @x As Integer = 1
Declare @y As Integer = 0
Select Case When @y != 0 Then @x/@y Else 'cannot devide by 0' end

With

Declare @x As Integer = 1
Declare @y As Integer = 0
Select Case When @y != 0 Then Convert(Varchar, @x/@y) Else 'cannot devide by 0' end

MSSQL - Query to list all database names with corresponding ID's

Watch this example on YouTube



Select DB_Name(database_id) as DatabaseName, database_id
From sys.databases;

MSSQL - Get database name by database id

Watch this example on YouTube


select DB_NAME(5) as dbName

MSSQL - Fix Error - There is already an object named '#Test' in the database.

Watch this example on YouTube



To fix it replace
Create Table #Test(id int, data varchar(100))

with

IF OBJECT_ID('tempdb..#Test') IS NULL
Create Table #Test(id int, data varchar(100))

MSSQL - fix error Cannot drop the table '#test', because it does not exist or you do not have permission.

watch solution on YouTube


Cannot drop the table '#test', because it does not exist or you do not have permission.


to fix it replace

Drop Table #test

with

IF OBJECT_ID('Customers..#temp') IS NOT NULL
Drop Table #test

MSSQL - Count number of occourance of a certain character in string

Watch on YouTube

Here i am counting number of "," (commas) in string

Declare @string varchar(1000)
Set @string = 'a, b, c, d'

Select len(@string) - len(replace(@string, ',', ''))

Here I am counting number of "a" in string

Declare @string varchar(1000)
Set @string = 'a, b, c, d'

Select len(@string) - len(replace(@string, 'a', ''))

MVC - fix error - The associated metadata type for type contains the following unknown properties or fields:

Watch solution on YouTube


Entire error message:
Additional information: The associated metadata type for type 'MVC_Intra.Models.Customer' contains the following unknown properties or fields: Address. Please make sure that the names of these members match the names of the properties on the main type.

Ensure your Metadata class doesn't  have extra (or deleted) properties

using System.ComponentModel.DataAnnotations;
namespace MVC_Intra.Models
{
    [MetadataType(typeof(CustomersMetadata))]
    public partial class Customer { }
    public class CustomersMetadata
    {
        public int ID { get; set; }
        public string FisrtName { get; set; }
        public string LastName { get; set; }

   
    }

Watch this example on YouTube





To fix it - add the following using
using System.ComponentModel.DataAnnotations;

MVC - Fix error - JavaScript critical error at line Unterminated string constant

Error:
JavaScript critical error at line 72, column 38 in http://localhost:50349/\n\nSCRIPT1015: Unterminated string constant

Watch solution on YouTube:



I have the following code in my View file
        </footer>

        @{
            var message = ViewData["Message"] ?? string.Empty;
        }
        <script type="text/javascript">
            var message = '@message';
            if (message)
                alert(message);
        </script>

and I am passing message from my controller like this:
ViewData["Message"] = "Message 1 \n Message 2";


To fix it replace
ViewData["Message"] = "Message 1 \n Message 2";

with
ViewData["Message"] = "Message 1 \\n Message 2";