connecting to sharepoint oniline using Powershell : Cannot find an appropriate constructor for type Microsoft.SharePoint.Client.ClientContext

Question

Hi I am trying to connect to sharepoint online and publish calender using the data from a SQL Table and I am getting the following exception , please advise.The same code works fine with slight modification on a on prem sharepoint server I have added sharepointonline for the authentication but it is failing with the error.

[System.Reflection.Assembly]::LoadFile ("C:\MOSSLibrary\Microsoft.SharePoint.Client.dll") | Out-Null
[System.Reflection.Assembly]::LoadFile("C:\MOSSLibrary\Microsoft.SharePoint.Client.Runtime.dll") | Out-Null 
$username = "XXXXXX"
$url = "XXXXXX"


$pass= cat C:\text.txt | ConvertTo-SecureString
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username,$Pass)
$Context.Credentials = $Creds

$web = $Context.Web
$Context.Load($web)
$Context.Load($splist)
$splist = $Context.web.Lists.GetByTitle("XXXX")

$ItemCreateInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation

####Some Data coming from SQL Server DB into $table########

$table = $result.Tables[0];

foreach ($row in $table)
{

Write-Host $row.Item("changetitle") $row.Item("status");

$Item1 =  $splist.AddItem($ItemCreateInfo)
$Item1["Title"] = "test"
Write-host $date    
$Item1.Update()
$Context.ExecuteQuery()
}

Exception

New-Object : A constructor was not found. Cannot find an appropriate constructor for type Microsoft.SharePoint.Client.ClientContext. At C:\MOSSLibrary\testingpublish.ps1:15 char:12 + $Context = New-Object Microsoft.SharePoint.Client.ClientContext($site ... +
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : ObjectNotFound: (:) [New-Object], PSArgumentException + FullyQualifiedErrorId : CannotFindAppropriateCtor,Microsoft.PowerShell.Commands.NewObjectCommand The property 'Credentials' cannot be found on this object. Verify that the property exists and can be set. At C:\MOSSLibrary\testingpublish.ps1:17 char:1 + $Context.Credentials = $Creds + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound You cannot call a method on a null-valued expression. At C:\MOSSLibrary\testingpublish.ps1:20 char:1 + $Context.Load($web) + ~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\MOSSLibrary\testingpublish.ps1:21 char:1 + $Context.Load($splist) + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull You cannot call a method on a null-valued expression. At C:\MOSSLibrary\testingpublish.ps1:22 char:1 + $splist = $Context.web.Lists.GetByTitle("XXXXXXX") + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull

Solution

It seems Assemblies are not loading correctly.

[System.Reflection.Assembly]::LoadFile ("C:\MOSSLibrary\Microsoft.SharePoint.Client.dll") | Out-Null
[System.Reflection.Assembly]::LoadFile("C:\MOSSLibrary\Microsoft.SharePoint.Client.Runtime.dll") | Out-Null 

Instead of above, try following

Add-Type -Path "C:\MOSSLibrary\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\MOSSLibrary\Microsoft.SharePoint.Client.Runtime.dll"

PS: Make sure that C:\MOSSLibrary\ contains following two .dll's

Microsoft.SharePoint.Client.dll
Microsoft.SharePoint.Client.Runtime.dll