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

Tuesday 14 April 2015

MS Access - query to read until first and second occurrence of a new line characters

Watch this example on YouTube:

First Occurrence: Left([Field1],InStr(1,[Field1],Chr(13)))

Second Occurrence: Left([Field1],InStr(InStr(1,[Field1],Chr(13))+1,[Field1],Chr(13)))

Wednesday 8 April 2015

ASP.NET - Create TreeView programmatically with CheckBoxes - ensure program can get checkbox value after postback

Watch this example on YouTube

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Panel ID="Panel1" runat="server" OnLoad="Panel1_Load"></asp:Panel>
    </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;

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

        }

        protected void Panel1_Load(object sender, EventArgs e)
        {
            TreeView tv = new TreeView();
            tv.ID = "MyID";
            tv.ShowCheckBoxes = TreeNodeTypes.All;
            for (int i = 0; i < 10; i++)
            {
                TreeNode tn = new TreeNode();
                tn.NavigateUrl = "Test " + i.ToString();
                tn.Text = "Test " + i.ToString();
                tn.ShowCheckBox = true;
                tv.Nodes.Add(tn);
            }
            Panel1.Controls.Add(tv);
            Button btn = new Button();
            btn.Text = "PostBack";
            btn.ID = "PostBack";
            btn.CausesValidation = false;
            btn.Click += (s, en) =>
                {
                    string checkBx = string.Empty;
                    foreach (TreeNode trN in tv.Nodes)
                    {
                        if (trN.Checked)
                        {
                            checkBx += trN.Value + ", ";
                        }
                    }
                    Session["CheckBoxes"] = checkBx;
                };
            Panel1.Controls.Add(btn);
        }
    }
}

Tuesday 7 April 2015

ASP.NET - GridView with word wrap - Ensure words are not overlapping

Watch this example on YouTube

StyleSheet

.GridView{
    word-wrap: break-word;
}

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link href="StyleSheet2.css" rel="stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" CssClass="GridView" runat="server" AutoGenerateColumns="false" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
            <Columns>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server"
                            Text='<%# Bind("Name") %>' Width="100px"
                            ></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField>
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("Age") %>' Width="100px"></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 GridViewWordWrap : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataTable dt = LoadData();
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

        protected DataTable LoadData()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Age");
            dt.Rows.Add(new object[] { "FrankVeryLoooooooooooooooooooooooooooooong", "33AgeVeryLooooooooooooooooooooong" });
            dt.Rows.Add(new object[] { "BobVeryLoooooooooooooooooooooooooooooong", "44AgeVeryLooooooooooooooooooooong" });
            dt.Rows.Add(new object[] { "JohndkVeryLoooooooooooooooooooooooooooooong", "55AgeVeryLooooooooooooooooooooong" });
            dt.Rows.Add(new object[] { "TarasVeryLoooooooooooooooooooooooooooooong", "66AgeVeryLooooooooooooooooooooong" });
            dt.Rows.Add(new object[] { "IvanVeryLoooooooooooooooooooooooooooooong", "77AgeVeryLooooooooooooooooooooong" });
            dt.Rows.Add(new object[] { "LesiaVeryLoooooooooooooooooooooooooooooong", "88AgeVeryLooooooooooooooooooooong" });
            return dt;
        }

        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}

Saturday 4 April 2015

ASP.NET - How to open Excel file (or any other file)

Watch on YouTube:

using System.Diagnostics;

   ProcessStartInfo sInf = new ProcessStartInfo();
   sInf.FileName = "EXCEL.EXE";
   sInf.Arguments = @"C:\Temp\ExcelTest\testexcel.xlsx";
   Process.Start(sInf);

ASP.NET - Create TreeView with CheckBoxes - Check/Uncheck on client and server side

Watch this example on YouTube:

