Categories

Modifying the Add-AccessRecord function

As we discovered last time we need to alter the Add-AccessRecord function to enable us to define the fields as well as the values.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
function Add-AccessRecord {
# .ExternalHelp Maml-AccessFunctions.XML
[CmdletBinding()]
param (
    [parameter(ParameterSetName="sql")]
    [string]$sql,
   
    [System.Data.OleDb.OleDbConnection]$connection,
   
    [parameter(ParameterSetName="field")]
    [parameter(ParameterSetName="value")]
    [string]$table,

    [parameter(ParameterSetName="field")]
    [string]$fields,
   
    [parameter(ParameterSetName="field")]
    [parameter(ParameterSetName="value")]
    [string]$values      
)

    switch ($psCmdlet.ParameterSetName){
        value    {$sql = "INSERT INTO $table VALUES ($values)" } 
        field    {$sql = "INSERT INTO $table ($fields) VALUES ($values)" } 
    }
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

This is a simple matter of adding the $fields parameter and another parameter set.  A switch statement is used to create the correct SQL statement.  if we use the –sql parameter then that statement is passed straight through.

We now have the flexibility in this function to add records in the manner in which we best feel comfortable.  A couple of examples are provided

Add-AccessRecord -sql "INSERT INTO test1 (Firstname, lastname, DOB) VALUES ('John', 'Blue', '24/12/1981')" -connection $db

Add-AccessRecord -connection $db -table test1 -fields "Firstname, lastname, DOB" -values "'Jo', 'Green', '24/11/1981'"

I think I’m going to make the connection parameter mandatory because I keep forgetting to add it in.

The other point to notice is the

#  .ExternalHelp   Maml-AccessFunctions.XML 

statement.  This is used to link the function to an external help file.  More on that later.

Technorati Tags: ,,

Leave a Reply