powershellms-accessoledbcommandoledbexception

Trying to save values to an Access DB, but I'm getting an error when I try ExecuteNonQuery

I'm trying to use Powershell to pull a list of PC Names from an MS Access database, pull their drive size stats (WMI-Object), then save them back to Access. I keep getting the following error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "No value given 
for one or more required parameters."
At C:\Users\*******\Desktop\Scripts\AccessUpdate\HDDUpdate.ps1:75 char:5
+     $WriteFreeSpaceCmd.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

This is running on Windows 10, Access 2016 x64, debugging with Visual Studio Code, PowerShell 5.1, NET Framework 4.8, ADE 2016 x64.

I've checked all the field names, but I have no idea what to check past that. My Google-fu wasn't effective either.

$connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\jharold\Desktop\Scripts\AccessUpdate\WinHDDTest.accdb;Persist Security Info=False;"  #Change Source to DB location

[reflection.assembly]::LoadWithPartialName("System.Data") | Out-Null
$Conn = New-Object System.Data.OleDb.OleDbConnection

$Conn.ConnectionString = $connstr
$Conn.Open() | Out-Null

#
#  Pull PC names from db and gather HDD info code works fine
#

foreach ($PC in $HDDInfo) {
    $WriteFreeSpaceCmd = New-Object System.Data.OleDb.OleDbCommand
    $WriteFreeSpaceCmd.Connection = $Conn

    $WriteTotalSpaceCmd = New-Object System.Data.OleDb.OleDbCommand
    $WriteTotalSpaceCmd.Connection = $Conn

    $temp1 = "UPDATE Status SET ImportFreeSpace = " + $PC.FreeSpace + " WHERE NetBios_Name = " + $PC.NetBiosInfo
    $WriteFreeSpaceCmd.CommandText = $temp1 

    $temp2 = "UPDATE Status SET ImportTotalSpace = " + $PC.TotalSpace + " WHERE NetBios_Name = " + $PC.NetBiosInfo
    $WriteTotalSpaceCmd.CommandText = $temp2    

    $WriteFreeSpaceCmd.ExecuteNonQuery() 
    $WriteTotalSpaceCmd.ExecuteNonQuery() 
}

I expect $PC.FreeSpace and $PC.TotalSpace to save to the corresponding PC's ImportFreeSpace and ImportTotalSpace as text. Instead, it errors out as soon as it runs ExecuteNonQuery().

Solution

  • The values in your UPDATE command should have quotes around them. Like: 

    $temp1 = "UPDATE Status SET ImportFreeSpace = '{0}' WHERE NetBios_Name = '{1}' " -f $PC.FreeSpace, $PC.NetBiosInfo