New to PowerShell, but not programming. very confused

[MNtobar]

I have no idea what version of PowerShell I am running, but I am using Win10.

PowerShell newbie just trying to understand how things work. Many of years experience scripting SQL database stuff.

Here is my setup:

create table AA_ToddPS
(
Tseq int,
Tnum int,
Tstr varchar(40)
)

Insert into AA_ToddPS (Tseq, Tnum, Tstr)
Values (1, 711, 'Birthday'),(2, 721, 'Anniversary')

Select * from AA_toddPS

Tseq Tnum Tstr
1      711    Birthday
2      721    Anniversary

Here is what I have cobbled together from various sources:

## Just the User tablerows
$query = "SELECT Tseq, tnum, tstr FROM grain.dbo.AA_TODDPS order by Tseq"
$seqquery = "select max(Tseq) FROM grain.dbo.AA_TODDPS"

$seqnew = 0
$seqnew = invoke-Sqlcmd -Query $seqquery -ServerInstance 'WME-VM10-RW'
$tablerows = invoke-Sqlcmd -Query $query -ServerInstance 'WME-VM10-RW' 

## build a "data" table
$Datatable = $Null

$Datatable = New-Object System.Data.DataTable

$Datatable.Columns.Add("Tseq", "System.Int32") | Out-Null
$Datatable.Columns.Add("Tnum", "System.Int32") | Out-Null
$Datatable.Columns.Add("Tstr", "System.String") | Out-Null

$count = 0
foreach ($row in $tablerows)    
{
    $row = $Datatable.NewRow()

    $row.Tseq = $tablerows.Tseq[$count]
    $row.Tnum = $tablerows.Tnum[$count]
    $row.Tstr = $tablerows.Tstr[$count]

    $Datatable.Rows.Add($row)
    $count += 1
}

$count = 1
foreach ($Row in $DataTable.Rows) {
    $insert = "insert into grain.dbo.AA_TODDPS (tseq, tnum, tstr) 
               Values({0}, {1}, '{2}');" -F ($seqnew), ($Row.tnum + ($count * 100)), $Row.tstr

    $result = invoke-Sqlcmd -Query $insert -ServerInstance 'WME-VM10-RW' -OutputSqlErrors $true

   write-host "$result"
   $count = $count + 1
   $seqnew = $seqnew + 1
  
}

What I am anticipating is that when I hit my breakpoint at line 36, I would expect $insert to be

insert into grain.dbo.AA_TODDPS (tseq, tnum, tstr)

         Values (3, 921, 'Anniversary')

What I get is this:

$seqnew is just a variable, but has it been morphed into some object? I am not tied to using this setup to get this done, I just want to have it work, and to understand why.

Thank you for your time.

[#2 · Accepted solution]

@MNtobar 

Yes, your screenshot showing "what you get" makes perfect sense.

As noted in the commandlet's documentation, Invoke-Sqlcmd returns one of the following types (see the OutputAs parameter description for these details):

• DataRows ([System.Data.DataRow])
• DataSet ([System.Data.DataSet])
• DataTables ([System.Data.DataTable])

If you don't specify the -OutputAs parameter, then you get DataTables by default - which is what you see in your screenshot.

• Invoke-Sqlcmd (SqlServer) | Microsoft Docs

If you cross-reference that with your script, here are the points of interest:

• Line 5: An integer value of 0 is assigned to the variable $seqnew, which ends up never being used because:
• Line 6: A new value of type [System.Data.DataTable] is assigned to $seqnew from the output of Invoke-Sqlcmd;
• Line 34: $seqnew, which is still of type [System.Data.DataTable] is included in the formatted string, probably under the assumption it's an integer when it's not.

This is why your screenshot is telling you $seqnew is a [System.Data.DataTable], because as of line 6, that's what it has become.

Cheers,

Lain

[#3]

Thanks,@LainRobertson 

“Everything works the way it is supposed to, even when it isn’t what you expect.” -- Gyro Gearloose (attributed) 😊

Your reply was gracious, instructional and very much appreciated. If have been in environments where the reply would have been laced with derision and the (usually) unhelpful statement of "RTFM!!"

I had looked at the referred to documentation. My background caused me to expect the “Inputs” and “Outputs” sections to be the first items on a doc page. I gave up after reading through all the non-Azure examples and scrolling through several pages of parameters. I should have persevered and read closer. Thanks.

Tobar