Powershell – Error – “Error formatting a string: Input string was not in a correct format..”

Background

Here is another Powershell error message I received this morning.

Error

Error Image


Error formatting a string: Input string was not in a correct format..
+ CategoryInfo          : InvalidOperation: (Driver={SQL Ser...ase={1};APP={2}:String) [], RuntimeException
+ FullyQualifiedErrorId : FormatError

Code

Code – Error



Set-StrictMode -Version 1.0

[string] $moduleName="";
[string] $appName="";
[string] $dbConnectionStringSyntax="";
[string] $sqlServerInstance="";
[string] $databaseName="";

$dbConnectionStringSyntax="Driver={SQL Server};Server={0};Database={1};APP={2}";

#get PowerShell Module Name
$moduleName=([io.fileinfo]$MyInvocation.MyCommand.Definition).BaseName;

$appName = $moduleName;

$sqlServerInstance ="localhost";

$databaseName ="master";

#Database Connection String
$dbConnectionString = $dbConnectionStringSyntax -f $sqlServerInstance, $databaseName, $appName;

if ( `
		    ( $dbConnectionString -eq $Null ) `
		-or ( $dbConnectionString -eq "" ) `
    )
{

	Write-Warning ("Unable to properly develop database connection string");

	Exit;	

}

Write-Output("Database Connection String - {0}" -f $dbConnectionString);

Code – Correction

Outline

  1. The issue is that our connection string contains control characters
  2. Database Connection String
    • Original :- Driver={SQL Server};Server={0};Database={1};APP={2}”
    • Revised :- Driver={{SQL Server}};Server={0};Database={1};APP={2}”
      • We escaped { by repeating it
      • Same for }, we escaped by repeating it

Code – Correction -01



Set-StrictMode -Version 1.0

[string] $moduleName="";
[string] $appName="";
[string] $dbConnectionStringSyntax="";
[string] $dbConnectionStringSyntaxEscaped="";
[string] $sqlServerInstance="";
[string] $databaseName="";

$dbConnectionStringSyntax="Driver={SQL Server};Server={0};Database={1};APP={2}";
$dbConnectionStringSyntaxEscaped="Driver={{SQL Server}};Server={0};Database={1};APP={2}";

#get PowerShell Module Name
$moduleName=([io.fileinfo]$MyInvocation.MyCommand.Definition).BaseName;

$appName = $moduleName;

$sqlServerInstance ="localhost";

$databaseName ="master";

#Database Connection String                     



$dbConnectionString = $dbConnectionStringSyntaxEscaped -f `
                            $sqlServerInstance, `
                            $databaseName, `
                            $appName;

if ( `
            ( $dbConnectionString -eq $Null ) `
        -or ( $dbConnectionString -eq "" ) `
    )
{

    Write-Warning ("Unable to properly develop database connection string");

    Exit;   

}

Write-Output("Database Connection String - {0}" -f $dbConnectionString);

Code Sharing

Gist

Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s