Emtpy value detection

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 4 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
STM001
Posts: 60
Last visit: Thu Mar 07, 2024 11:26 am

Emtpy value detection

Post by STM001 »

Hello,

I'm currently running a loop to dynamicly create an SQL Query. I need to detect which value are empty to put NULL instead of '' in the insert query. I tested my code in Windows Powershell ISE and the result is what I want. But when I test in powershell studio, it doesn't work. What I'm doing is a copy of another SQL row while modifying the first 2 values.

Expected result (and got in powershell ISE):
INSERT INTO dbo.database (Type,ID,C3,...) values ('R','9999',NULL,...)

Result in powershell Studio
INSERT INTO dbo.database (Type,ID,C3,...) values ('R','9999','',...)

The code:

Code: Select all

$ConnectionString = 'Data Source=database.server.fqdn;Initial Catalog=databasetable;Integrated Security=True;User ID=;Password='


$QueryString = "SELECT * from dbo.settings WHERE ID = '1039' and TYPE = 'R'"
$command = New-Object System.Data.SqlClient.SqlCommand ($QueryString, $ConnectionString)
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter ($command)
					

$dataset = New-Object System.Data.DataSet
[void]$adapter.Fill($dataset)
					
$sourceSettings = $dataset.Tables[0].Rows[0]
$colCount = $sourceSettings.Table.Columns.Count


