MSSQL - Load records only if all specified columns are null

Watch this example on YouTube


Select * From TableWithNulls
Where Coalesce(FName, LName, Address) is null

MSSQL - Load records only if 1 of the specified columns is not null

Watch this example on YouTube


Check if at least 1 column is not null

Select * From TableWithNulls
WHERE Coalesce(FName, LName, Address) is not null

MVC - Chart - Display multiple charts in one View

Watch this example on YouTube


 

1. Controller

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


namespace WebApplication7.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Chart1()
        {
            var key = new Chart(width: 300, height: 300)
                .AddTitle("Employee 1")
                .AddSeries(
                    chartType: "Bubble",
                    name: "Emp1",
                    xValue: new[] { "Peter", "Frank", "Bob", "Ann" },
                    yValues: new[] { "2", "4", "3", "7" }
                );
            return File(key.ToWebImage().GetBytes(), "image/jpeg");
        }
        public ActionResult Chart2()
        {
            var key = new Chart(width: 300, height: 300)
                .AddTitle("Employee 2")
                .AddSeries(
                    chartType: "Column",
                    name: "Emp2",
                    xValue: new[] { "Peter", "Frank", "Bob", "Ann" },
                    yValues: new[] { "2", "4", "3", "7" }
                );
            return File(key.ToWebImage().GetBytes(), "image/jpeg");
        }

2. View

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


<img src="@Url.Action("Chart1")" />
<img src="@Url.Action("Chart2")" />

MVC - Chart - Display Multiple Series

Watch this example on YouTube


1. Add to Home Controller:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.IO;
using System.Text;
using System.Drawing;
using System.Collections;
using System.Web.Helpers;

namespace WebApplication3.Controllers
{
    public class HomeController : Controller
    {
      
        public ActionResult ChartWithMultipleValues()
        {
            Chart key = new Chart(width: 500, height: 500)
                .AddTitle("Employee Chart")
                .AddLegend("Some legend")
                .SetYAxis("Percentage Value", 0, 100)
                .AddSeries(
                    chartType: "Column",
                    name: "January",
                    xValue: new[] { "Bob", "Ivan", "Frank", "Anna" },
                    yValues: new[] { "33", "44", "22", "88" }
                )
                .AddSeries(
                    name: "February",
                    yValues: new[] { "99", "78", "88", "33" }
                );
            return File(key.ToWebImage().GetBytes(), "image/jpeg");
        }

2. View

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

<img src="@Url.Action("ChartWithMultipleValues")" />

MVC - C# - How to remove duplicates from the array

Watch this example on YouTube


   
        int[] a = { 1, 2, 3, 2, 3, 1, 5, 5, 6 };
        int[] b = a.Distinct().ToArray();

or 

        int[] a = { 1, 2, 3, 2, 3, 1, 5, 5, 6 };
        a = a.Distinct().ToArray();

MVC - Chart - Create Simple Chart and load data from DB - Entity Framework

Watch this example on YouTube


 

1. Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication7.Models;
using System.Collections;
using System.Web.Helpers;


namespace WebApplication7.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();

        public ActionResult GetChartImage()
        {
            ArrayList xValue = new ArrayList();
            ArrayList yValue = new ArrayList();
            var results = db.GetProducts().ToList();
            results.ToList().ForEach(rs => xValue.Add(rs.Month));
            results.ToList().ForEach(rs => yValue.Add(rs.Amount));
            var key = new Chart(width: 300, height: 300)
                .AddTitle("Months")
                .AddSeries(chartType: "Pie",
                name: "Some Name",
                xValue: xValue,
                yValues: yValue);
            return File(key.ToWebImage().GetBytes(), "image/jpeg");
        }

        public ActionResult Index()
        {
            return View();
        }

2. View

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


<img src="@Url.Action("GetChartImage")" />

MVC - Create simple Chart

Watch this example on YouTube


 
1. Home Controller

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

namespace WebApplication7.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult GetChartImage()
        {
            var key = new Chart(width: 300, height: 300)
                .AddTitle("Employee Chart")
                .AddSeries(
                chartType: "Bubble",
                name: "Employee",
                xValue: new[] {"Frank", "John", "Bob", "Ann"},
                yValues: new[] {"33", "22", "55", "44"}
                );
            return File(key.ToWebImage().GetBytes(), "image/jpeg");
        }

