locked
Populating DB via powershell script broken after update to Windows 10 RRS feed

  • Question

  • Hi everybody,
    I recently updated from Windows 8.1 to 10 and noticed some weird behavior in some scripts, which worked fine previously.

    I'm using powershell to update an access 2013 database via ADO, and for some reason, the data type seems to be passed incorrectly by the script now. To troubleshoot this, I created a new script which only contains the problematic part:

    $adOpenStatic = 3
    $adLockOptimistic = 3
    
    $cn = new-object -comobject ADODB.Connection
    $rs = new-object -comobject ADODB.Recordset
    
    $DataSource = "F:\Documents\Docs\scripts\manictimesync\testdb.accdb"
    
    $cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=$DataSource")
    $rs.Open("act3", $cn, $adOpenStatic, $adLockOptimistic)
    
    $properties = @{"ID"=25;"DisplayName"="abc"}
    $values = New-Object -TypeName PSCustomObject -Property $properties
    
    for ($i=0; $i -lt 1; $i++){
        $rs.AddNew()
    
        foreach ($property in $values[$i].psobject.properties)
        {
            Write-Host "Field Name: $($rs.Fields.Item($property.Name).Name)"
            Write-Host "Field Type: $($rs.Fields.Item($property.Name).Type)"
            Write-Host "Field Value: $($rs.Fields.Item($property.Name).Value)"
    
            Write-Host "Data Name: $($property.Name)"
            Write-Host "Data Value: $($property.Value)"
            Write-Host "Data type: $($property.Value.GetType())"
    
            $rs.Fields.Item($property.Name).Value = $property.Value
        }
    }

    The output I get from running this:

    Field Name: ID Field Type: 3 Field Value: 0 Data Name: ID Data Value: 25 Data type: int Field Name: DisplayName Field Type: 202 Field Value: Data Name: DisplayName Data Value: abc Data type: string

    #Error: Specified cast is not valid.


    The line which triggers the issue is

    $rs.Fields.Item($property.Name).Value = $property.Value

    It seems that I can't pass any string values to the database. It also doesn't matter if I'm using the long or short string data type.

    If, however, I try to pass a string value directly, without assigning it to a variable, everything works fine, e.g.

    $rs.Fields.Item($property.Name).Value = $property.Value
    #Error
    
    $rs.Fields.Item($property.Name).Value = "abc"
    #Works fine

    This made me thing that the issue is somehow related in how powershell passes the value. Do you have any idea what might cause this issue or, even better, how to fix it?

    Saturday, August 8, 2015 11:22 PM

Answers

  • Here is the correct way to use Access isn PowerShell:

    $DataSource='c:\test\testdb.accdb'
    $conStr="Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=$DataSource"
    $conn=New-Object System.Data.OleDb.OleDbConnection($conStr)
    
    $conn.Open()
    $adcmd = $conn.CreateCommand()
    $adcmd.CommandText='select * from act3'
    $oleadptr=New-Object System.Data.OleDb.OledbDataAdapter($adcmd)
    $cb=New-Object System.Data.OleDb.OleDbCommandBuilder($oleadptr)
    
    $dt = New-object System.Data.DataTable
    $oleadptr.Fill($dt) 
    
    $id=56
    $displayName='Hello'
    for($i=0; $i -lt 10;$i++){
    	$row=$dt.NewRow()
    	$row.DisplayName=$displayName
    	$row.ID=$id
    	$row=$dt.Rows.Add($row)
    }
    $oleadptr.Update($dt)
    $conn.Close()
    


    \_(ツ)_/

    • Marked as answer by mmuffins Sunday, August 9, 2015 12:13 PM
    Sunday, August 9, 2015 12:59 AM

All replies

  • Another example which maybe shows the issue a bit clearer:


    $adOpenStatic = 3
    $adLockOptimistic = 3
    
    $cn = new-object -comobject ADODB.Connection
    $rs = new-object -comobject ADODB.Recordset
    
    $DataSource = "F:\Documents\Docs\scripts\manictimesync\testdb.accdb"
    
    $cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=$DataSource")
    $rs.Open("act3", $cn, $adOpenStatic, $adLockOptimistic)
    
    $id = 44
    $displayname = "abc"
    
    $rs.AddNew()
    
    $rs.Fields.Item("ID").Value = $id #works fine
    $rs.Fields.Item("DisplayName").Value = "abc" #works fine
    
    $rs.UpdateBatch()
    
    
    $rs.AddNew()
    
    $rs.Fields.Item("ID").Value = $id #works fine
    $rs.Fields.Item("DisplayName").Value = $displayname #error -> Specified cast is not valid.
    
    $rs.UpdateBatch()


    Saturday, August 8, 2015 11:40 PM
  • This forces the conversion to occur correctly:

    $rs.Fields.Item("DisplayName").Value ="$displayname"


    \_(ツ)_/

    Sunday, August 9, 2015 12:16 AM
  • Here is the correct way to use Access isn PowerShell:

    $DataSource='c:\test\testdb.accdb'
    $conStr="Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;Data Source=$DataSource"
    $conn=New-Object System.Data.OleDb.OleDbConnection($conStr)
    
    $conn.Open()
    $adcmd = $conn.CreateCommand()
    $adcmd.CommandText='select * from act3'
    $oleadptr=New-Object System.Data.OleDb.OledbDataAdapter($adcmd)
    $cb=New-Object System.Data.OleDb.OleDbCommandBuilder($oleadptr)
    
    $dt = New-object System.Data.DataTable
    $oleadptr.Fill($dt) 
    
    $id=56
    $displayName='Hello'
    for($i=0; $i -lt 10;$i++){
    	$row=$dt.NewRow()
    	$row.DisplayName=$displayName
    	$row.ID=$id
    	$row=$dt.Rows.Add($row)
    }
    $oleadptr.Update($dt)
    $conn.Close()
    


    \_(ツ)_/

    • Marked as answer by mmuffins Sunday, August 9, 2015 12:13 PM
    Sunday, August 9, 2015 12:59 AM
  • That... that actually works. And the new method is actually way better than what I.

    Thanks a lot, you really saved my day!

    Sunday, August 9, 2015 12:13 PM
  • The issue is not in W10.  It exists in all versions of Windows with PowerShell 3 and later.  I believe the issue is a result of how PowerShell sees that particular COM object.  Avoid COM when the is a Net class.

    \_(ツ)_/

    Sunday, August 9, 2015 12:15 PM