Wednesday, April 22, 2020

Reading from SQL server and assigning the value to a variable in a custom class.

using System;
using System.Data.SqlClient;

namespace CSharpConnectToSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            dboFrank result = new dboFrank();
            Console.WriteLine("Hello World!");
            string connetionString = null;
            SqlConnection cnn;
            connetionString = @"Server = JOHN-PC\TEW_SQLEXPRESS; Database=myDB; Trusted_Connection=Yes;";
            cnn = new SqlConnection(connetionString);
            try
            {
                cnn.Open();
                Console.WriteLine("Connection Open ! ");
                SqlCommand command = new SqlCommand("SELECT PersonID, LastName, LargeNumber FROM dbo.Frank",cnn);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    result.LastName = reader["LastName"] as string;
                    Console.WriteLine("the last name is: " + result.LastName);
                }
            }
            catch (Exception ex)
            { 
                Console.WriteLine("Can not open connection ! " + ex.Message);
            }
            Console.ReadLine();
        }
    }

    public class dboFrank
    {
        public int PersonID { get; set; }
        public string LastName { get; set; }
        public double LargeNumber { get; set; }
    }
}




Monday, April 20, 2020

MS Project: Create Tasks, Create Resources, Assign Precedence, Set to Automatically Level resources, set task duration

Add reference to:
  • Microsoft Office Project 16 Object Library.
Imports Microsoft.Office.Interop
Module Module1
    Sub Main()
        Dim ProjApp As New MSProject.Application
        ProjApp.ScreenUpdating = False
        ProjApp.Visible = True
        ProjApp.FileNew()
        Dim proj As MSProject.Project = ProjApp.ActiveProject
        'Creating a task
        Dim tas1Name As MSProject.Task = proj.Tasks.Add("Task1")
        'Assigning the task some resources
        tas1Name.ResourceNames = "res1,res2,res3"
        Dim tas2Name As MSProject.Task = proj.Tasks.Add("Task2")
        tas2Name.ResourceNames = "res1"
        Dim tas3Name As MSProject.Task = proj.Tasks.Add("Task3")
        tas3Name.ResourceNames = "res1"
        Dim tas4Name As MSProject.Task = proj.Tasks.Add("Task4")
        tas4Name.ResourceNames = "res1"
        Dim tas5Name As MSProject.Task = proj.Tasks.Add("Task5")
        tas5Name.ResourceNames = "res2"
        Dim tas6Name As MSProject.Task = proj.Tasks.Add("Task6")
        tas6Name.ResourceNames = "res2"
        'Specify predecessors
        tas2Name.LinkPredecessors(tas1Name)
        tas3Name.LinkPredecessors(tas1Name)
        tas4Name.LinkPredecessors(tas1Name)
        tas4Name.LinkPredecessors(tas2Name)
        tas4Name.LinkPredecessors(tas3Name)
        tas6Name.LinkPredecessors(tas5Name)
        tas4Name.LinkPredecessors(tas6Name)
        'ProjApp.LevelingOptions(True)
        'ProjApp.LevelNow()
        tas1Name.SetField(MSProject.PjField.pjTaskManual, Value:="No")
        tas2Name.SetField(MSProject.PjField.pjTaskManual, Value:="No")
        tas3Name.SetField(MSProject.PjField.pjTaskManual, Value:="No")
        tas4Name.SetField(MSProject.PjField.pjTaskManual, Value:="No")
        tas5Name.SetField(MSProject.PjField.pjTaskManual, Value:="No")
        tas6Name.SetField(MSProject.PjField.pjTaskManual, Value:="No")
        MsgBox("about to change the durations")
    End Sub
End Module

Friday, April 17, 2020

MS Project Automation

Add reference to:
  • Microsoft Office Project 16 Object Library.
Imports Microsoft.Office.Interop

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim pjApp As New MSProject.Application
        pjApp = New MSProject.Application
        pjApp.Visible = True
        pjApp.FileNew()
        pjApp.ActiveProject.Tasks.Add("Wind clocks")
        pjApp.FileSave()
        pjApp.FileClose()
        MsgBox("about to close project")
        pjApp.Quit()
    End Sub

End Class

MS Word Automation from Visual Studio

Add reference to Microsoft Word 16 Object Library