        public ActionResult Index()
        {
            return View();
        }


2. View

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


<img src="@Url.Action("GetChartImage")" />

MSSQL - Fix Error - The server principal 'LIVING\Administrator' already exists.

Msg 15025, Level 16, State 2, Line 1
The server principal 'LIVING\Administrator' already exists.

Watch this example on YouTube

Fix Error - The type or namespace name 'Chart' could not be found (are you missing a using directive or an assembly reference?)

Error    CS0246    The type or namespace name 'Chart' could not be found (are you missing a using directive or an assembly reference?)  

Watch this example on YouTube


To fix it add
using System.Web.Helpers;

Microsoft Excel - Remove duplicates from colum row or cell

Watch this example on YouTube


1. select column row or cell
2. Go to Data -> Data Tools -> Remove Duplicates

Microsoft Excel - How to setup column or cell or row that allows only positive numbers

Watch this example on YouTube


1. Highlight column or row or cell
2. Go to Data -> Data Tools -> Data Validation
3. Data Validation -> settings -> Allow: Decimal, Greater Than, Minimum:0
4. Error Alert -> error message -> enter error message here

Microsoft Excel - Compare if 2 cells are the same

Watch this example on YouTube

This formula will check if 2 cells are the same:
=IF(EXACT(A1, B1), "Yes", "No")

MVC - Charting - Display multiple series using data from SqlServer( Entity Framework)

Watch this example on YouTube


1. Table 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ChartTest](
    [ProductName] [varchar](50) NULL,
    [Month] [varchar](50) NULL,
    [Amount] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Apple', N'Jan', 20)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Apple', N'Feb', 40)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Apple', N'Mar', 22)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Apple', N'Apr', 44)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Orange', N'Jan', 55)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Orange', N'Feb', 22)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Orange', N'Mar', 33)
GO
INSERT [dbo].[ChartTest] ([ProductName], [Month], [Amount]) VALUES (N'Orange', N'Apr', 88)
GO

2. Stored Procedure

CREATE PROCEDURE [dbo].[GetProducts]

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 * FROM ChartTest
END


3. Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI.DataVisualization.Charting;
using System.IO;
using System.Text;
using System.Drawing;
using WebApplication4.Models;

namespace WebApplication4.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();

        public ActionResult ChartMultiColumns()
        {
            var dataAll = db.GetProducts().ToList();
            var months = from d in dataAll orderby d.Month group d by d.Month into m select m.Key;
            var chart = new Chart();
            foreach(var mn in months)
            {
                var data = from d in dataAll where d.Month == mn select d;
                var area = new ChartArea();
                chart.ChartAreas.Add(area);
                var series = new Series();
                foreach(var item in data)
                {
                    series.Points.AddXY(item.ProductName, item.Amount);
                }
                series.ChartType = SeriesChartType.Column;
                series.Name = data.First().Month;
                chart.Series.Add(series);
            }
            Legend chartLegend = new Legend();
            chartLegend.Name = "Result";
            chartLegend.LegendStyle = LegendStyle.Column;
            chart.Legends.Add(chartLegend);

            var returnStream = new MemoryStream();
            chart.ImageType = ChartImageType.Png;
            chart.SaveImage(returnStream);
            returnStream.Position = 0;
            return new FileStreamResult(returnStream, "image/png");
        }


4. View

@{
    ViewBag.Title = "ChartMultiColumns";
}

<h2>ChartMultiColumns</h2>

<img src="@Url.Action("ChartMultiColumns")" />

MVC - Charting - Create simple chart from database using Entity Framework - Step By Step

Watch this example on YouTube:



1. Table

USE [Company]
GO

