Where (SQL) Clause does not accept string Variable

hi all

i have following code below throws error.

i want change display name in excel file based on batch numbers , email

exception calling "executenonquery" "0" argument(s): "no value given 1 or more required parameters."
at line:7 char:1
+ $sqlcommand.executenonquery()

if change strquery line from:

$strquery = "update [$strsheetname] set displayname = 'heyhey' batch = $batche , email $colme"

to

$strquery = "update [$strsheetname] set displayname = 'heyhey' batch = $batche , email 'apple9'"

it works fine not string variable $colme. $batche variable works ok number in xlsx file.

can please?

#educationtest1.xlsx holds columns batch; email; displayname; firstname; lastname

#i want change display name based on batch numbers , email

$strfilename ="c:\dev\educationtest1.xlsx"
$strsheetname = 'sheet1$'
$strprovider = "provider=microsoft.ace.oledb.12.0"
#$strprovider = "provider=microsoft.jet.oledb.4.0"
$strdatasource = "data source = $strfilename"
$strextend = "extended properties=excel 8.0"


#create query here loops array of batch number , update

$batches = 1003, 1004
$colme = 'apple9'

foreach ($batche in $batches){

$strquery = "update [$strsheetname] set displayname = 'heyhey' batch = $batche , email $colme"

$objconn = new-object system.data.oledb.oledbconnection("$strprovider;$strdatasource;$strextend")
$sqlcommand = new-object system.data.oledb.oledbcommand($strquery)
$sqlcommand.connection = $objconn
$objconn.open()
$sqlcommand.executenonquery()
$objconn.close()
}

on surface of example, error justified email string.

i expect powershell indeed substituting value variable $colme, not quite think think.

see, powershell variable definition $colme interpreted string value of apple9 (i.e. without single quotes, not 'apple9'. this, of course, generate sql error you're seeing.

define $colme $colme = "'apple9'" (i.e. using double quotes enclose single quotes) , should fine.

cheers,
lain

Windows Server  >  Windows PowerShell