Issue with mail template script

Hello experts,

I am calling a script in my approval mail template but getting some weird error while script being called.

Am I missing any connection string here?

Error : 

Exception has been thrown by the target of an invocation.
at Designer.ScriptEditor.ScriptTestEditor.ExecuteScript(ScriptItem sItem)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
Format of the initialization string does not conform to specification starting at index 0.

Code : 

<BaseObjectTypeAttribute("PwOHelperPwO")> _
Public Function CCC_GetProductIdentfromRequest( ByVal dbPwOHelperPwO As ISingleDbObject) As String
Dim scriptErrorBase As String = "Script Error [CCC_GetProductIdentfromRequest]"
Dim returnValue As iDatareader
Dim results As New StringBuilder
Dim connData As ConnectData = DbApp.Instance.Connect(VID_GetValueOfDialogdatabases("ConnectionString"))
Dim cSQL As SqlExecutor = Connection.CreateSqlExecutor(connData.PublicKey)
Dim pwoUID As String = dbPwoHelperPwo.GetValue("UID_PersonWantsOrg")

Dim myQuery As String = String.Format("select " &
"apg.Ident_AccProductGroup " &
"from PersonWantsOrg pwo" &
" inner join QERAssign qas" &
" On pwo.ObjectKeyOrdered = qas.XObjectKey" &
" inner join AccProduct apd" &
" On qas.UID_AccProduct = apd.UID_AccProduct" &
" inner join AccProductGroup apg" &
" On apd.UID_AccProductGroup = apg.UID_AccProductGroup " &
"where pwo.UID_PersonWantsOrg = '{0}'",pwoUID)
Try
returnValue = cSQL.SqlExecute(String.Format(myQuery))

While returnValue.Read()
results.Append(returnValue("Ident_AccProductGroup").ToString)
End While
Catch ex As Exception
Throw New Exception(String.Format("{0} {1} {2}", scriptErrorBase, myQuery, ex))
End Try

Return results.ToString

End Function

<BaseObjectTypeAttribute("RPSSubscription")> _
Public Function CCC_GetProductIdentfromRPSSub(ByVal dbRPSSubscription As ISingleDbObject) As String
Dim scriptErrorBase As String = "Script Error [CCC_GetProductIdentfromRPSSub]"
Dim repSubUID As String = dbRPSSubscription.GetValue("UID_RPSSubscription")

Dim targetApp As String = String.Empty

Dim returnValue As IDataReader
Dim results As New StringBuilder
Dim connData As ConnectData = DbApp.Instance.Connect(VID_GetValueOfDialogdatabases("ConnectionString"))
Dim cSQL As SqlExecutor = Connection.CreateSqlExecutor(connData.PublicKey)

Dim myQuery As String = String.Format("SELECT dp.ParameterValue " &
"from RPSSubscription rps " &
"inner join RPSReport rpr " &
" On rps.uid_rpsreport = rpr.uid_rpsreport " &
"inner join DialogParameterSet dps " &
" On dps.objectKeyUsedBy = rps.XObjectKey " &
"inner join DialogParameter dp " &
" On dp.UID_DialogParameterSet = dps.UID_DialogParameterSet " &
"Where dp.ParameterName = 'ApplicationName' And rps.UID_RPSSubscription = '{0}' " &
"Or dp.ParameterName = 'UIDCSMRoot' And rps.UID_RPSSubscription = '{0}'", repSubUID)

Try
returnValue = cSQL.SqlExecute(myQuery)

If Not returnValue Is Nothing Then
scriptErrorBase = scriptErrorBase + " returnValue has value"
While returnValue.Read()
scriptErrorBase = scriptErrorBase + " read returnValue"

Dim paramValue As String = String.Empty
'A simple report does not require an application name so this value may be empty if it has a value
' it is the CSMRoot - see next query.
If Not returnValue("ParameterValue") Is Nothing Then
paramValue = returnValue("ParameterValue").ToString()
Else
scriptErrorBase = scriptErrorBase + " returnValue 'ParameterValue' is Nothing"
End If

