How to do - MS SQL C# Excel Access: 2016

Monday 5 December 2016

R Language - Simple Example - Open Excel file and display its content

watch this example on YouTube

Download/install R
Download/install Strawberry
Execute the following

install.packages(pkgs="gdata")

_________________________________________________________________

to open/read data to variable

> myvar <- read.xls("C://test//Excel//test.xlsx")

to display data from myvar

>myvar

Friday 2 December 2016

MVC - change width of EditorFor

Watch this example on YouTube:

add the following to css class
.width200{
width: 200px;
}

now ensure EditorFor is pointing to the class above
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "width200" } })

Wednesday 30 November 2016

MVC - TextBoxFor - Set default value

Watch this example on YouTube

Replace
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })

With
@Html.TextBoxFor(model => model.FirstName, new { @class = "form-control" , @Value="NO NAME" })

MVC - Set width of TextBox

Watch this exampel on YouTube

in .css add the following
.width200{
    width: 200px;
}

in view replace

   @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
with
   @Html.TextBoxFor(model => model.FirstName, new { htmlAttributes = new { @class = "width200" } })

MVC - Table - display No Data found message if empty table

Watch this example on YouTube:

Replace

@model IEnumerable<MVCTest.Models.Customer>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.FirstName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.LastName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Address)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model)
{
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.FirstName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.LastName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Address)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
            @Html.ActionLink("Details", "Details", new { id = item.ID }) |
            @Html.ActionLink("Delete", "Delete", new { id = item.ID })
        </td>
    </tr>
}

</table>

with

@model IEnumerable<MVCTest.Models.Customer>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
@if (Model.Count() > 0)
{

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.FirstName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.LastName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Address)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model)
{
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.FirstName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.LastName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Address)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id = item.ID }) |
            @Html.ActionLink("Details", "Details", new { id = item.ID }) |
            @Html.ActionLink("Delete", "Delete", new { id = item.ID })
        </td>
    </tr>
}

</table>
}
else{
    <h2>No Data Found</h2>
}

Wednesday 2 November 2016

Fix - MVC Error: 'object' does not contain a definition for ''

Watch this example on YouTube

To fix it replace
@Html.TextBoxFor(model => model.CustomerID, new object { @class = "WidthLong" })
With
@Html.TextBoxFor(model => model.CustomerID, new { @class = "WidthLong" })

Watch this on YouTube:

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

To fix it - replace
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
</head>
<body>
    <form id="form1" runat="server">
        <script type="text/javascript">
            $(document).ready(function () {
                $('.rb').click(function () {
                    if ($('#RadioButton1').checked()) {
                        alert('radio button 1 checked');
                    } else {
                        alert('radio button 2 checked');
                    }

                });
            });
        </script>
    <div>
        <asp:RadioButton ID="RadioButton1" runat="server" GroupName="GroupOne" />
        <asp:RadioButton ID="RadioButton2" runat="server" GroupName="GroupOne"/>
        <asp:Button ID="Button1" class="rb" runat="server" Text="Button" />
    </div>
    </form>
</body>

With
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
</head>
<body>
    <form id="form1" runat="server">
        <script type="text/javascript">
            $(document).ready(function () {
                $('.rb').click(function () {
                    if ($('#RadioButton1').attr('checked')) {
                        alert('radio button 1 checked');
                    } else {
                        alert('radio button 2 checked');
                    }

                });
            });
        </script>
    <div>
        <asp:RadioButton ID="RadioButton1" runat="server" GroupName="GroupOne" />
        <asp:RadioButton ID="RadioButton2" runat="server" GroupName="GroupOne"/>
        <asp:Button ID="Button1" class="rb" runat="server" Text="Button" />
    </div>
    </form>
</body>

jQuery - fix error - 0x800a1391 - JavaScript runtime error: '$' is undefined

Watch this example on YouTube

To fix it Replace

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <script type="text/javascript">
            $(document).ready(function () {
                $('.rb').click(function () {
                    alert('clicked');
                });
            });
        </script>
    <div>

    </div>
    </form>
</body>
</html>

