Insert into Access DB help

Welcome Forums General PowerShell Q&A Insert into Access DB help

Viewing 4 reply threads
  • Author
    Posts
    • #164355
      Participant
      Topics: 1
      Replies: 1
      Points: 15
      Rank: Member

      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:

      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()
      }
    • #164370
      Senior Moderator
      Topics: 9
      Replies: 1240
      Points: 4,462
      Helping Hand
      Rank: Community Hero

      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"
      
    • #164379
      Participant
      Topics: 2
      Replies: 2
      Points: 5
      Rank: Member

      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

    • #164418
      Participant
      Topics: 0
      Replies: 2
      Points: 42
      Rank: Member
      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.
    • #164701
      Participant
      Topics: 1
      Replies: 1
      Points: 15
      Rank: Member

      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;

      $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()
      
      
Viewing 4 reply threads
  • The topic ‘Insert into Access DB help’ is closed to new replies.