scriptErrorBase = scriptErrorBase + "assigned paramValue from returnValue"

If Not String.IsNullOrEmpty(paramValue) Then
results.Append(paramValue)
End If

End While
End If

Catch ex As Exception
Throw New Exception(String.Format("{0} {1} {2}", scriptErrorBase, myQuery, ex))
End Try

If Not results Is Nothing Then
targetApp = results.ToString()
End If

If Not String.IsNullOrEmpty(targetApp) Then
'simple reports have a parameterName refering to the CSMRoot which is related to the application name.
If targetApp.Contains("-") Then
myQuery = String.Format("SELECT Ident_CSMRoot FROM CSMRoot WHERE UID_CSMRoot = '{0}'", targetApp)
'clear previous results
targetApp = String.Empty
results.Clear()

Try
returnValue = cSQL.SqlExecute(String.Format(myQuery))

While returnValue.Read()
results.Append(returnValue("Ident_CSMRoot").ToString)
End While

Catch ex As Exception
Throw New Exception(String.Format("{0} {1} {2}", scriptErrorBase, myQuery, ex))
End Try

targetApp = results.ToString()

End If

If Not String.IsNullOrEmpty(targetApp) Then
targetApp = String.Format("Application: {0}", targetApp)
End If

End If

Return targetApp

