How to do - MS SQL C# Excel Access: May 2015

Saturday, 30 May 2015

C# - ASP.NET - How to fix error - The name 'NumberStyles' does not exist in the current context

Watch this example on YouTube:

Replace
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class ErrorTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            double err;
            bool valid = double.TryParse("3.3", NumberStyles.Currency, CultureInfo.GetCultureInfo("en-us"), out err);
        }
    }
}

With
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;

namespace WebApplication1
{
    public partial class ErrorTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            double err;
            bool valid = double.TryParse("3.3", NumberStyles.Currency, CultureInfo.GetCultureInfo("en-us"), out err);
        }
    }
}

C# - APS.Net - How to fix error - The name 'GetCultureInfo' does not exist in the current context

Watch on YouTube:

Replace

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;

namespace WebApplication1
{
    public partial class ErrorTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            double err;
            bool valid = double.TryParse("3.3", NumberStyles.Currency, GetCultureInfo("en-us"), out err);
        }
    }
}

with

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;

namespace WebApplication1
{
    public partial class ErrorTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            double err;
            bool valid = double.TryParse("3.3", NumberStyles.Currency, CultureInfo.GetCultureInfo("en-us"), out err);
        }
    }
}

Wednesday, 27 May 2015

ASP.NET - DataList with CustomValidator and ValidationSummary

Watch this example on YouTube:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataListWithCustomValidator.aspx.cs" Inherits="WebApplication1.DataListWithCustomValidator" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
        function ValidateNames(source, args) {
            var ctr = 0
            var FirstName = ""
            var LastName = ""
            var dataList = document.getElementById('<%= DataList1.ClientID %>')
            var input = dataList.getElementsByTagName("input")
            var custValidator = document.getElementById('<%= CustomValidator1.ClientID %>')
            var ErrorMessage = ""
            for (var i = 0; i < input.length; i++) {
                if (input[i].type == "text") {
                    if (input[i].id.indexOf("txtFirstName") != -1) {
                        ctr++
                        FirstName = input[i].value
                    }
                    if (input[i].id.indexOf("txtLastName") != -1) {
                        LastName = input[i].value
                        if ((LastName != "" && FirstName == "") || (LastName == "" && FirstName != "")) {
                            args.IsValid = false
                            if (ErrorMessage == "") {
                                ErrorMessage = "Customer Number " + ctr + " - ensure both names are entered";
                            }
                            else {
                                ErrorMessage = ErrorMessage + "\n - Customer Number " + ctr + " - ensure both names are entered";
                            }
                        }
                        FirstName = ""
                        LastName = ""
                    }
                }
            }

            if (!args.IsValid) {
                source.errormessage = ErrorMessage;
                //custValidator.setAttribute("errormessage", ErrorMessage)
            }
        }

    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:DataList ID="DataList1" runat="server" RepeatLayout="Flow" RepeatDirection="Horizontal">
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text="FirstName"></asp:Label>
                    <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
                    <asp:Label ID="Label2" runat="server" Text="LastName"></asp:Label>
                    <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
                    <br />
                </ItemTemplate>
            </asp:DataList>
            <asp:Button ID="Button1" runat="server" Text="Submit" ValidationGroup="test" />
        </div>
        <asp:ValidationSummary ID="ValidationSummary1" runat="server"
            HeaderText="Please, correct the following errors:"
            ShowMessageBox="true" ShowSummary="false" ValidationGroup="test" />
        <asp:CustomValidator ID="CustomValidator1" runat="server" ErrorMessage="" Text="" Display="None"
            ClientValidationFunction="ValidateNames" ValidationGroup="test"></asp:CustomValidator>

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

CODE BEHIND
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
namespace WebApplication1
{
    public partial class DataListWithCustomValidator : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dt = LoadData();
            DataList1.DataSource = dt;
            DataList1.DataBind();

        }

        private DataTable LoadData()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("FName");
            dt.Columns.Add("LName");
            dt.Rows.Add(new object[] { "", "" });
            dt.Rows.Add(new object[] { "", "" });
            dt.Rows.Add(new object[] { "", "" });
            return dt;
        }
    }
}

Tuesday, 26 May 2015

MSSQL - How to fix error - incorrect syntax near try expecting conversation

Watch this example on YouTube:

Error
incorrect syntax near try expecting conversation

or
incorrect syntax near catch expecting conversation

to fix:

Replace
BEGIN TRY
    BEGIN TRAN
        SELECT * FROM Table2
        GO
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH

with
BEGIN TRY
    BEGIN TRAN
       SELECT * FROM Table
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH

MS SQL - How to Calculate Sum of Multiple Columns in one row

Watch this example on YouTube:

Here is my table:

I want to know what is total of bonus, salary and gambling for each user
The following query will provide me with this info:

Select CustomerName,
(Select Sum(Earnings)
From (Values (Bonus), (Salary), (Gambling)) As tblSum(Earnings))
From Table3

Monday, 25 May 2015

MSSQL - SQLServer - How to Calculate Max of Multiple Columns

Here is my table:

I want to know what is the max of bonus, salary or gambling for each user
Here is the query that will do it:
select CustomerName,
(select Max(Numbers)
from (Values (Bonus), (Salary), (Gambling) ) as TblNum(Numbers))

From Table3

Thursday, 21 May 2015

MSSQL - SQLServer - How to Calculate Average of Multiple Columns

Watch this example on YouTube:

Here is my table:

I want to know average of Bonus, Salary and Gambling for each user.
The following query will calculate it:
Select CustomerName ,
(Select AVG(MyAverage) From (Values (Bonus), (Salary), (Gambling)) as TblAverage(MyAverage))
From Table3

Wednesday, 20 May 2015

MSSQL - Min funciton that takes 2 or more values - like Math.Min in .NET

Watch this example on YouTube:

Here is my table

The following query will get minimum bonus, salary or gambling for each customer, whichever is the lowest:

Select CustomerName,
(Select Min(Amount)
From (Values (Bonus), (Salary), (Gambling)) as tblMinimum(Amount))
From Table3

Here is my output: