I'm running these sql commands to update the NDSM_DN column of my database
with information that was parsed from the DN column of my database. When I
attempt the Update, it updates the NDSM_DN column on all the rows to the
parsed value from the last row. I'm sure I've just got the SQL syntax
wrong. Can you please help?

=========================================
Database table Example before the script is run:
Name,DN,NDSM_DN
Finance,Finance.BYT.EED,null
Marketing,Marketing.BYT.EED,null
Eng,Eng.BYT.EED,null
=========================================
Database table Example AFTER the script is run:
Name,DN,NDSM_DN
Finance,Finance.BYT.EED,O=EED/OU=BYT/CN=Eng
Marketing,Marketing.BYT.EED,O=EED/OU=BYT/CN=Eng
Eng,Eng.BYT.EED,O=EED/OU=BYT/CN=Eng

Notice that the NDSM_DN values are all set to the last entry in the Eng row.

========================================
Here's the script:
CLS
# Create SqlConnection object, define connection string, and open connection
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=server01;
Database=objectProcessing;user=user;password=passw ord"
$con.Open()

# Define the SELECT statement and create the SqlDataAdapter object
$cmdSelect = "SELECT * FROM T_NDSGroups_09032009"
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmdSelect, $con)

# Create and fill DataTable object
$dt = New-Object System.Data.DataTable
$da.Fill($dt) | Out-Null

# Retrieve the data
Foreach ($row in $dt.rows)
{
$path = $null
$unit=$row.DN.split(".")
For($i=$unit.count-1;$i -ge 0;$i--){
Switch($i){
0{IF($unit[$i] -match
"^..="){$path+="/"+$unit[$i]}Else{$path+="/CN="+$unit[$i]}}
($unit.count-1){If($unit[$i] -match
"^.="){$path=$unit[$i]}Else{$path="O="+$unit[$i]}}
Default{IF($unit[$i] -match
"^..="){$path+="/"+$unit[$i]}Else{$path+="/OU="+$unit[$i]}}
}
}
$row.NDSM_DN=$path
}
# Create SqlCommand object, define UPDATE statement, and set the connection
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "UPDATE T_NDSGroups_09032009 SET NDSM_DN = @NDSM_DN"
$cmd.Connection = $con

## Add parameters to pass values to the UPDATE statement
$cmd.Parameters.Add("@OBJECT_NAME__DN_0", "char", 0, "OBJECT_NAME__DN_0") |
Out-Null
$cmd.Parameters["@OBJECT_NAME__DN_0"].SourceVersion = "Original"

$cmd.Parameters.Add("@NDSM_DN", "char", 400, "NDSM_DN") | Out-Null
$cmd.Parameters["@NDSM_DN"].SourceVersion = "Current"

# Set the UpdateCommand property
$da.UpdateCommand = $cmd

# Update the database
$RowsUpdated = $da.Update($dt)

# Display the number of rows updated
Write-Host Number of rows updated: $RowsUpdated


# Close the connection
$con.Close()

BBF wrote:

There is no parameter named "@OBJECT_NAME__DN_0" in your query. I assume it
would go in the hypothetical WHERE clause...

--
J.

I'm having trouble understanding how parameters are used. It looks like this
query is supposed to set all the NDSM_DN's to the current value of the
NDSM_DN column in each row, but it isn't. It sets the NDSM_DN value in each
row to the last NDSM_DN value in the table.?.? I must have something wrong.
Please help.

=========================================
Database table Example before the script is run:
Name DN NDSM_DN
Finance Finance.BYT.EED null
Marketing Marketing.BYT.EED null
Eng Eng.BYT.EED null
=========================================
Database table Example AFTER the script is run:
Name DN NDSM_DN
Finance Finance.BYT.EED O=EED/OU=BYT/CN=Eng
Marketing Marketing.BYT.EED O=EED/OU=BYT/CN=Eng
Eng Eng.BYT.EED O=EED/OU=BYT/CN=Eng

Notice that the NDSM_DN values are all set to the last entry in the Eng row.

CLS
# Create SqlConnection object, define connection string, and open connection
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=server01;
Database=objectProcessing;user=user;password=passw ord"
$con.Open()

# Define the SELECT statement and create the SqlDataAdapter object
$cmdSelect = "SELECT * FROM T_NDSGroups_09032009"
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmdSelect, $con)

# Create and fill DataTable object
$dt = New-Object System.Data.DataTable
$da.Fill($dt) | Out-Null

# Retrieve the data
Foreach ($row in $dt.rows)
{
$path = $null
$unit=$row.DN.split(".")
For($i=$unit.count-1;$i -ge 0;$i--){
Switch($i){
0{IF($unit[$i] -match
"^..="){$path+="/"+$unit[$i]}Else{$path+="/CN="+$unit[$i]}}
($unit.count-1){If($unit[$i] -match
"^.="){$path=$unit[$i]}Else{$path="O="+$unit[$i]}}
Default{IF($unit[$i] -match
"^..="){$path+="/"+$unit[$i]}Else{$path+="/OU="+$unit[$i]}}
}
}
$row.NDSM_DN=$path
}
# Create SqlCommand object, define UPDATE statement, and set the connection
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "UPDATE T_NDSGroups_09032009 SET NDSM_DN = @NDSM_DN"
$cmd.Connection = $con

## Add parameters to pass values to the UPDATE statement
$cmd.Parameters.Add("@NDSM_DN", "char", 400, "NDSM_DN") | Out-Null
$cmd.Parameters["@NDSM_DN"].SourceVersion = "Current"

# Set the UpdateCommand property
$da.UpdateCommand = $cmd

# Update the database
$RowsUpdated = $da.Update($dt)

# Display the number of rows updated
Write-Host Number of rows updated: $RowsUpdated


# Close the connection
$con.Close()

With Jeroen's comment, I added a where statement to the query. Each row is
now updated with the appropriate information.