End Function

  • I did decrypt my connection string but still script is failing  updated code is as below 

    Public Function CCC_GetProductIdentfromRequest( ByVal dbPwOHelperPwO As ISingleDbObject) As String
        Dim scriptErrorBase As String = "Script Error [CCC_GetProductIdentfromRequest]"
        Dim returnValue As iDatareader
        Dim results As New StringBuilder
            
        Dim lineProvider As New DbLineProvider()
            lineProvider.ProviderName = "System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=**************"
            'TEST DEBUG!
            'lineProvider.ConnectionString = "Data Source=""*******.*****.***\DSPAC29, 40039"";Initial Catalog=D1IM;Integrated Security=True;Pooling=False"
            'MANUAL EDIT: Neil Stevenson - Read from main database, so use current connection string
            lineProvider.ConnectionString = NES_Utils_Decrypt(VID_GetValueOfDialogdatabases("ConnectionString"))
            
        Dim connData As ConnectData = DbApp.Instance.Connect(lineProvider.ConnectionString)
        Dim cSQL As SqlExecutor = Connection.CreateSqlExecutor(connData.PublicKey)
        Dim pwoUID As String = dbPwoHelperPwo.GetValue("UID_PersonWantsOrg")
        
        Dim myQuery As String = String.Format("select " &
                                "apg.Ident_AccProductGroup " &
                                "from PersonWantsOrg pwo" &
                                " inner join QERAssign qas" &
                                "  On pwo.ObjectKeyOrdered = qas.XObjectKey" &
                                " inner join AccProduct apd" &
                                "  On qas.UID_AccProduct = apd.UID_AccProduct" &
                                " inner join AccProductGroup apg" &
                                "  On apd.UID_AccProductGroup = apg.UID_AccProductGroup " &
                                "where pwo.UID_PersonWantsOrg = '{0}'",pwoUID)
        Try
            returnValue = cSQL.SqlExecute(String.Format(myQuery))
                
            While returnValue.Read()
                  results.Append(returnValue("Ident_AccProductGroup").ToString)            
            End While
        Catch ex As Exception
            Throw New Exception(String.Format("{0} {1} {2}", scriptErrorBase, myQuery, ex))
        End Try
        
        Return results.ToString
        
    End Function
    
    <BaseObjectTypeAttribute("RPSSubscription")> _
    Public Function CCC_GetProductIdentfromRPSSub(ByVal dbRPSSubscription As ISingleDbObject) As String
        Dim scriptErrorBase As String = "Script Error [CCC_GetProductIdentfromRPSSub]"
        Dim repSubUID As String = dbRPSSubscription.GetValue("UID_RPSSubscription")
    
        Dim targetApp As String = String.Empty
    
        Dim returnValue As IDataReader
        Dim results As New StringBuilder
        
        Dim lineProvider As New DbLineProvider()
            lineProvider.ProviderName = "System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=****************"
            'TEST DEBUG!
            'lineProvider.ConnectionString = "Data Source=""*********.*******.****\DSPAC29, 40039"";Initial Catalog=D1IM;Integrated Security=True;Pooling=False"
            'MANUAL EDIT: Neil Stevenson - Read from main database, so use current connection string
            lineProvider.ConnectionString = NES_Utils_Decrypt(VID_GetValueOfDialogdatabases("ConnectionString"))
            
        Dim connData As ConnectData = DbApp.Instance.Connect(lineProvider.ConnectionString)
        
        Dim cSQL As SqlExecutor = Connection.CreateSqlExecutor(connData.PublicKey)
    
        Dim myQuery As String = String.Format("SELECT dp.ParameterValue " &
                    "from RPSSubscription rps " &
                        "inner join RPSReport rpr " &
                        "   On rps.uid_rpsreport = rpr.uid_rpsreport " &
                        "inner join DialogParameterSet dps " &
                        "   On dps.objectKeyUsedBy = rps.XObjectKey " &
                        "inner join DialogParameter dp  " &
                        "   On dp.UID_DialogParameterSet = dps.UID_DialogParameterSet " &
                    "Where dp.ParameterName = 'ApplicationName' And rps.UID_RPSSubscription = '{0}' " &
                        "Or dp.ParameterName = 'UIDCSMRoot' And rps.UID_RPSSubscription = '{0}'", repSubUID)
    
        Try
            returnValue = cSQL.SqlExecute(myQuery)
    
            If Not returnValue Is Nothing Then
                scriptErrorBase = scriptErrorBase + " returnValue has value"
                While returnValue.Read()
                    scriptErrorBase = scriptErrorBase + " read returnValue"
                    
                    Dim paramValue As String = String.Empty
                    'A simple report does not require an application name so this value may be empty if it has a value
                    ' it is the CSMRoot - see next query.
                    If Not returnValue("ParameterValue") Is Nothing Then
                        paramValue = returnValue("ParameterValue").ToString()
                    Else
                        scriptErrorBase = scriptErrorBase + " returnValue 'ParameterValue' is Nothing"
                    End If
                    
                    scriptErrorBase = scriptErrorBase + "assigned paramValue from returnValue"
                    
                    If Not String.IsNullOrEmpty(paramValue) Then
                        results.Append(paramValue)
                    End If
    
                End While
            End If
    
        Catch ex As Exception
            Throw New Exception(String.Format("{0} {1} {2}", scriptErrorBase, myQuery, ex))
        End Try
    
        If Not results Is Nothing Then
            targetApp = results.ToString()
        End If
    
        If Not String.IsNullOrEmpty(targetApp) Then
            'simple reports have a parameterName refering to the CSMRoot which is related to the application name.
            If targetApp.Contains("-") Then
                myQuery = String.Format("SELECT Ident_CSMRoot FROM CSMRoot WHERE UID_CSMRoot = '{0}'", targetApp)
                'clear previous results
                targetApp = String.Empty
                results.Clear()
    
                Try
                    returnValue = cSQL.SqlExecute(String.Format(myQuery))
    
                    While returnValue.Read()
                        results.Append(returnValue("Ident_CSMRoot").ToString)
                    End While
    
                Catch ex As Exception
                    Throw New Exception(String.Format("{0} {1} {2}", scriptErrorBase, myQuery, ex))
                End Try
    
                targetApp = results.ToString()
    
            End If
    
            If Not String.IsNullOrEmpty(targetApp) Then
                targetApp = String.Format("Application: {0}", targetApp)
            End If
    
        End If
    
        Return targetApp
    
    End Function``` can you help me?