0

I have the following PnP PowerShell script:

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$DBServer;Integrated Security=True;Initial Catalog=$DBName")                        
$conn.Open();                        
$query = $Query                        
$dap = new-object System.Data.SqlClient.SqlDataAdapter($query,$conn);                        
$dt = new-object System.Data.DataTable;                        
$null = $dap.Fill($dt)  
      foreach($r in $dt.Rows )   {                         
$SPUserEmail = $r["email_address"];
$SPuser = (Get-PnPUser | ? Email -eq ($SPUserEmail));}

but i will always get this error:

The specified user  could not be found.

but if I type the email address manually i will get the user:

$SPuser = (Get-PnPUser | ? Email -eq test@test.com);

although when I do Write-Host $SPUserEmail I will get test@test.com

So what could be the reason?

Thanks

mklement0
  • 382,024
  • 64
  • 607
  • 775
John John
  • 1
  • 72
  • 238
  • 501
  • 1
    Is `$r` a single-item hashtable? I couldn't see why filtering through `Where-Object` would give you that error if it wasn't passed directly to the cmdlet via a parameter – Abraham Zinala Jan 04 '23 at 21:42
  • @AbrahamZinala `$r` is a value returned from SQL server – John John Jan 04 '23 at 21:44
  • @AbrahamZinala i posted more code – John John Jan 04 '23 at 21:46
  • 2
    Can you provide a sample of `$r`. Does `Get-PNPUser` return a list of users? – Abraham Zinala Jan 04 '23 at 22:39
  • @AbrahamZinala `$R` IS `System.Data.DataRow` .. AND `Get-PNPUser` return one user only – John John Jan 04 '23 at 23:00
  • 2
    If `? Email -eq test@test.com` works, the implication is that `$r["email_address"]` either doesn't return a _single value_ or, if it does, that that value contains _hidden control characters_. – mklement0 Jan 04 '23 at 23:06
  • @mklement0 it will return a single value because when i write this `Write-Host $SPUserEmail` I will get `test@test.com` – John John Jan 04 '23 at 23:09
  • 1
    Inspect your string for unexpected hidden control characters. A quick way to do that is character-by-character inspection with `& { [int[]] [char[]] $Args[0] | % { '0x{0:x} [{1}]' -f $_, [char] $_ } } $yourString`. A more sophisticated way is via the `Debug-String` function, available [as an MIT-licensed Gist](https://gist.github.com/mklement0/7f2f1e13ac9c2afaf0a0906d08b392d1), as demonstrated in the bottom section of [this answer](https://stackoverflow.com/a/52648684/45375). – mklement0 Jan 04 '23 at 23:13
  • @mklement0 i tried this `Write-Host & { [int[]] [char[]] $Args[0] | % { '0x{0:x} [{1}]' -f $r["email_address"], [char] $r["email_address"] } } $r["email_address"] ` but i got syntax error – John John Jan 04 '23 at 23:21
  • 1
    @johnGu, don't use `Write-Host`, which is [usually the wrong tool](http://www.jsnover.com/blog/2013/12/07/write-host-considered-harmful/). Just use the command as-is. [This answer](https://stackoverflow.com/a/58240860/45375) explains why `Write-Host` is unhelpful for _visualizing_ results. – mklement0 Jan 04 '23 at 23:26
  • @mklement0 ok `& { [int[]] [char[]] $Args[0] | % { '0x{0:x} [{1}]' -f $r["email_address"], [char] $r["email_address"] } } $r["email_address"]` will return this error `Cannot convert value "test@test.com" to type "System.Char". Error: "String must be exactly one character long."` – John John Jan 04 '23 at 23:30
  • 1
    @johnGu, that's not the command I suggested; notably, only the `$yourString` part of it needed substitution with your specific value, `$r["email_address"]` – mklement0 Jan 04 '23 at 23:33
  • @mklement0 so what i need to do? – John John Jan 04 '23 at 23:56
  • 1
    @johnGu, that's not what I get when I run `& { [int[]] [char[]] $Args[0] | % { '0x{0:x} [{1}]' -f $_, [char] $_ } } 'test@test.com'` – mklement0 Jan 04 '23 at 23:59
  • @mklement0 this what i will get `0x74 [t] 0x65 [e] 0x73 [s] 0x74 [t] 0x40 [@] 0x74 [t] 0x65 [e] 0x73 [s] 0x74 [t] 0x2e [.] 0x63 [c] 0x6f [o] 0x6d [m]` – John John Jan 05 '23 at 00:02
  • @mklement0 so not sure where is the issue now? – John John Jan 05 '23 at 00:03
  • That is the expected result - do you get the same result with `$r["email_address"]` instead of literal `'test@test.com'`? If so, then I'm out of ideas. Try to provide a [mcve] or as close as you can get to one. – mklement0 Jan 05 '23 at 02:04

0 Answers0