/****** Object:  Table [dbo].[ChartTest]    Script Date: 2019-05-18 3:31:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ChartTest](
    [ProductName] [varchar](50) NULL,
    [Month] [varchar](50) NULL,
    [Amount] [int] NULL
) ON [PRIMARY]

GO

2. Stored Procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE GetProducts

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 * FROM ChartTest
END
GO

3. Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication3.Models;
using System.Web.UI.DataVisualization.Charting;
using System.IO;
using System.Text;
using System.Drawing;

namespace WebApplication3.Controllers
{
    public class HomeController : Controller
    {
        CompanyEntities db = new CompanyEntities();

        public ActionResult ChartFromEF()
        {
            var data = db.GetProducts().ToList();
            var chart = new Chart();
            var area = new ChartArea();
            chart.ChartAreas.Add(area);
            var series = new Series();
            foreach(var item in data)
            {
                series.Points.AddXY(item.ProductName, item.Amount);
            }
            series.Label = "#PERCENT{P0}";
            series.Font = new Font("Arial", 8.0f, FontStyle.Bold);
            series.ChartType = SeriesChartType.Column;
            series["PieLabelStyle"] = "Outside";
            chart.Series.Add(series);
            var returnStream = new MemoryStream();
            chart.ImageType = ChartImageType.Png;
            chart.SaveImage(returnStream);
            returnStream.Position = 0;
            return new FileStreamResult(returnStream, "image/png");
        }

4. View

@{
    ViewBag.Title = "ChartFromEF";
}

<h2>ChartFromEF</h2>

<img src="@Url.Action("ChartFromEF")" />

MVC - Charting - Create simple chart

Watch this example on YouTube











Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI.DataVisualization.Charting;
using System.IO;
using System.Text;
using System.Drawing;

namespace MVCCharting.Controllers
{
    public class HomeController : Controller
    {

        public ActionResult Chrt1()
        {
            var data = new Dictionary<string, float>
            {
                {"test1", 10.1f },
                {"test2", 1.1f },
                {"test3", 22.3f },
                 {"test4", 30.1f },
                {"test5", 13.1f },
                {"test6", 82.3f }
            };
            var chart = new Chart();
            var area = new ChartArea();
            chart.ChartAreas.Add(area);
            var series = new Series();
            foreach (var item in data)
            {
                series.Points.AddXY(item.Key, item.Value);
            }
            series.Label = "Some Label";
            series.Font = new Font("Arial", 10.0f, FontStyle.Bold);
            series.ChartType = SeriesChartType.Column;
            series["PieLabelStyle"] = "Outside";
            chart.Series.Add(series);
            var returnStream = new MemoryStream();
            chart.ImageType = ChartImageType.Png;
            chart.SaveImage(returnStream);
            returnStream.Position = 0;
            return new FileStreamResult(returnStream, "image/png");
        }

View

@{
    ViewBag.Title = "Chrt1";
}

<h2>Chrt1</h2>

<img src="@Url.Action("Chrt1")" />

MVC C# .NET - Fix Error - The type or namespace name 'DataVisualization' does not exist in the namespace 'System.Web.UI' (are you missing an assembly reference?)

Error    CS0234    The type or namespace name 'DataVisualization' does not exist in the namespace 'System.Web.UI' (are you missing an assembly reference?)   

Watch this example on YouTube



when trying to use
using System.Web.UI.DataVisualization.Charting;

MSSQL - Create table that allows to have only 1 (one) row

watch this example on YouTube

The following table will not allow user to enter more than 1 record

CREATE Table SomeTable(
    Locking Char(1) not null,
    SomeID int,
    constraint PK_SomeTable Primary Key (Locking),
    constraint CK_SomeTable_Locked Check (Locking='X')
)
Insert into SomeTable (Locking, SomeID) Values ('X', 1)
Select * From SomeTable

MSSQL - Fix Error - The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Msg 3902, Level 16, State 1, Line 13
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.


Watch this example on YouTube


To fix it replace ( add IF @@TRANCOUNT > 0)

Declare @Error Varchar(max)
Declare @ID int = 0

BEGIN TRAN
    Insert into ProductHistory (NewName) values ('test')
    IF @ID = 0
    BEGIN
        SET @Error = 'some error'
        ROLLBACK TRAN
    END
    Select * From ProductHistory
COMMIT TRAN

with

Declare @Error Varchar(max)
Declare @ID int = 0

BEGIN TRAN
    Insert into ProductHistory (NewName) values ('test')
    IF @ID = 0
    BEGIN
        SET @Error = 'some error'
        ROLLBACK TRAN
    END
    Select * From ProductHistory
IF @@TRANCOUNT > 0
COMMIT TRAN

MSSQL - Check stored procedure's last modified date

Watch this example on YouTube


SELECT name, create_date, modify_date
FROM sys.objects
WHERE Type='P' and name = 'PROCEDURE NAME'
ORDER BY modify_date DESC

MSSQL - Fix Error Column names in each table must be unique. Column name 'col1' in table 'TableTest' is specified more than once.

Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'col1' in table 'TableTest' is specified more than once.

Watch this example on YouTube

to fix it replcae

ALTER TABLE TableTest
Add col1 int
Go

with

ALTER TABLE TableTest
Add col1UNIQUE int
Go

MSSQL - Fix Error - Cannot use a BREAK statement outside the scope of a WHILE statement.

Msg 135, Level 15, State 1, Line 4
Cannot use a BREAK statement outside the scope of a WHILE statement.

Watch this example on YouTube

To Fix it replace

Declare @Num1 int = 10
If @Num1 = 20
BEGIN
    BREAK
END
While (ISNULL(@Num1, 0) < 10)
BEGIN
    Select GetDate()
END

with

Declare @Num1 int = 10
If @Num1 = 20
BEGIN
    RETURN
END
While (ISNULL(@Num1, 0) < 10)
BEGIN
    Select GetDate()
END

MSSQL - Fix Error - ALTER TABLE ALTER COLUMN Col1 failed because one or more objects access this column.

Msg 5074, Level 16, State 1, Line 1
The index 'IX_TableWithIndexTest' is dependent on column 'Col1'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Col1 failed because one or more objects access this column.

Watch this example on YouTube



To Fix it replace

Alter Table TableWithIndexTest
Alter Column Col1 nvarchar(max) null
GO

with (remove index related to the column you want to alter)

Drop Index TableWithIndexTest.IX_TableWithIndexTest
GO

Alter Table TableWithIndexTest
Alter Column Col1 nvarchar(max) null
GO

MSSQL - Fix Error - Must specify the table name and index name for the DROP INDEX statement.

Msg 159, Level 15, State 1, Line 1
Must specify the table name and index name for the DROP INDEX statement.


Watch this example on YouTube



To fix it replace

Drop Index IX_IndexTest
GO

with

Drop TABLE_NAME.Index IX_IndexTest
GO