With

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
</head>
<body>
    <form id="form1" runat="server">
        <script type="text/javascript">
            $(document).ready(function () {
                $('.rb').click(function () {
                    alert('clicked');
                });
            });
        </script>
    <div>

    </div>
    </form>
</body>
</html>

ASP.Net - fix error - Cannot have multiple items selected in a DropDownList.

Cannot have multiple items selected in a DropDownList.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Watch this example on YouTube:

Here is my HTML:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem>ONE</asp:ListItem>
            <asp:ListItem>TWO</asp:ListItem>
            <asp:ListItem>THREE</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="Button1" runat="server" Text="Button" />
    </div>
    </form>
</body>
</html>

And here is my code behind

        protected void Page_Load(object sender, EventArgs e)
        {
            ListItem lst = DropDownList1.Items.FindByText("ONE");
            if (lst != null)
            {
                lst.Selected = true;
            }
        }

To fix it replace
lst.Selected = true;
With
                DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf(DropDownList1.Items.FindByText("ONE"));

VB.NET - Enum - get number of elements (lenght of enum)

Watch this example on YouTube

    Public Enum SomeEnum
        Bob = 1
        Frank = 2
        Gary = 3
        Taras = 4
        Oleh = 5
    End Enum

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim num As Integer = [Enum].GetNames(GetType(SomeEnum)).Length
        MessageBox.Show(num)
    End Sub

Monday 31 October 2016

Fix Error - A potentially dangerous Request.Form value was detected from the client ().

Watch this example on YouTube

Fix error
A potentially dangerous Request.Form value was detected from the client (CustomerFirstName="<B>").

