We have seen how to add a record to an Access table by passing in the whole SQL string. This is OK when we want to add a single record or possibly not fill all fields in a row.
001
002 003 004 005 006 007 008 009 010 011 012 |
function Add-AccessRecord {
param ( [string]$sql, [System.Data.OleDb.OleDbConnection]$connection, [string]$table, [string]$values ) $sql = "INSERT INTO $table VALUES ($values)" $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection) $cmd.ExecuteNonQuery() } |
If we alter our function to create the SQL as shown we can pass the table name and values into the function.
Add-AccessRecord -connection $db -table test1 -values ‘"John","Smith","01/07/1981"’
This will add a record and start applying the values in sequence to the row. it assumes that values are supplied for all fields.
At the moment the function effectively breaks our previous version because $sql is redefined in the function. We need to test whether we should create a SQL record or use the SQL statement supplied. We could check if the $sql parameter is given a value but a better way would be to use parameter sets which we will look at after we have learned to read data from a table.