whorsfall
asked on
Trying to do a SQL insert from powershell to a MSAccess database
Hi,
I am trying to do a SQL insert into MSAccess database and coming up with an error in powershell.
Do u have any suggestions on how I can fix the code below:
The error I get is:
Exception setting "Connection": "Cannot convert the "System.__ComObject" value of type "System.__ComObject#{00001 550-0000-0 010-8000-0 0aa006d2ea 4}" to type "System.Data.OleDb.OleDbCo nnection". "
At C:\ad\Untitled1.ps1:11 char:5
+ $test_cmd.Connection = $db
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], SetValueInvocationExceptio n
+ FullyQualifiedErrorId : ExceptionWhenSetting
Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery: Connection property has not been initialized."
At C:\ad\Untitled1.ps1:14 char:5
+ $test_cmd.ExecuteNonQuery( )
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException
I am trying to do a SQL insert into MSAccess database and coming up with an error in powershell.
Do u have any suggestions on how I can fix the code below:
clear
$db = New-Object -com "ADODB.Connection"
$db.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\ad\VP.accdb")
$sql = "insert into vp_debug (Machine_Name) Values ('Test')"
$test_cmd = New-Object System.Data.OleDb.OleDbCommand
$test_cmd.Connection = $db
$test_cmd.CommandText = $sql
$test_cmd.ExecuteNonQuery()
The error I get is:
Exception setting "Connection": "Cannot convert the "System.__ComObject" value of type "System.__ComObject#{00001
At C:\ad\Untitled1.ps1:11 char:5
+ $test_cmd.Connection = $db
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], SetValueInvocationExceptio
+ FullyQualifiedErrorId : ExceptionWhenSetting
Exception calling "ExecuteNonQuery" with "0" argument(s): "ExecuteNonQuery: Connection property has not been initialized."
At C:\ad\Untitled1.ps1:14 char:5
+ $test_cmd.ExecuteNonQuery(
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.