Put SQL output in variable

[SvanGool]

Hello,

I've got several forms which get data from SQL views and output them in a datagridview, this workrs great.
But now I want the put the output into variables but I cannot get this to work.
So the table has name, adres, date of birth etc. and I want each of them stored in a separated variable. My output is always one record.

Can anybody give me a hint?

Code: [Select all] [Expand/Collapse] [Download] (Untitled.ps1)

1.     function Get-Query_VW_Controlevragen
2.     {
3.         $Connection = New-Object System.Data.SQLClient.SQLConnection
4.         $Connection.ConnectionString = 'Data Source=SVRDB21;Initial Catalog=IBRM_Output;Integrated Security=True;User ID=;Password='
5.         $Connection.Open()
6.         $Command = New-Object System.Data.SQLClient.SQLCommand
7.         $Command.Connection = $Connection
8.         $Command.CommandText = "select * from SD.VW_Controlevragen where [SamAccountname] = '$($SamAccountName)'"
9.         $Reader = $Command.ExecuteReader()
10.         while ($Reader.Read())
11.         {
12.             $Reader.GetValue($1)
13.         }
14.         $Connection.Close()
15.     }
16.    
17.     Get-Query_VW_Controlevragen
18.    

[jvierra]

How can you take an output that has many rows and columns and put it in a variable? A table is a variable.

If you load the data correctly you will have a table variable.
The following will return a table with one row.

Code: Select all

function Get-Query_VW_Controlevragen{
    Param(
         $SamAccountName
     )
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = 'Data Source=SVRDB21;Initial Catalog=IBRM_Output;Integrated Security=True;User ID=;Password='
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = "select * from SD.VW_Controlevragen where [SamAccountname] = '$SamAccountName'"
    $Reader = $Command.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    [void]$dt.Load($Reader)
    $Connection.Close()
    ,$dt
}
$dt = Get-Query_VW_Controlevragen $samAccontName

It is best to pass in the variables rather than default to outer scopes.

With your original function you can do this to get an array:

$values = Get-Query_VW_Controlevragen

You can insert the array values into multiple variables like this:

$var1,$var2,$var3 = Get-Query_VW_Controlevragen

I recommend using the table.