CODEWITHSUNDEEP
powershellconnection-stringsqlconnection
Robert
Robert

Reputation: 1

Powershell and SQL Connection String Error

function GenericSqlQuery($ConnectionString, $SQLQuery) {
    Write-Host $ConnectionString
    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    while ($Reader.Read()) {
         $Reader.GetValue(0)
    }
    $Connection.Close()
}

See the above, the $Connectionstring is replaced ok when the Write-Host is executed but when the $Connection.ConnectionString = $ConnectionString is executed it does not get replaced and the script errors with the following

Exception setting "ConnectionString": "Format of the initialization string does not conform to 
specification starting at index 64."
At C:\Users\Blah\Desktop\Debugger\ABS9_DB_Update.ps1:7 char:5
+     **$Connection.ConnectionString = "$ConnectionString"**
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

Anyone know why?

Tried all sorts of combinations but I cannot get anything to work. The following had the same issue

$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"

I even tried

$Connection.ConnectionString = "server='"+$Server+"';database='"+$Database+"';trusted_connection=true;" 

$Connection.ConnectionString="server='"+"$Server"+"';database='"+"$Database"+"';trusted_connection=true;"

But none of the variables get replaced.

Upvotes: 0

Views: 145

Answers (1)

Robert
Robert

Reputation: 1

I figured it out, the values were coming from text boxes on the GUI form and I simply needed to reference with the .Text property like $Connection.ConnectionString = "server="+$SQLServer.text+";database="+$SQLDatabase.text

Upvotes: 0

Related Questions