Powershell SQL returns

Question

I am trying to view the results of a sql query into a remote server. My issue is that on return i see the first value repeated for each of the other values.

Here is the code:

#Connect to VPN
cls
C: 
cd "C:\Program Files (x86)\Cisco Systems\VPN Client"
& ".\vpnclient.exe" connect WWVPN1 user sceris pwd ******

$vendorNumber = "2130196"
$vendorName = ""
$invoiceNumber = "1362433"

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=wwfinance; Initial Catalog=ScerIS; Integrated Security=False; uid=Peter; pwd=*****; MultipleActiveResultSets=true")
## Open DB Connection
$conn.Open()

 $sqlText = "SELECT UdiValue1, UdiValue37, UdiValue38, UdiValue3
             FROM ScerIS.dbo.indexedRangesView_4
             WHERE (UdiValue37 like '%$vendorName%' OR UdiValue38 like '$VendorNumber') AND UdiValue3 = '$invoiceNumber'"

    $cmd = New-Object System.Data.SqlClient.SqlCommand($sqlText, $conn)

    $Reader = $cmd.ExecuteReader()

  while ($Reader.Read()) {
     $ArchiveDate = $Reader.GetValue($1)
     $VendorNumber = $Reader.GetValue($2)
     $VendorName = $Reader.GetValue($3)
     $InvoiceNumber = $Reader.GetValue($4)
    }

    write-host $ArchiveDate
    write-host $VendorNumber
    write-host $VendorName
    write-host $InvoiceNumber


    $conn.close()


    #Disconnect from VPN
cd "C:\Program Files (x86)\Cisco Systems\VPN Client"
& ".\vpnclient.exe" disconnect

The output will show the archive date 4 times once for each write-host. How can i successfully get the other values to display?

Sample Output

9/4/2015 12:00:00 AM 9/4/2015 12:00:00 AM 9/4/2015 12:00:00 AM 9/4/2015 12:00:00 AM

You're not writing anything until after you've looped through the entire result set. $ArchiveDate, $VendorNumber, $VendorName, and $InvoiceNumber are all going to have the value of the last iteration of the loop. Given what you're doing here, I might use a DataAdapter instead of a DataReader, but its not clear what you're trying to display or why. — Bacon Bits
– Bacon Bits, Commented Sep 11, 2015 at 20:38
Even if it is the last iteration of the loop that wouldn't explain why Archive Date get's written out 4 times. — Peter3
– Peter3, Commented Sep 11, 2015 at 20:42

tspauld · Accepted Answer · 2015-09-11 20:41:53Z

3

$1, $2, $3, and $4 are never defined. If you want to get the values from the first 4 columns use:

while ($Reader.Read()) {
  $ArchiveDate = $Reader.GetValue(0)
  $VendorNumber = $Reader.GetValue(1)
  $VendorName = $Reader.GetValue(2)
  $InvoiceNumber = $Reader.GetValue(3)
}

answered Sep 11, 2015 at 20:41

tspauld

3,5222 gold badges27 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter3 Over a year ago

Thanks, for some reason i was under the impression that those were special powershell syntax like $true or after a -match you can use $Matches. not sure why though

Collectives™ on Stack Overflow

Powershell SQL returns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related