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

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++;
                    }
                }
            }
        }
    }
}