Imports Microsoft.Office.Interop

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim oWord As Word.Application
        Dim oDoc As Word.Document
        Dim oTable As Word.Table
        Dim oPara1 As Word.Paragraph, oPara2 As Word.Paragraph
        Dim oPara3 As Word.Paragraph, oPara4 As Word.Paragraph
        Dim oRng As Word.Range
        Dim oShape As Word.InlineShape
        Dim oChart As Object
        Dim Pos As Double

        'Start Word and open the document template.
        oWord = CreateObject("Word.Application")
        oWord.Visible = True
        oDoc = oWord.Documents.Add

        MsgBox("Word should be visible")
        'Insert a paragraph at the beginning of the document.
        oPara1 = oDoc.Content.Paragraphs.Add
        oPara1.Range.Text = "Heading 1"
        oPara1.Range.Font.Bold = True
        oPara1.Format.SpaceAfter = 24    '24 pt spacing after paragraph.
        oPara1.Range.InsertParagraphAfter()

        'Insert a paragraph at the end of the document.
        '** \endofdoc is a predefined bookmark.
        oPara2 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)
        oPara2.Range.Text = "Heading 2"
        oPara2.Format.SpaceAfter = 6
        oPara2.Range.InsertParagraphAfter()

        'Insert another paragraph.
        oPara3 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)
        oPara3.Range.Text = "This is a sentence of normal text. Now here is a table:"
        oPara3.Range.Font.Bold = False
        oPara3.Format.SpaceAfter = 24
        oPara3.Range.InsertParagraphAfter()

        'Insert a 3 x 5 table, fill it with data, and make the first row
        'bold and italic.
        Dim r As Integer, c As Integer
        oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, 3, 5)
        oTable.Range.ParagraphFormat.SpaceAfter = 6
        For r = 1 To 3
            For c = 1 To 5
                oTable.Cell(r, c).Range.Text = "r" & r & "c" & c
            Next
        Next
        oTable.Rows.Item(1).Range.Font.Bold = True
        oTable.Rows.Item(1).Range.Font.Italic = True

        'Add some text after the table.
        'oTable.Range.InsertParagraphAfter()
        oPara4 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks.Item("\endofdoc").Range)
        oPara4.Range.InsertParagraphBefore()
        oPara4.Range.Text = "And here's another table:"
        oPara4.Format.SpaceAfter = 24
        oPara4.Range.InsertParagraphAfter()

        'Insert a 5 x 2 table, fill it with data, and change the column widths.
        oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, 5, 2)
        oTable.Range.ParagraphFormat.SpaceAfter = 6
        For r = 1 To 5
            For c = 1 To 2
                oTable.Cell(r, c).Range.Text = "r" & r & "c" & c
            Next
        Next
        oTable.Columns.Item(1).Width = oWord.InchesToPoints(2)   'Change width of columns 1 & 2
        oTable.Columns.Item(2).Width = oWord.InchesToPoints(3)

        'Keep inserting text. When you get to 7 inches from top of the
        'document, insert a hard page break.
        Pos = oWord.InchesToPoints(7)
        oDoc.Bookmarks.Item("\endofdoc").Range.InsertParagraphAfter()
        Do
            oRng = oDoc.Bookmarks.Item("\endofdoc").Range
            oRng.ParagraphFormat.SpaceAfter = 6
            oRng.InsertAfter("A line of text")
            oRng.InsertParagraphAfter()
        Loop While Pos >= oRng.Information(Word.WdInformation.wdVerticalPositionRelativeToPage)
        oRng.Collapse(Word.WdCollapseDirection.wdCollapseEnd)
        oRng.InsertBreak(Word.WdBreakType.wdPageBreak)
        oRng.Collapse(Word.WdCollapseDirection.wdCollapseEnd)
        oRng.InsertAfter("We're now on page 2. Here's my chart:")
        oRng.InsertParagraphAfter()

        'Insert a chart and change the chart.
        oShape = oDoc.Bookmarks.Item("\endofdoc").Range.InlineShapes.AddOLEObject(
            ClassType:="MSGraph.Chart.8", FileName _
            :="", LinkToFile:=False, DisplayAsIcon:=False)
        oChart = oShape.OLEFormat.Object
        oChart.charttype = 4 'xlLine = 4
        oChart.Application.Update()
        MsgBox("about to close the chart")
        oChart.Application.Quit()
        'If desired, you can proceed from here using the Microsoft Graph 
        'Object model on the oChart object to make additional changes to the
        'chart.
        oShape.Width = oWord.InchesToPoints(6.25)
        oShape.Height = oWord.InchesToPoints(3.57)

        'Add text after the chart.
        oRng = oDoc.Bookmarks.Item("\endofdoc").Range
        oRng.InsertParagraphAfter()
        oRng.InsertAfter("THE END.")

    End Sub
End Class


https://support.microsoft.com/en-ca/help/316383/how-to-automate-word-from-visual-basic-net-to-create-a-new-document

