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

Monday, 5 October 2015

Watch how to fix it on YouTube:

Some NuGet packages were installed using a target framework different from the current target framework and may need to be reinstalled. Visit http://docs.nuget.org/docs/workflows/reinstalling-packages for more information. Packages affected: EntityFramework, Microsoft.Net.Http

- in my case I got this error because I changed target framework to .Net 4.0 (from .Net 4.5)
- to fix it - go to
Tools/NuGet Package Manager/NuGet Package Console and execute the following command:
PM> Update-Package -reinstall

Thursday, 1 October 2015

Javascript - how to fix error - Expected hexadecimal digit

Watch this exmaple on YouTube:
Replace:
    Private Sub dispMessage(ByVal message As String)
        Try
            Dim myScript As String = String.Format("<script> alert('{0}');</script>", message)
            Page.ClientScript.RegisterStartupScript(Page.GetType(), "script", myScript)
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        dispMessage("Record has been saved\updated.")
    End Sub

With:

    Private Sub dispMessage(ByVal message As String)
        Try
            Dim myScript As String = String.Format("<script> alert('{0}');</script>", message)
            Page.ClientScript.RegisterStartupScript(Page.GetType(), "script", myScript)
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        dispMessage("Record has been saved-updated.")
    End Sub

\u - is causing this error

Tuesday, 22 September 2015

How to fix error: The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>).

Watch this example on YouTube:

Move javascript code from <Head> to <Body>

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

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
     <script type="text/javascript">
         function doSomething() {
             var test = document.getElementById('<%= TextBox1.ClientID %>')
            }
    </script>
</head>
<body>
        <script type="text/javascript">
            function doSomething() {
                var test = document.getElementById('<%= TextBox1.ClientID %>')
        }
    </script>
    <form id="form1" runat="server">
        <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
        </asp:ToolkitScriptManager>
        <asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="TextBox1"></asp:CalendarExtender>
        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        </div>
    </form>
</body>
</html>

After

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

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html>

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

</head>
<body>
         <script type="text/javascript">
             function doSomething() {
                 var test = document.getElementById('<%= TextBox1.ClientID %>')
         }
    </script>
        <script type="text/javascript">
            function doSomething() {
                var test = document.getElementById('<%= TextBox1.ClientID %>')
        }
    </script>
    <form id="form1" runat="server">
        <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
        </asp:ToolkitScriptManager>
        <asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="TextBox1"></asp:CalendarExtender>
        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        </div>
    </form>
</body>
</html>

to Before

Monday, 22 June 2015

GridView - How to replace line breaks in GridView

watch this example on YouTube:

html

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

<!DOCTYPE html>

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

    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# ((string)Eval("Name")).Replace("\n", "<br />") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>
    </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 GridViewReplaceLineBrakes : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataTable dt = LoadData();
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

        public DataTable LoadData()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Rows.Add(new object[] { "Frank\nSomething" });
            dt.Rows.Add(new object[] { "Bob\nSomething Else" });
            dt.Rows.Add(new object[] { "Ann\nLast Name" });
            return dt;
        }
    }
}

C# - ASP.NET - How to remove comma from a string

Watch on YouTube:

string text = "some text,";
Response.Write(text.TrimEnd(','));

Thursday, 18 June 2015

ASP.NET - How to ensure user entered domain name and user name in correct format

Watch this example on YouTUbe:

Copy and paste code below to test (validator highlighted in red)

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
        <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="TextBox1"
            ErrorMessage="Ensure Login ID is in the following format: DomainName\LoginName"
            ValidationExpression="(DOMAINName|domainName|domainname)\\\w+$"></asp:RegularExpressionValidator>

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

Wednesday, 17 June 2015

How to fix error The process cannot access the file because it is being used by another process.

Watch this example on YouTube:

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

Additional information: The process cannot access the file because it is being used by another process.

REPLACE:

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

using System.Text;
using System.IO;

namespace WebApplication1
{
    public partial class OpenFileIssue : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string FileDirectory = "C:\\Temp\\ExcelTest\\Test.csv";
            var reader = new StreamReader(File.OpenRead(FileDirectory));
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                var values = line.Split(',');
                if (values[1].Trim() != String.Empty)
                {
                    Response.Write("found something<br />");
                }
            }
            reader = new StreamReader(File.OpenRead(FileDirectory));
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                var values = line.Split(',');
                if (values[1].Trim() != String.Empty)
                {
                    Response.Write("found something");
                }
            }
            reader.Close();
            string FileDirMoved = "C:\\Temp\\ExcelTest\\Moved\\Test.csv";
            File.Move(FileDirectory, FileDirMoved);
        }
    }
}

WITH:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Text;
using System.IO;

namespace WebApplication1
{
    public partial class OpenFileIssue : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string FileDirectory = "C:\\Temp\\ExcelTest\\Test.csv";
            var reader = new StreamReader(File.OpenRead(FileDirectory));
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                var values = line.Split(',');
                if (values[1].Trim() != String.Empty)
                {
                    Response.Write("found something<br />");
                }
            }
            reader.Close();
            reader = new StreamReader(File.OpenRead(FileDirectory));
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                var values = line.Split(',');
                if (values[1].Trim() != String.Empty)
                {
                    Response.Write("found something");
                }
            }
            reader.Close();
            string FileDirMoved = "C:\\Temp\\ExcelTest\\Moved\\Test.csv";
            File.Move(FileDirectory, FileDirMoved);
        }
    }
}

