Insert into Access DB help

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

This topic contains 4 replies, has 4 voices, and was last updated by

 
Participant
2 months, 1 week ago.

  • 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: 8
    Replies: 1040
    Points: 3,433
    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: 38
    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()
    
    

You must be logged in to reply to this topic.