-HTML

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript">
        function ClientSideChangeSelection() {
            var chkBox = window.event.srcElement;
            var isChecked;
            var isCheckBox = false;

            if (chkBox.tagName == "INPUT" && chkBox.type.toUpperCase() == "CHECKBOX") {
                var treeNode = chkBox;
                isChecked = treeNode.checked;
                do{
                    chkBox = chkBox.parentElement;
                } while (chkBox.tagName != "TABLE")
                var firstLevel = chkBox.rows[0].cells.length;

                var tableElements = chkBox.parentElement.getElementsByTagName("TABLE");
                var tableElementsCount = tableElements.length;
                if (tableElementsCount > 0) {
                    for (i = 0; i < tableElementsCount; i++) {
                        if (tableElements[i] == chkBox) {
                            i++;
                            isCheckBox = true;
                            if (i == tableElementsCount) {
                                return;
                            }
                        }
                        if (isCheckBox == true) {
                            var secondLevel = tableElements[i].rows[0].cells.length;
                            if (secondLevel > firstLevel) {
                                var cell = tableElements[i].rows[0].cells[secondLevel - 1];
                                var inputElement = cell.getElementsByTagName("INPUT");
                                inputElement[0].checked = isChecked;
                            }
                            else {
                                return;
                            }
                        }
                    }
                }
            }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TreeView ID="TreeView1" onclick="ClientSideChangeSelection()" ShowCheckBoxes="All" runat="server"></asp:TreeView>
    </div>
    </form>
</body>
</html>

-CODE BEHIND

        protected void Page_Load(object sender, EventArgs e)
        {
            TreeNode tNode1 = new TreeNode();
            tNode1.Text = "HeadNode1";
            tNode1.Value = "HeadNode1";

            TreeNode h1ChildNode1 = new TreeNode();
            h1ChildNode1.Text = "Head1Child1";
            tNode1.ChildNodes.Add(h1ChildNode1);

            TreeNode h1GrandChild1 = new TreeNode();
            h1GrandChild1.Text = "Head1Child1Grand1";
            h1ChildNode1.ChildNodes.Add(h1GrandChild1);

            TreeNode h1ChildNode2 = new TreeNode();
            h1ChildNode2.Text = "Head1Child2";
            tNode1.ChildNodes.Add(h1ChildNode2);

            TreeNode h1ChildNode3 = new TreeNode();
            h1ChildNode3.Text = "Head1Child3";
            tNode1.ChildNodes.Add(h1ChildNode3);
            TreeView1.Nodes.Add(tNode1);

            TreeNode tNode2 = new TreeNode();
            tNode2.Text = "HeadNode2";
            tNode2.Value = "HeadNode2";

            TreeNode h2ChildNode1 = new TreeNode();
            h2ChildNode1.Text = "Head2ChildNode1";
            tNode2.ChildNodes.Add(h2ChildNode1);

            TreeNode h2ChildNode2 = new TreeNode();
            h2ChildNode2.Text = "Head2ChildNode2";
            tNode2.ChildNodes.Add(h2ChildNode2);

            TreeNode h2ChildNode3 = new TreeNode();
            h2ChildNode3.Text = "Head2ChildNode3";
            tNode2.ChildNodes.Add(h2ChildNode3);

            TreeView1.Nodes.Add(tNode2);

            ServerSideChangeSelection(TreeView1, true);

        }

        protected TreeView ServerSideChangeSelection(TreeView t, bool check)
        {
            foreach (TreeNode tn in t.Nodes)
            {
                tn.Checked = check;
                if(tn.ChildNodes.Count > 0 ){
                    foreach(TreeNode childNd in tn.ChildNodes){
                        childNd.Checked = check;
                    }
                }
            }
            return t;
        }

Thursday 2 April 2015

The Ukrainians - A History OF Rock Music IN Ukrainian - promo

Wednesday 1 April 2015

Watch this example on YouTube:

Copy and paste the code below:

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 TreeViewTest3 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            TreeView myTree = new TreeView();
            myTree.ExpandDepth = 1;
            myTree.ID = "TEST";
            myTree.CollapseAll();
            myTree.ShowCheckBoxes = TreeNodeTypes.All;

            TreeNode tNode1 = new TreeNode();
            tNode1.Text = "HeadNode1";
            tNode1.Value = "HeadNode1";

            TreeNode h1ChildNode1 = new TreeNode();
            h1ChildNode1.Text = "Head1Child1";
            tNode1.ChildNodes.Add(h1ChildNode1);

            TreeNode h1GrandChild1 = new TreeNode();
            h1GrandChild1.Text = "Head1Child1Grand1";
            h1ChildNode1.ChildNodes.Add(h1GrandChild1);

            TreeNode h1ChildNode2 = new TreeNode();
            h1ChildNode2.Text = "Head1Child2";
            tNode1.ChildNodes.Add(h1ChildNode2);

            TreeNode h1ChildNode3 = new TreeNode();
            h1ChildNode3.Text = "Head1Child3";
            tNode1.ChildNodes.Add(h1ChildNode3);

            myTree.Nodes.Add(tNode1);

            CheckOrUncheck(myTree, true);
            form1.Controls.Add(myTree);
        }

        protected TreeView CheckOrUncheck(TreeView t, bool check){
            foreach(TreeNode tn in t.Nodes){
                tn.Checked = check;
                if (tn.ChildNodes.Count > 0)
                {
                    foreach (TreeNode ch1 in tn.ChildNodes)
                    {
                        ch1.Checked = check;
                    }
                }
            }

            return t;
        }
    }
}