Tuesday, 16 June 2015

How to fix error - JavaScript runtime error: '' is undefined

Watch on YouTube:

To fix - replace
ScriptManager.RegisterStartupScript(Page, typeof(Page), "", "DispMess(" + "test" + ")", true);
with:
ScriptManager.RegisterStartupScript(Page, typeof(Page), "", "DispMess('" + "test" + "')", true);

Wednesday, 10 June 2015

ASP.NET - How to get value of asp TextBox using JavaScript

Watch this example on YouTube:

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
        function getText() {
            var str = document.getElementById('<%= TextBox1.ClientID%>').value;
            alert(str);
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="Button" OnClientClick="getText();" />
    </div>
    </form>
</body>
</html>

MSSQL - Check if stored procedure exists

Watch this example on YouTube:

IF EXISTS(Select * FROM sys.objects Where type = 'P' and name = 'NameOfYourStoredProcedure') print 'OK'

Tuesday, 9 June 2015

MSSQL - How to get First name and last name from string or how to read until first empty space and after first empty space

Watch this example on YouTube:
Here is my table:

The following statement
select CustomerName,
Substring(CustomerName, 1, (CHARINDEX(' ', CustomerName + ' ')-1)) as FirstName,
Substring(CustomerName, LEN(Substring(CustomerName, 1, (CHARINDEX(' ', CustomerName + ' ')+1))), LEN(CustomerName)) as LastName

from Table3

will produce this result:

Wednesday, 3 June 2015

OpenXml - how to loop through all cells in Excel - Including empty cells which are ignored by OpenXML

Watch this example on YouTube:

You can copy and paste code below to see how to loop through Excel doc, all explanations in video above.

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

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;

namespace WebApplication1
{
    public partial class OpenXML___Looping_Through_Empty_Cells : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        private string GetColumnLetter(int columnNumber)
        {
            string LetterName = string.Empty;
            if (columnNumber < 25)
            {
                LetterName = ((char)(columnNumber + 66)).ToString();
            }
            else if (columnNumber < 51)
            {
                LetterName = "A" + ((char)(columnNumber + 40)).ToString();
            }
            return LetterName;
        }
        private string GetCellValue(WorkbookPart workBookPart, string sheetName, string addressName)
        {
            string value = null;
            Sheet mySheet = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
            if (mySheet == null)
            {
                throw new ArgumentException("cannot find the following sheet: " + mySheet);
            }
            WorksheetPart wsPart = (WorksheetPart)(workBookPart.GetPartById(mySheet.Id));
            Cell myCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
            if (myCell != null)
            {
                value = myCell.InnerText;
                if (myCell.DataType != null)
                {
                    switch (myCell.DataType.Value)
                    {
                        case CellValues.SharedString:
                            var stringTable = workBookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            if (stringTable != null)
                            {
                                value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                            }
                            if (stringTable == null)
                            {
                                value = "";
                            }
                            break;
                        case CellValues.Boolean:
                            switch (value)
                            {
                                case "0":
                                    value = "FALSE";
                                    break;
                                case "1":
                                    value = "TRUE";
                                    break;
                            }
                            break;
                    }
                }
            }
            return value;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open("C:\\Temp\\ExcelTest\\testexcel.xlsx", false))
            {
                string cellValue;
                int columnNumber = -1;
                int rowNumber = 0;

                WorkbookPart workBookPart = spreadSheetDocument.WorkbookPart;
                Sheet workSheet = workBookPart.Workbook.Descendants<Sheet>().Where(s =>
                    s.Name.Value.Equals("Sheet1")).FirstOrDefault();
                WorksheetPart workSheetPart = (WorksheetPart)(workBookPart.GetPartById(workSheet.Id));
                SheetData sheetData = workSheetPart.Worksheet.Elements<SheetData>().First();
                foreach (Row r in sheetData.Elements<Row>())
                {
                    rowNumber++;
                    columnNumber = -1;
                    foreach (Cell c in r.Elements<Cell>())
                    {
                        string letter;
                        letter = GetColumnLetter(columnNumber);

                        while (c.CellReference.ToString().Substring(0, letter.Length) != letter)
                        {
                            columnNumber++;
                            letter = GetColumnLetter(columnNumber);
                        }

                        cellValue = GetCellValue(workBookPart, "Sheet1", c.CellReference);
                        switch (letter)
                        {
                            case "A":
                                ListBox1.Items.Add(new ListItem("A" + rowNumber.ToString() + ": " + cellValue));
                                break;
                            case "B":
                                ListBox1.Items.Add(new ListItem("B" + rowNumber.ToString() + ": " + cellValue));
                                break;
                            case "C":
                                ListBox1.Items.Add(new ListItem("C" + rowNumber.ToString() + ": " + cellValue));
                                break;
                            default:
                                break;
                        }
                        columnNumber++;
                    }
                }
            }
        }
    }
}

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