$queryInsertTable = ""
$QueryInsertValue = ""
for ($i=0; $i -lt $colCount; $i++){
	# Trapping of source Type, overwritting value in insert query
    if ($($sourceSettings.Table.Columns.ColumnName[$i]) -eq "Type"){
        $queryInsertTable += "'Type'"
        $queryInsertValue += "'R'"
        # Trapping of source ID, overwritting value in insert query
    }elseif ($($sourceSettings.Table.Columns.ColumnName[$i]) -eq "ID"){
        $queryInsertTable += "'ID'"
        $queryInsertValue += "'9999'"
    }else{
    	# All other value
        $queryInsertTable += "'$($sourceSettings.Table.Columns.ColumnName[$i])'"
        # If value is null, I want to insert a NULL statement instead of '' (empty value)
        if ($($sourceSettings[$i]) -eq ""){
            $queryInsertValue += "NULL"
        }else{
            $queryInsertValue += "'$($sourceSettings[$i])'"
        }
        
    }

    if ($i+1 -lt $colCount){
        $queryInsertTable += ","
        $queryInsertValue += ","
    }
}
$QueryString = "INSERT INTO dbo.Settings ($queryInsertTable) values ($QueryInsertValue)"
Write-Host $QueryString
Output I get in ISE:
INSERT INTO dbo.Settings ('Type','ID','UserID','UserDomain','UserPassword','UDShare','UDDir','UDProfiles','SLShare','SLShareDynamicLogging','EventShare','OSInstall','ComputerName','OSDComputerName','Home_Page','JoinDomain','JoinWorkGroup','DomainAdmin','DomainAdminDomain','DomainAdminPassword','MachineObjectOU','ScanStateArgs','LoadStateArgs','ComputerBackupLocation','BackupShare','BackupDir','UserDataLocation','DoCapture','ProductKey','OverrideProductKey','WDSServer','CaptureGroups','AdminPassword','OrgName','FullName','TimeZone','TimeZoneName','TaskSequenceID','KeyboardLocale','InputLocale','UserLocale','SystemLocale','UILanguage','Xresolution','Yresolution','BitsPerPel','Vrefresh','AreaCode','CountryCode','LongDistanceAccess','Dialing','OSDBitlockerMode','BdeInstallSuppress','BdeInstall','OSDBitLockerStartupKeyDrive','OSDBitLockerCreateRecoveryPassword','OSDBitLockerWaitForEncryption','BDEWaitForEncryption','BdeDriveLetter','BdeDriveSize','BdePin','BdeRecoveryKey','BdeKeyLocation','TpmOwnerPassword','OSDBitLockerRecoveryPassword','OSDBitLockerStartupKey','DriverGroup','ServerA','ServerB','ServerC','ResourceRoot','FinishAction','SMSTSRunCommandLineUserName','SMSTSRunCommandLineUserPassword','USMTConfigFile','WipeDisk','_SMSTSORGNAME','WsusServer','USMT3','DriverSelectionProfile','PackageSelectionProfile','WizardSelectionProfile','AutoMode','AutoUsers','ApplicationSuccessCodes','BackupFile','SkipWizard','SkipCapture','SkipAdminPassword','SkipApplications','SkipComputerBackup','SkipDomainMembership','SkipComputerName','SkipUserData','SkipPackageDisplay','SkipLocaleSelection','SkipProductKey','SkipSummary','SkipFinalSummary','SkipBDDWelcome','SkipTimeZone','SkipTaskSequence','SkipBitLocker','DestinationDisk','DestinationPartition','OSDAdapterCount','OSDAdapter0Name','OSDAdapter0MacAddress','OSDAdapter0EnableDHCP','OSDAdapter0IPAddressList','OSDAdapter0SubnetMask','OSDAdapter0Gateways','OSDAdapter0GatewayCostMetric','OSDAdapter0DNSServerList','OSDAdapter0DNSSuffix','OSDAdapter0EnableDNSRegistration','OSDAdapter0EnableFullDNSRegistration','OSDAdapter0EnableLMHOSTS','OSDAdapter0EnableWINS','OSDAdapter0TcpipNetbiosOptions','OSDAdapter0WINSServerList','OSDAdapter0EnableTCPIPFiltering','OSDAdapter0TCPFilterPortList','OSDAdapter0UDPFilterPortList','OSDAdapter0IPProtocolFilterList','OSDDiskIndex','OSDPartitions','OSDPartitions0TYPE','OSDPartitions0FILESYSTEM','OSDPartitions0BOOTABLE','OSDPartitions0QUICKFORMAT','OSDPartitions0VOLUMENAME','OSDPartitions0SIZE','OSDPartitions0SIZEUNITS','OSDPartitions0VOLUMELETTERVARIABLE','OSDPartitions1TYPE','OSDPartitions1FILESYSTEM','OSDPartitions1BOOTABLE','OSDPartitions1QUICKFORMAT','OSDPartitions1VOLUMENAME','OSDPartitions1SIZE','OSDPartitions1SIZEUNITS','OSDPartitions1VOLUMELETTERVARIABLE','DoNotCreateExtraPartition','OSRoles','OSRoleServices','OSFeatures','ReplicaOrNewDomain','NewDomain','NewDomainDNSName','ParentDomainDNSName','ReplicaDomainDNSName','ChildName','DomainNetBiosName','ForestLevel','DomainLevel','AutoConfigDNS','ConfirmGC','CriticalReplicationOnly','ADDSUserName','ADDSUserDomain','ADDSPassword','ReplicationSourceDC','DatabasePath','ADDSLogPath','SysVolPath','SafeModeAdminPassword','SiteName','DHCPServerOptionRouter','DHCPServerOptionDNSServer','DHCPServerOptionWINSServer','DHCPServerOptionDNSDomain','DHCPServerOptionNBTNodeType','DHCPServerOptionPXEClient','DHCPScopes','DHCPScopes0SubnetMask','DHCPScopes0IP','DHCPScopes0Name','DHCPScopes0Description','DHCPScopes0StartIP','DHCPScopes0EndIP','DHCPScopes0ExcludeStartIP','DHCPScopes0ExcludeEndIP','DHCPScopes0OptionRouter','DHCPScopes0OptionDNSServer','DHCPScopes0OptionWINSServer','DHCPScopes0OptionDNSDomainName','DHCPScopes0OptionNBTNodeType','DHCPScopes0OptionLease','DHCPScopes0OptionPXEClient','DNSZones','DNSZones0Name','DNSZones0Type','DNSZones0MasterIP','DNSZones0DirectoryPartition','DNSZones0FileName','DNSZones0Scavenge','DNSZones0Update','DNSServerOptionDisableRecursion','DNSServerOptionBINDSecondaries','DNSServerOptionFailOnLoad','DNSServerOptionEnableRoundRobin','DNSServerOptionEnableNetmaskOrdering','DNSServerOptionEnableSecureCache','DNSServerOptionNameCheckFlag','SMSTSPreferredAdvertID','SMSTSUDAUsers','SMSTSAssignUsersMode','OSDStateStorePath','A8','A7','A6','A5','A4','A3','A2','A1','C9','C8','C7','C6','C5','C4','C3','C2','C1') values ('R','9999',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'YES',NULL,NULL,NULL,'corpo.stm.info',NULL,'ASDF',NULL,'ASDF','ASDF','ASDFASDF','ASDF',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE','ASDF','N',' ',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,' ',NULL,' ','10 ')
Output I get in Powershell studio:
INSERT INTO dbo.Settings ('Type','ID','UserID','UserDomain','UserPassword','UDShare','UDDir','UDProfiles','SLShare','SLShareDynamicLogging','EventShare','OSInstall','ComputerName','OSDComputerName','Home_Page','JoinDomain','JoinWorkGroup','DomainAdmin','DomainAdminDomain','DomainAdminPassword','MachineObjectOU','ScanStateArgs','LoadStateArgs','ComputerBackupLocation','BackupShare','BackupDir','UserDataLocation','DoCapture','ProductKey','OverrideProductKey','WDSServer','CaptureGroups','AdminPassword','OrgName','FullName','TimeZone','TimeZoneName','TaskSequenceID','KeyboardLocale','InputLocale','UserLocale','SystemLocale','UILanguage','Xresolution','Yresolution','BitsPerPel','Vrefresh','AreaCode','CountryCode','LongDistanceAccess','Dialing','OSDBitlockerMode','BdeInstallSuppress','BdeInstall','OSDBitLockerStartupKeyDrive','OSDBitLockerCreateRecoveryPassword','OSDBitLockerWaitForEncryption','BDEWaitForEncryption','BdeDriveLetter','BdeDriveSize','BdePin','BdeRecoveryKey','BdeKeyLocation','TpmOwnerPassword','OSDBitLockerRecoveryPassword','OSDBitLockerStartupKey','DriverGroup','ServerA','ServerB','ServerC','ResourceRoot','FinishAction','SMSTSRunCommandLineUserName','SMSTSRunCommandLineUserPassword','USMTConfigFile','WipeDisk','_SMSTSORGNAME','WsusServer','USMT3','DriverSelectionProfile','PackageSelectionProfile','WizardSelectionProfile','AutoMode','AutoUsers','ApplicationSuccessCodes','BackupFile','SkipWizard','SkipCapture','SkipAdminPassword','SkipApplications','SkipComputerBackup','SkipDomainMembership','SkipComputerName','SkipUserData','SkipPackageDisplay','SkipLocaleSelection','SkipProductKey','SkipSummary','SkipFinalSummary','SkipBDDWelcome','SkipTimeZone','SkipTaskSequence','SkipBitLocker','DestinationDisk','DestinationPartition','OSDAdapterCount','OSDAdapter0Name','OSDAdapter0MacAddress','OSDAdapter0EnableDHCP','OSDAdapter0IPAddressList','OSDAdapter0SubnetMask','OSDAdapter0Gateways','OSDAdapter0GatewayCostMetric','OSDAdapter0DNSServerList','OSDAdapter0DNSSuffix','OSDAdapter0EnableDNSRegistration','OSDAdapter0EnableFullDNSRegistration','OSDAdapter0EnableLMHOSTS','OSDAdapter0EnableWINS','OSDAdapter0TcpipNetbiosOptions','OSDAdapter0WINSServerList','OSDAdapter0EnableTCPIPFiltering','OSDAdapter0TCPFilterPortList','OSDAdapter0UDPFilterPortList','OSDAdapter0IPProtocolFilterList','OSDDiskIndex','OSDPartitions','OSDPartitions0TYPE','OSDPartitions0FILESYSTEM','OSDPartitions0BOOTABLE','OSDPartitions0QUICKFORMAT','OSDPartitions0VOLUMENAME','OSDPartitions0SIZE','OSDPartitions0SIZEUNITS','OSDPartitions0VOLUMELETTERVARIABLE','OSDPartitions1TYPE','OSDPartitions1FILESYSTEM','OSDPartitions1BOOTABLE','OSDPartitions1QUICKFORMAT','OSDPartitions1VOLUMENAME','OSDPartitions1SIZE','OSDPartitions1SIZEUNITS','OSDPartitions1VOLUMELETTERVARIABLE','DoNotCreateExtraPartition','OSRoles','OSRoleServices','OSFeatures','ReplicaOrNewDomain','NewDomain','NewDomainDNSName','ParentDomainDNSName','ReplicaDomainDNSName','ChildName','DomainNetBiosName','ForestLevel','DomainLevel','AutoConfigDNS','ConfirmGC','CriticalReplicationOnly','ADDSUserName','ADDSUserDomain','ADDSPassword','ReplicationSourceDC','DatabasePath','ADDSLogPath','SysVolPath','SafeModeAdminPassword','SiteName','DHCPServerOptionRouter','DHCPServerOptionDNSServer','DHCPServerOptionWINSServer','DHCPServerOptionDNSDomain','DHCPServerOptionNBTNodeType','DHCPServerOptionPXEClient','DHCPScopes','DHCPScopes0SubnetMask','DHCPScopes0IP','DHCPScopes0Name','DHCPScopes0Description','DHCPScopes0StartIP','DHCPScopes0EndIP','DHCPScopes0ExcludeStartIP','DHCPScopes0ExcludeEndIP','DHCPScopes0OptionRouter','DHCPScopes0OptionDNSServer','DHCPScopes0OptionWINSServer','DHCPScopes0OptionDNSDomainName','DHCPScopes0OptionNBTNodeType','DHCPScopes0OptionLease','DHCPScopes0OptionPXEClient','DNSZones','DNSZones0Name','DNSZones0Type','DNSZones0MasterIP','DNSZones0DirectoryPartition','DNSZones0FileName','DNSZones0Scavenge','DNSZones0Update','DNSServerOptionDisableRecursion','DNSServerOptionBINDSecondaries','DNSServerOptionFailOnLoad','DNSServerOptionEnableRoundRobin','DNSServerOptionEnableNetmaskOrdering','DNSServerOptionEnableSecureCache','DNSServerOptionNameCheckFlag','SMSTSPreferredAdvertID','SMSTSUDAUsers','SMSTSAssignUsersMode','OSDStateStorePath','A8','A7','A6','A5','A4','A3','A2','A1','C9','C8','C7','C6','C5','C4','C3','C2','C1') values ('R','9999','','','','','','','','','','YES','','','','ASDF','','ASDF','ASDF','ASDFASDF','ASDF','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','TRUE','ASDF','N','','','','','','','','','','','','','','10 ')
User avatar
STM001
Posts: 60
Last visit: Thu Mar 07, 2024 11:26 am

Re: Emtpy value detection

Post by STM001 »

I managed to fix it using

Code: Select all

$value = "$($sourceSettings[$i])"
        if ($value -eq "" ) {
            $queryInsertValue += "NULL"     
        }else{
            $queryInsertValue += "'$($sourceSettings[$i])'"
            
        }
Not pretty, but it's working.
This topic is 4 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked