Insert into Access DB help

PowerShell Help
1

Trying to create a basic guest sign-in system (using windows forms through powershell) and failing when inserting into access db. I have tried to follow the formatting suggested online, but cannot get past these 2 errors;

  1. Exception calling "ExecuteNonquery" with "0" arguments: Syntax error in INSERT INTO statement
  2. Update-DB: the term 'update-DB' is not recognised as a name of a cmdlet etc..
now obviously its very easy to understand- but I can't figure out why, there is a sytax error in my insert and what the correct command is for update-db (unless i missing a module :/)?

My code:
[pre]function Submit {

$info = [pscustomobject] @{
F1 = ‘$FirstnameTextBox.Text’
F2 = ‘$SurnameTextbox.Text’
F3 = ‘$CarRegTextbox.Text’
F4 = ‘$CompanyVisitingDropdownBox.SelectedValue’
F5 = ‘Get-Date’
F6 = ‘NO’
}
$dbase = ‘C:\Signin.accdb’
$conn=New-Object System.Data.OleDb.OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$dbase”)
$conn.Open()
$query = “INSERT INTO Table ([FirstName], [Surname], [CarReg], [CompanyVisiting], [DateTimeIn], [SignedOut]) VALUES (‘$($info.F1)’,‘$($info.F2)’,‘$($info.F3)’,‘$($info.F4)’,‘$($info.F5)’,‘$($info.F6)’)”
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
$result = $cmd.ExecuteNonQuery()

Write-Output $result

Update-DB $info $dbase
$conn.Close()
}[/pre]

2

I can point out one issue here,

#Wrong
$info = [pscustomobject] @{
    F1 = '$FirstnameTextBox.Text'
    F2 = '$SurnameTextbox.Text'
    F3 = '$CarRegTextbox.Text'
    F4 = '$CompanyVisitingDropdownBox.SelectedValue'
    F5 = 'Get-Date'
    F6 = 'NO'
}


#Correct
$info = [pscustomobject] @{
    F1 = $FirstnameTextBox.Text
    F2 = $SurnameTextbox.Text
    F3 = $CarRegTextbox.Text
    F4 = $CompanyVisitingDropdownBox.SelectedValue
    F5 = Get-Date
    F6 = 'NO'
}

you don’t need quotes around them, having a quote will give you unexpected value.

example

$Variable = Get-Date
$Variable | Format-List *
$Variable.Day
'$Variable.Day'
"$Variable.Day"
3

Thanks Kvprasoon,

You are right, wrapping a variable in ‘’ treats it as text so that’s my bad…

I will get this updated on Monday and see if this resolves my syntax issue - though, I would have thought the DB would just accept the plain text anyway so not sure if it will… (DB shouldn’t care if it’s accepting ‘$FirstName.TextBox.Text’ or ‘Rhys’ (as the actual value) for example).

I will be able to correct it to actually input the values though so cheers for that. Just struggling for content online as I know this method isn’t exactly… new and up to date (SQL would be much preferred but this is only a very small system). In the meantime I’m just going to look for any reference about the update-db command I was advised to use with this connection method

4
  1. Exception calling "ExecuteNonquery" with "0" arguments: Syntax error in INSERT INTO statement
    • There is a syntax error in your insert statement. The first rule of troubleshooting code like this is that you are just executing the string value of $Query so just output that value so you can see exactly what the value of $query is. You can then try to execute that query yourself against the database and verify. You can do this by using : Write-Output $query after it is defined.
    • In your example, the table name is 'table' which is a reserved word. If you actually called the table 'table' then you need to use identifiers. In this case you would change it to INSERT INTO [table] ([FirstName], [Surname].......... However, you should never call a table 'table'.
2. Update-DB: the term 'update-DB' is not recognised as a name of a cmdlet etc..
    • If Update-db is not recognized as a command, this means that you did not define the function Update-DB or import the module you need. This is not a standard command-built into Powershell so you would need to know where this is coming from and get that command installed/imported.
5

Now resolved - many thanks for both your help. amending the ‘’ around variables, changing table name and removing the update-db command (it wasn’t needed after all) is now inserting data into my access DB :)!

For anyone interested… this was the final result;

[pre]

$info = [pscustomobject] @{
F1 = $FirstnameTextBox.Text
F2 = $SurnameTextbox.Text
F3 = $CarRegTextbox.Text
F4 = $CompanyVisitingDropdownBox.SelectedItem
F5 = Get-Date
F6 = ‘NO’
}
$dbase = ‘C:\Signin.accdb’
$conn=New-Object System.Data.OleDb.OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$dbase”)
$conn.Open()
$query = “INSERT INTO SignIns ([FirstName], [Surname], [CarReg], [CompanyVisiting], [DateTimeIn], [SignedOut]) VALUES (‘$($info.F1)’,‘$($info.F2)’,‘$($info.F3)’,‘$($info.F4)’,‘$($info.F5)’,‘$($info.F6)’)”
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
$result = $cmd.ExecuteNonQuery()
$conn.Close()

[/pre]