to fix it add the following in Controller

        [HttpPost]
        [ValidateAntiForgeryToken]
        [ValidateInput(false)]
        public ActionResult Create(Customers2 customers2)
        {

Fix Error - Non-invocable member 'System.Configuration.ConfigurationManager.ConnectionStrings' cannot be used like a method.

C# - Non-invocable member 'System.Configuration.ConfigurationManager.ConnectionStrings' cannot be used like a method.

Watch this example on YouTube

To fix it replace
System.Configuration.ConfigurationManager.ConnectionStrings("DefaultConnection").ToString();
With
System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();

Visual Studio - Fix issue - Intellisense not working

Watch this example on YouTube

To Fix it go to
Tools
Options
Text Editor
All Languages
General
make sure both
Auto list numbers
and
Parameter information are checked (not half checked)

Saturday 29 October 2016

jQuery - Check if control is visible

watch this example on YouTube

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            if ($('#Button1').is(":visible")) {
                alert('visible');
            } else {
                alert('not visible');
            }
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Visible="true" Text="Button" />
    </div>
    </form>
</body>
</html>

jQuery - Check if control exists

Watch this example on YouTube:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            jQuery.fn.exists = function () { return this.length > 0; }
            if ($('#Button1').exists()) {
                alert('control exists');
            }
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="Button" />
    </div>
    </form>
</body>
</html>

jQuery - same function used by multiple IDs (multiple controls)

Watch this example on YouTube

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            $('#Button1, #TextBox1 , #CheckBox1').click(function () {
                alert('control clicked');
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" class='rb' runat="server" Text="Button" />
        <asp:CheckBox ID="CheckBox1" class='rb' runat="server" />
        <asp:TextBox ID="TextBox1" class='rb' runat="server"></asp:TextBox>
    </div>
    </form>
</body>
</html>

jQuery - How to get selected text of dropdownlist

Watch this example on YouTube:

<head runat="server">
    <title></title>

    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            $('.rb').click(function () {
                alert($("#DropDownList1").find("option:selected").text());
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem>ONE</asp:ListItem>
            <asp:ListItem>TWO</asp:ListItem>
            <asp:ListItem>THREE</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="Button1" class="rb" runat="server" Text="Button" />
    </div>
    </form>
</body>
</html>

jQuery - Get by ID with space

jQuery - exclude all non numeric charachters in string

Thursday 27 October 2016

SQL Server - fix error - Incorrect syntax near '.'

Watch this example on YouTube
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

To fix it replace

Select Customers.CustomerID Customers.CustomerFirstName from Customers

with (add comma)

Select Customers.CustomerID, Customers.CustomerFirstName from Customers

SQL Server - Fix error - Msg 208 Invalid object name

Watch this example on YouTube
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Country'.

To fix it replace

Select * from Country

with
Select * from Test.dbo.Country

Wednesday 26 October 2016

VB.NET - Fix Error - Type 'Stream' is not defined.

Watch this example on YouTube

To fix it add
Imports System.IO

jQuery - Fix the following error: 0x800a138b - JavaScript runtime error: Cannot assign to a function result

Watch this example on YouTube:

Error:
0x800a138b - JavaScript runtime error: Cannot assign to a function result

To fix it replace
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            $('.rb').click(function () {
                $('#TextBox1').val()='';
                $('#TextBox2').val()='';
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:RadioButton ID="RadioButton1" runat="server" class="rb" GroupName="test" />
        <asp:RadioButton ID="RadioButton2" runat="server" class="rb" GroupName="test" />
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
    </div>
    </form>
</body>
</html>

With

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <script type="text/javascript" src="Scripts/jquery-1.10.2.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            $('.rb').click(function () {
                $('#TextBox1').val()='';
                $('#TextBox2').val()='';
                $('#TextBox1').val('');
                $('#TextBox2').val('');
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:RadioButton ID="RadioButton1" runat="server" class="rb" GroupName="test" />
        <asp:RadioButton ID="RadioButton2" runat="server" class="rb" GroupName="test" />
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
    </div>
    </form>
</body>
</html>

Tuesday 25 October 2016

Fix the following error - -'AsEnumerable' is not a member of 'System.Data.DataTable'.

Watch this example on YouTube

-'AsEnumerable' is not a member of 'System.Data.DataTable'.

to fix it add reference to System.Data.DataSetExtensions

Monday 24 October 2016

Fix Error - 'Compute' is not a member of 'System.Data.DataRow'.

Watch this example on YouTube
Fix Error 'Compute' is not a member of 'System.Data.DataRow'.

To fix it replace

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ds As DataSet = LoadData()
        Dim query = From rec In ds.Tables(0).AsEnumerable() Select rec
        If ds.Tables(0).Rows.Count > 0 Then
            For Each r As DataRow In query
                Dim s As Integer = IIf(r.Compute("Sum(Age)", "Valid = '1'") Is DBNull.Value, 0, r.Compute("Sum(Age)", "Valid = '1'"))
                MessageBox.Show(s.ToString)
            Next
        End If
    End Sub

    Public Function LoadData() As DataSet
        Dim ds As New DataSet
        Dim dt As New DataTable
        dt.Columns.Add("Valid")
        dt.Columns.Add("Age", Type.GetType("System.Int32"))
        dt.Rows.Add(New Object() {"1", 33})
        dt.Rows.Add(New Object() {"2", 44})
        dt.Rows.Add(New Object() {"1", 22})
        dt.Rows.Add(New Object() {"1", 22})
        ds.Tables.Add(dt)
        Return ds
    End Function

With

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ds As DataSet = LoadData()
        Dim query = From rec In ds.Tables(0).AsEnumerable() Select rec
        If ds.Tables(0).Rows.Count > 0 Then
            For Each r As DataRow In query
                Dim s As Integer = IIf(r.Table.Compute("Sum(Age)", "Valid = '1'") Is DBNull.Value, 0, r.Table.Compute("Sum(Age)", "Valid = '1'"))
                MessageBox.Show(s.ToString)
            Next
        End If
    End Sub

    Public Function LoadData() As DataSet
        Dim ds As New DataSet
        Dim dt As New DataTable
        dt.Columns.Add("Valid")
        dt.Columns.Add("Age", Type.GetType("System.Int32"))
        dt.Rows.Add(New Object() {"1", 33})
        dt.Rows.Add(New Object() {"2", 44})
        dt.Rows.Add(New Object() {"1", 22})
        dt.Rows.Add(New Object() {"1", 22})
        ds.Tables.Add(dt)
        Return ds
    End Function

Fix Error - Additional information: Invalid usage of aggregate function Sum() and Type: String.

Watch this example on YouTube
Error - Additional information: Invalid usage of aggregate function Sum() and Type: String.

To fix replace:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ds As DataSet = LoadData()
        Dim query = From rec In ds.Tables(0).AsEnumerable() Select rec
        If ds.Tables(0).Rows.Count > 0 Then
            For Each r As DataRow In query
                Dim s As Integer = IIf(r.Table.Compute("Sum(Age)", "Valid = '1'") Is DBNull.Value, 0, r.Table.Compute("Sum(Age)", "Valid = '1'"))
                MessageBox.Show(s.ToString)
            Next
        End If
    End Sub

    Public Function LoadData() As DataSet
        Dim ds As New DataSet
        Dim dt As New DataTable
        dt.Columns.Add("Valid")
        dt.Columns.Add("Age")
        dt.Rows.Add(New Object() {"1", 33})
        dt.Rows.Add(New Object() {"2", 44})
        dt.Rows.Add(New Object() {"1", 22})
        dt.Rows.Add(New Object() {"1", 22})
        ds.Tables.Add(dt)
        Return ds
    End Function

With

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ds As DataSet = LoadData()
        Dim query = From rec In ds.Tables(0).AsEnumerable() Select rec
        If ds.Tables(0).Rows.Count > 0 Then
            For Each r As DataRow In query
                Dim s As Integer = IIf(r.Table.Compute("Sum(Age)", "Valid = '1'") Is DBNull.Value, 0, r.Table.Compute("Sum(Age)", "Valid = '1'"))
                MessageBox.Show(s.ToString)
            Next
        End If
    End Sub

    Public Function LoadData() As DataSet
        Dim ds As New DataSet
        Dim dt As New DataTable
        dt.Columns.Add("Valid")
        dt.Columns.Add("Age", Type.GetType("System.Int32"))
        dt.Rows.Add(New Object() {"1", 33})
        dt.Rows.Add(New Object() {"2", 44})
        dt.Rows.Add(New Object() {"1", 22})
        dt.Rows.Add(New Object() {"1", 22})
        ds.Tables.Add(dt)
        Return ds
    End Function

Wednesday 10 August 2016

MSSQL - Query to calculate Weighted Average (Like SumProduct in Excel)

watch this example on YOUTUBE

Select Sum(Cast (Col1 as float) * Cast(Col2 as float)) /Sum(Cast(Col1 as float))
From WeightAverage

MSSQL - Query that loads data by 15 minutes intervals

Query to group by time intervals - group by 15 minutes time intervals

Watch this example on YouTube
select count(id) as NumberOfOrders,
DatePart(Year, MyDate) as TransactionYear,
DatePart(MONTH, MyDate) as TransactionMonth,
DatePart(DAY, MyDate) As TransactionDate,
DatePart(Hour, MyDate) As TransactionHour,
DatePart(Minute, MyDate)/15 As TransactionInterval,
Convert(Varchar, DatePart(Hour, MyDate)) + Case DatePart(Minute, MyDate) /15 When 0 Then ':00' When 1 Then ':15'
When 2 Then ':30' When 3 Then ':45' End as TransInterval
From IntervalTest
Group By
DatePart(Year, MyDate) ,
DatePart(MONTH, MyDate),
DatePart(DAY, MyDate),
DatePart(Hour, MyDate) ,
DatePart(Minute, MyDate)/15

Friday 1 July 2016

JavaScript - check if date is valid

Watch this example on YouTube

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="JavaScript Check if date is valid.aspx.cs" Inherits="WebTest.JavaScript_Check_if_date_is_valid" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
        if (Date.parse("1/1/2000")) {
            alert("Valid Date")
        }
        else {
            alert("Invalid Date")
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="Button" OnClientClick="ValidateDate();" />
    </div>
    </form>
</body>
</html>

ASP.NET - IE 10 - Fix Error - csv couldn't be downloaded

Watch this example on youtube:

Replace

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < 200000; i++)
            {
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");

                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + Environment.NewLine);
            }
            string sHeader = "attachement; filename=test.csv";
            var exc = HttpContext.Current.Response;
            exc.Clear();
            exc.ContentType = "application/octet-stream";
            exc.ContentEncoding = System.Text.Encoding.Default;
            exc.AppendHeader("Content-Disposition", sHeader);
            exc.Write(sb.ToString());
            exc.Flush();
            exc.Clear();
            exc.Close();

With

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < 200000; i++)
            {
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");

                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + ",");
                sb.Append("some data here " + Environment.NewLine);
            }

            string sHeader = "attachement; filename=test.csv";
            var exc = HttpContext.Current;
            exc.Response.Clear();
            exc.Response.ContentType = "application/octet-stream";
            exc.Response.ContentEncoding = System.Text.Encoding.Default;
            exc.Response.AppendHeader("Content-Disposition", sHeader);
            exc.Response.Write(sb.ToString());
            exc.Response.Flush();
            exc.Response.End();
            exc.ApplicationInstance.CompleteRequest();

Thursday 23 June 2016

Fix the following Error Operator '&&' cannot be applied to operands of type 'System.Linq.IQueryable<>' and 'lambda expression'

Watch this example on YouTube:

Replace

      public ActionResult TestPage(TESTEntities model = null)
        {
            var test = (from z in model.Customers2
                        select z);
            if (!String.IsNullOrEmpty(model.Customers2.First().CustomerFirstName))
            {
                test = model.Customers2.Where(s => s.CustomerFirstName != null) &&
                   (s => s.FirstOrDefault().ToString().Trim().Equals(model.Customers2.FirstOrDefault().CustomerFirstName.Trim()));
            }
            return View();
        }

With

      public ActionResult TestPage(TESTEntities model = null)
        {
            var test = (from z in model.Customers2
                        select z);
            if (!String.IsNullOrEmpty(model.Customers2.First().CustomerFirstName))
            {
                //test = model.Customers2.Where(s => s.CustomerFirstName != null) &&
                 //   (s => s.FirstOrDefault().ToString().Trim().Equals(model.Customers2.FirstOrDefault().CustomerFirstName.Trim()));
                test = model.Customers2.Where(s => s.CustomerFirstName == null ? false : s.CustomerFirstName.Equals("AAA"));
            }
            return View();
        }

Monday 20 June 2016

MSSQL - Update Date Only - Keep orgial time

Watch this example on YouTube

Update TestTable Set
OrderDate = DateAdd(day, DateDiff(day, OrderDate, '1/1/2016'), OrderDate)

Thursday 26 May 2016

MVC - Fix error The name 'Path' does not exist in the current context

Watch this example on YouTube:
to fix it replace
        public ActionResult ImportFile(HttpPostedFileBase file)
        {
            if (Request.Files.Count > 0)
            {
                var fil = Request.Files[0];
                if (fil != null && fil.ContentLength > 0)
                {
                    var fileName = Path.GetFileName(file.FileName);
                    var path = Path.Combine(Server.MapPath("~/Images/"), fileName);
                    file.SaveAs(path);
                }
            }
            return View();
with
        public ActionResult ImportFile(HttpPostedFileBase file)
        {
            if (Request.Files.Count > 0)
            {
                var fil = Request.Files[0];
                if (fil != null && fil.ContentLength > 0)
                {
                    var fileName = System.IO.Path.GetFileName(file.FileName);
                    var path = System.IO.Path.Combine(Server.MapPath("~/Images/"), fileName);
                    file.SaveAs(path);
                }
            }
            return View();

Saturday 21 May 2016

MVC - Fix the following error - The type or namespace name 'SelectListItem' could not be found (are you missing a using directive or an assembly reference?)

Watch this example on YouTube:
Error while executing the following code:
        public IEnumerable<SelectListItem> Months
        {
            get
            {
                return DateTimeFormatInfo
                       .InvariantInfo
                       .MonthNames
                       .Select((monthName, index) => new SelectListItem
                       {
                           Value = (index + 1).ToString(),
                           Text = monthName
                       });
            }
        }

To fix it add the following using:
using System.Globalization;

MVC - Fix the following error - Error The name 'DateTimeFormatInfo' does not exist in the current context

Watch this on YouTube:
error while executing the following code:
public IEnumerable<SelectListItem> Months
        {
            get
            {
                return DateTimeFormatInfo
                       .InvariantInfo
                       .MonthNames
                       .Select((monthName, index) => new SelectListItem
                       {
                           Value = (index + 1).ToString(),
                           Text = monthName
                       });
            }
        }
To fix it - add the following using:
using System.Globalization;

Wednesday 18 May 2016

MVC - Sorting Paging Filtering using ViewModel (not ViewBag)

watch this example on YouTube

SortAndPage class
namespace MvcSortAndFilterAndPage.Models
{
    public class SortAndPage
    {
        public string SortField { get; set; }
        public string SortDirection { get; set; }
        public int PageSize { get; set; }
        public int PageCount { get; set; }
        public int CurrentPageIndex { get; set; }
    }
}
Customer Custom class
namespace MvcSortAndFilterAndPage.Models
{
    public class CustomerCustom :SortAndPage
    {
        public IEnumerable<Customers2> cust { get; set; }
        public IEnumerable<Customers2> custDDL { get; set; }
        public string SelectedFirstName { get; set; }
        public string SelectedLastName { get; set; }
    }
}

add the following to _layout.cshtml
        @Scripts.Render("~/Scripts/jquery-1.8.2.min.js")
        @Scripts.Render("~/Scripts/jquery.unobtrusive-ajax.js")
    </head>

Here is modified part of CustomerController
using MvcSortAndFilterAndPage.Models;

namespace MvcSortAndFilterAndPage.Controllers
{
    public class CustomerController : Controller
    {
        private TESTEntities db = new TESTEntities();

        //
        // GET: /Customer/

        public ActionResult Index(CustomerCustom model = null)
        {
            int i;
            if (model != null)
            {
                i = model.CurrentPageIndex;
            }
            model = new CustomerCustom
            {
                cust = db.Customers2.ToList(),
                custDDL = db.Customers2.ToList()
            };
            var res = (from s in model.cust
                       select s);
            res = res.ToList();
            if (model.CurrentPageIndex == 0)
            {
                model.CurrentPageIndex = 0;
            }
            model.PageSize = 2;
            model.PageCount = ((res.Count() + model.PageSize - 1) / model.PageSize);
            if (model.CurrentPageIndex > model.PageCount)
            {
                model.CurrentPageIndex = model.PageCount;
            }
            model.cust = res.Skip(model.CurrentPageIndex * model.PageSize).Take(model.PageSize);

            return View(model);
        }

        [HttpPost]
        public ActionResult Index(CustomerCustom model, string btn = null){
            if (model.SortField == null){
                model.SortField = "CustomerFirstName";
                model.SortDirection = "ascending";
            }
            #region SortData

            switch(model.SortField){
                case "CustomerFirstName":
                    model.cust = (model.SortDirection == "ascending" ?
                        db.Customers2.OrderBy(c => c.CustomerFirstName):
                        db.Customers2.OrderByDescending(c => c.CustomerFirstName));
                    break;
                case "CustomerLastName":
                    model.cust = (model.SortDirection == "ascending" ?
                        db.Customers2.OrderBy(c => c.CustomerLastName):
                        db.Customers2.OrderByDescending(c => c.CustomerLastName));
                    break;
            }

            #endregion

            var ddl = (from d in model.cust
                       select d);
            model.custDDL = ddl;

            #region FilterData

            if (!String.IsNullOrEmpty(model.SelectedFirstName))
            {
                model.cust = model.cust.Where(s => s.CustomerFirstName.ToString().Trim().Equals(model.SelectedFirstName.Trim()));
            }
            if(!String.IsNullOrEmpty(model.SelectedLastName))
            {
                model.cust = model.cust.Where(s => s.CustomerLastName.ToString().Trim().Equals(model.SelectedLastName.Trim()));
            }

            #endregion

            var res = (from s in model.cust
                           select s);
            res = res.ToList();
            if(model.CurrentPageIndex == 0){
                model.CurrentPageIndex = 0;
            }
            model.PageSize =2;
            model.PageCount = ((res.Count() + model.PageSize - 1) / model.PageSize);
            if(model.CurrentPageIndex > model.PageCount){
                model.CurrentPageIndex = model.PageCount;
            }
            model.cust = res.Skip(model.CurrentPageIndex * model.PageSize).Take(model.PageSize);
            return View(model);
        }
View(Index.cshtml) will look like this:
@model MvcSortAndFilterAndPage.Models.CustomerCustom

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>

@using (Html.BeginForm(null, null, FormMethod.Post))
{
    @Html.AntiForgeryToken()
    @Html.Hidden("SortField", Model.SortField)
    @Html.Hidden("SortDirection", Model.SortDirection)
    @Html.Hidden("PageCount", Model.PageCount)
    @Html.Hidden("PageSize", Model.PageSize)
    @Html.Hidden("CurrentPageIndex", Model.CurrentPageIndex)

    @Html.Hidden("SelectedFirstName", Model.SelectedFirstName)
    @Html.Hidden("SelectedLastName", Model.SelectedLastName)

<table>
    <tr>
        <th>
            <a href="#" data-sortfield="CustomerFirstName" class="header">@Html.DisplayNameFor(model => model.cust.First().CustomerFirstName)</a>
        </th>
        <th>
            <a href="#" data-sortfield="CustomerLastName" class="header">@Html.DisplayNameFor(model => model.cust.First().CustomerLastName)</a>
        </th>
        <th></th>
    </tr>
    <tr>
        <th>
            @Html.DropDownListFor(model => model.SelectedFirstName,
            new SelectList(Model.custDDL, "CustomerFirstName", "CustomerFirstName", Model.SelectedFirstName), "All", new {@id = "fn" })
        </th>
        <th>
            @Html.DropDownListFor(model => model.SelectedLastName,
            new SelectList(Model.custDDL, "CustomerLastName", "CustomerFirstName", Model.SelectedLastName), "All", new { @id = "ln" })
        </th>

    </tr>

@foreach (var item in Model.cust) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.CustomerFirstName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.CustomerLastName)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
            @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
        </td>
    </tr>
}

    <tr>
        <td colspan="4">
            @for (var i = 0; i < Model.PageCount; i++)
            {
                if (i == Model.CurrentPageIndex)
                {
                    <span class="current-pager" id="CurrentPageIndex">@(i + 1)</span>
                }
                else
                {
                    <a href="#" data-pageindex="@i" class="pager">@(i + 1)</a>
                }
            }
        </td>
    </tr>

</table>

}
<script type="text/javascript">
    $(document).ready(function () {
        $(".header").click(function (evt) {
            var sortfield = $(evt.target).data("sortfield");
            if ($("#SortField").val() == sortfield) {
                if ($("#SortDirection").val() == "ascending") {
                    $("#SortDirection").val("descending");
                }
                else{
                    $("#SortDirection").val("ascending");
                }
            }
            else {
                $("#SortField").val(sortfield);
                $("#SortDirection").val("ascending");
            }
            evt.preventDefault();
            $("form").submit();

        });

        $(".pager").click(function (evt) {
            var pageindex = $(evt.target).data("pageindex");
            $("#CurrentPageIndex").val(pageindex);
            evt.preventDefault();
            $("form").submit();
        });

        $("#fn").change(function (evt) {
            $("#SelectedFirstName").val($("#fn").val().trim());
            evt.preventDefault();
            $("form").submit();
        })
        $("#ln").change(function (evt) {
            $("#SelectedLastName").val($("#ln").val().trim());
            evt.preventDefault();
            $("form").submit();
        })
    });
</script>

Thursday 12 May 2016

C# - LINQ to List - Group and Count

Watch this example on YouTube

    public class test
    {
        public string Name { get; set; }
        public int Age { get; set; }
    }

    public partial class LINQ_List_Group_Count_test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            List<test> myList = new List<test>(){
                new test(){Name ="Frank", Age =80},
                new test(){Name = "Bob", Age = 30},
                new test(){Name = "Bob", Age = 50},
                new test(){Name = "John", Age = 40}
            };
            var AllNames = from m in myList select m.Name;
            var UniqueNames = from a in myList
                              group a by a.Name into g
                              select g.Count();
            Response.Write(UniqueNames.Count().ToString());
            Response.Write("<br />");
            Response.Write(AllNames.Count().ToString());

        }