with powershell i'm passing queries to sql in a loop. how can i cumulate what i'm receiving from sql (datatables) ?

hello,

in a loop i'm passing queries to sql. ok, i'm receiving the responses.
now i'm trying to group in a datatable all the respoinses i'm receiving which are datatables.
i've created the same datatable in order to be able to add to it but this is not working.

i need help.

$allmyResulst =
    $allResults = New-Object System.Data.DataTable
    $allResults.Columns.Add("PersonID",[System.Type]::GetType("System.Int32"))
    $allResults.Columns.Add("firstname",[System.Type]::GetType("System.String"))
    $allResults.Columns.Add("lastname",[System.Type]::GetType("System.String"))

foreach($aline in $allFileLines)
{
     $query = "Select PersonID, firstname, lastname, email from table where firstname='lala'
     $results = pass-queryagainstDBserver $theConnection $query
     # ^ i receive my results ok
     $allresults.rows.add($results)    <- this is not adding my new lines:
}

thank you in advance
toshi_Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dustin SaundersConnect With a Mentor Director of OperationsCommented:
Without testing, it looks like you're trying to add a DataTable to DataTable.Rows (and they're different object types).

What object type is returned by your custom function, DataTable?

Do your results have the same number of columns, because it looks like you're selecting 4 columns and adding to a table with 3.

Try this, to create a compatible row and fill it with values from a differing table, then add it back to the main table.
foreach($aline in $allFileLines)
{
     $query = "Select PersonID, firstname, lastname, email from table where firstname='lala'"
     $results = pass-queryagainstDBserver $theConnection $query 
     # ^ i receive my results ok 
     foreach ($thisRow in $results.Rows)
     {
        $addRow = $allResults.NewRow()
        $addRow["PersonID"] = $thisRow.PersonID
        $addRow["firstname"] = $thisRow.firstname
        $addRow["lastname"] = $thisRow.lastname
        $allResults.Rows.Add($addRow)
     }
}

Open in new window

0
 
toshi_Author Commented:
Hello Dustin,

thank you for your reply,

indeed i had forgotten to  add a column.
ok, indeed i could copy one Datarow from one Datatable to the other. Creating a new datarow works.

thank you.

Toshi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.