Setting Variables from SQL Datatable

by lmessner at 2012-12-10 17:02:24

Hi all,

First off I should mention that I’ve only been using PowerShell for little over a week now, and while I seem to be picking it up quite quickly, I’ve been scratching my head over this problem for a few days now… I would also like to apologize in advance if my terminology is about face, given what I’ve said above.
At the moment I’m working on a script that creates, moves, updates and deletes Active Directory users and information based on the information in a MySQL database. The script also creates Exchange mailboxes, user directories, sets permissions and so on.

Once the script connects to the required SQL dataset that’s generated by an SQL query, the information is then converted into a table, where I then set variables based on the column and row. My problem is, while the foreach command will grab the required variable, in this case the samAccountName, instead showing the account name, i get an output of ‘system.data.datarows.item’.

How do I display the variable instead of the command set, keeping in mind I need to grab multiple variables from each column and row of the table?

[code2=powershell]$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "server=myserver;database=mydatabase;uid=domain\user;password=password;trusted_connection=true;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = ("DatabaseQuery","DatabaseSet")
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$data = $dataset.Tables[0]
$SqlConnection.Close()

#Start AD PowerShell Module
Import-Module ActiveDirectory
foreach ($data.item in $data.Rows)
{
Add-Content c:\ADIOutput.txt "Checking $data.item[0]"
$name = $data_item[2] + " " + $data_item[3]
$UserExist = (!([ADSI]],OU=Staff,OU=Container,DC=domain,DC=com")))
if ($UserExist -ne "False")
{
Add-Content c:\ADIOutput.txt "User $data.item[0] is not in Staff container."
#ADI Ignore Function - Ignore user if user is in this group
$UserADIIgnore = {Get-QADMemberOf -identity ADI-Ignore -Name $data.item[0]}
if ($UserADIIgnore)
{
Add-Content c:\ADIOutput.txt "User $data.item[0] is not in ADI-Ignore"
New-IADUser -Name $name -sAMAccountname $data_item[0] -Path "OU=Staff,OU=Container,DC=domain,DC=com" -AccountPassword $data_item[1] -Mail $data_item[4] -EnableAccount -UserMustChangePassword[/code2]

My output into the ADIOutput.txt file shows "Checking system.data.datarows.item" instead of "Checking John" for example.

Thanks in advance.
by cmille19 at 2012-12-11 04:41:56
Cross posted question http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/6b9dcaca-e181-44a9-a149-dbcac9e7836c