0

In Powershell, I am doing a SQL query for a single row of data. Lets say $data for example.

The response from the query a System.Data.DataSet type. Within it, there is a tables property that has the data I need.

$data.Tables

ServerName          : Server15
SamAccount          : Admin-Server15
LastPWDReset        : 1/15/2019 12:00:00 AM
LastPWDResetAttempt :

I don't intend to write this data back out of anything. Instead, I want to display it, and convert the empty "LastPWDResetAttemp" to "NONE" where it is blank.

I thought it would be done like this:

 $data.Tables.lastPWDResetAttempt = "None"

but that gives me an error The property 'lastPWDResetAttempt' cannot be found on this object. Verify that the property exists and can be set.

I can't help but think I am missing some conversion from "Dataset" to "String".

I've tried to-string but in doing so, I ended up with just a string of data and not the headings. Nothing I could update, or easily use to build my eventual table.

My work around:

$webdata = "" | select ServerName,SamAccount,LastPWDReset,LastPWDResetAttempt
$webdata.ServerName = $data.tables.servername
$webdata.SamAccount = $data.tables.samaccount
$webdata.LastPWDReset = $data.tables.LastPWDReset
$webdata.LastPWDResetAttempt = $data.tables.LastPWDResetAttempt

$webdata.LastPWDResetAttempt = "Never"

works. I just can't believe there isn't an easier way, nor do I understand why I can view a list of the data, just to not then be able to set it.

  • $data.Tables[0].lastPWDResetAttempt = "None" gives me the same "The property 'lastPWDResetAttempt' cannot be found on this object. Verify that the property exists and can be set." If I try to view it is empty, and of a type "System.DBNull" –  Mar 29 '19 at 17:41
  • This seems to be a known issue, the more I look.. **That DBNull behavior is strange! Why doesn't it behave as expected?** was found on one web site, with a reference to the shutdown Microsoft Connect web site. Several conversations also about checking for NULL - https://stackoverflow.com/questions/22285149/dealing-with-system-dbnull-in-powershell but nothing about being able to set the values. –  Mar 29 '19 at 17:53

1 Answers1

0

I think it is because Tables is an array and does not have the property LastPWDResetAttempt.

You can try the following instead:

$data.Tables[0].Rows[0].LastPWDResetAttempt = "None"

Also I think your workaround, though it may contain more lines of code, is actually a better solution though.

Itchydon
  • 2,572
  • 6
  • 19
  • 33
talk2prashant
  • 1