DataRow displaying the value compared to using it

PowerShell Help
1

Fellow Powershellers.
I am trying to understand why powershell behaves in a specific way. Please read the comments in the code.

	$OtherDB_connection = New-Object System.Data.OleDb.OleDbConnection $OtherDB_connectionString
         $OtherDB_command = New-Object System.Data.OleDb.OleDbCommand $QeuryOtherInstance,$OtherDB_connection
         $OtherDB_command.CommandTimeout = 300
         $OtherDB_connection.Open()
         $OtherDB_adapter = New-Object System.Data.OleDb.OleDbDataAdapter $OtherDB_command
         $OtherDB_dataset = New-Object System.Data.DataSet
         $OtherDB_recordcount =  $OtherDB_adapter.Fill($OtherDB_dataSet)
         $OtherDB_CMdata = $OtherDB_dataset.Tables[0]
         $OtherDB_connection.Close() 
	foreach ($OtherDB_row2 in $OtherDB_CMdata)
         { 
		$OtherDB_rows.InstanceKeyID  #this displays perfectly
		$mystring = " Insert into dbo.TEMP_InstanceDBDriver (InstanceKeyID) Select   $OtherDB_rows.InstanceKeyID"
		
		#the above SELECT does not display the value but delivers  System.Data.DataRow.Item("InstanceKeyID") when I display the $mystring to the output
                #to make this work like one would think it should work, I have to move the original value into a variable and use the variable.

		#for example, below works.
		$mykey = $OtherDB_rows.InstanceKeyID
		$mystring = " Insert into dbo.TEMP_InstanceDBDriver (InstanceKeyID) Select $mykey"


	}	

	#what do I have to do to reference the value in  $OtherDB_rows.InstanceKeyID in teh SELECT string I am building without moving it into a variable?

Thanks Mark

2 
# I am actually wondering that your SQL query string works since it is actually build wrong. PowerShell expends variable instring in a special way. If you want to expand particular properties you can use the subexpression operator for proper variable expansion in quoted strings.
"Insert into dbo.TEMP_InstanceDBDriver (InstanceKeyID) Select $($OtherDB_rows.InstanceKeyID)"
3

Hi Olaf.
Actually my SQL string is correct - it works perfectly using the variable. I have been using this appraoch for some time. But obviously inefficient. I would rather not have to make variables and then use the variables in the select string
So, I will try your $($OtherDB_rows.InstanceKeyID) appraoch.

Will keep you posted. Many thanks. Mark

4

Hi olaf,
You suggestion works great. Many thanks.