VB.Net TreeView Populate and Show which node is selected


Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        TreeView1.Nodes.Add("Node 0")
        TreeView1.Nodes.Add("Node 1")
        TreeView1.Nodes.Add("Node 3")
        TreeView1.Nodes(0).Nodes.Add(New TreeNode("Project 1"))
        'Creating child nodes under the first child

        For loopindex As Integer = 1 To 4
            TreeView1.Nodes(0).Nodes(0).Nodes.Add(New _
            TreeNode("Sub Project" & Str(loopindex)))
        Next loopindex
        ' creating child nodes under the root
        TreeView1.Nodes(0).Nodes.Add(New TreeNode("Project 6"))
        'creating child nodes under the created child node

        For loopindex As Integer = 1 To 3
            TreeView1.Nodes(0).Nodes(1).Nodes.Add(New _
            TreeNode("Project File" & Str(loopindex)))
        Next loopindex
    End Sub

    Private Sub TreeView1_AfterSelect(sender As Object, e As TreeViewEventArgs) Handles TreeView1.AfterSelect
        ' Determine by checking the Node property of the TreeViewEventArgs.  
        MessageBox.Show(e.Node.Text)
    End Sub
End Class

Thanks to:
https://www.tutorialspoint.com/vb.net/vb.net_treeview.htm
https://docs.microsoft.com/en-us/dotnet/framework/winforms/controls/how-to-determine-which-treeview-node-was-clicked-windows-forms

Thursday, April 9, 2020

Insert a new record into the SQL table using Insert Into

This example inserts an integer (PersonID, a varchar (LastName), and float (LargeNumber) variable.

Note that for the SQL, it is a float which is 8 bytes, which corresponds to a double in .Net, see https://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server




SqlCommand command = new SqlCommand("INSERT INTO dbo.FRANK (PersonID,LastName,LargeNumber) VALUES (@id,@lastname,@number)",cnn);
double BigNumber = 1234.564;
command.Parameters.AddWithValue("@id", 1);
command.Parameters.AddWithValue("@lastname", "John Hufnagel");
command.Parameters.AddWithValue("@number", BigNumber);
int result = command.ExecuteNonQuery();
Console.WriteLine("I think it is done");
// Check Error
if (result < 0)
  Console.WriteLine("Error inserting data into Database!");
                

Establishing connection to SQL Server on local machine, C#

The machine name is JOHN-PC
The SQL Server name is: TEW_SQLEXPRESS
The database name is myDB


using System;
using System.Data.SqlClient;

namespace CSharpConnectToSQLServer
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            string connetionString = null;
            SqlConnection cnn;
            connetionString = @"Server = JOHN-PC\TEW_SQLEXPRESS; Database=myDB; Trusted_Connection=Yes;";
            cnn = new SqlConnection(connetionString);
            try
            {
                cnn.Open();
                Console.WriteLine("Connection Open ! ");
                cnn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Can not open connection ! ");
            }
            Console.ReadLine();

        }
    }
}

Thanks to: https://www.connectionstrings.com/sql-server/
Using the @ symbol: https://stackoverflow.com/questions/22311799/c-sharp-connection-string-error
The general body of the code: https://stackoverflow.com/questions/22803975/connection-to-sql-server-express-from-c-sharp

Connecting to a SQL Server from Visual Studio

https://blogs.gre.ac.uk/cmssupport/application-development/programming/asp-net/connecting-to-sql-server-using-visual-studio/

Setting up SQL Server Express with SQL Server Management Studio


https://youtu.be/DI1cXYtlQlo

SQL Server Management Studio located at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

MS Access opening a DAO database recordset

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-openrecordset-method-dao

Wednesday, April 8, 2020

Private vs Public Equity

I was reading in the Globe and Mail about Micheal Lee-Chin going back into the investment fund business by focusing on private equity.

Private equity is where private individuals (or corporations) invest in a company. This is contrast to public equity where anyone with access to the stock market can invest in a company. All companies start off life as private equity: A person may start a business in their own garage or studio, funding the company out of their own savings or convincing a few friends or investors, or maybe even convincing the dudes on Lion's Den, to fund their company. In any event, it is individual people that are coming forward and putting the money in the company.

As company grow, they often want to get access to a larger pool of capital so the have an IPO (initial public offering). After Facebook's latest experience, IPO should be a common household term. Now in Facebook's case, they did not go public because they wanted access to more capital. They are growing at quite a nice rate and their revenues and investments seem to enable Facebook to go where it wants. Rather, Facebook was regulated to go public because it had too many investors

Find out what wakes my computer from sleep:

At the command prompt, type:

powercfg -lastwake

Enable Administrator Account in Windows 7

Search cmd.exe in start menu and run cmd.exe as administrator.

To enable Administrator account Type: net user administrator /active:yes

Thanks to: https://www.instructables.com/id/3-Ways-to-Enable-Administrator-Account-in-Windows-/

Horizontal Scrolling for Code

Horizontal scrolling can be obtained by using a tag as follows using  style="overflow: auto;"          Dim   connStr   As   Stri...