Change SQL Connection details in web.config

[swhittaker]

I am new to Installshield and am trying to use the built in sql dialog to set my sql connection details in my webservice web.config. I have read a number of posts and have manged to get the "XML File Changes" working fine setting the datasource etc but it seems that there is a limit to the length of string as i can get it to work fine upto the User ID but as soon as i add on the Password the string becomes blank.

Works:
Data Source=[IS_SQLSERVER_SERVER];Initial Catalog=[IS_SQLSERVER_DATABASE];Integrated Security=[IS_SQLSERVER_AUTHENTICATION];User ID=[IS_SQLSERVER_USERNAME];

Returns Nothing
Data Source=[IS_SQLSERVER_SERVER];Initial Catalog=[IS_SQLSERVER_DATABASE];Integrated Security=[IS_SQLSERVER_AUTHENTICATION];User ID=[IS_SQLSERVER_USERNAME];Password=[IS_SQLSERVER_PASSWORD]

I am using 2009 if that helps.

Thanks
Shaun

[swhittaker]

Well i have figured it out and was to do with having null values in the username and password section.

[ayub_yousuf]

Hi Shaun
I want the same functionality for my application. Can u plz throw a code at my mail address ayub_yousuf@hotmail.com. Thanking you in advance:)

[TheTraveler]

Here is a link that might interest you.

The code allows you to load a XML document and change settings in a web.config file.

Hope this helps.

[ayub_yousuf]

Hi Traveler
I've been looking for the script which will take the parameters from SQL Connection Dialog and save it to a config file, Anyhow ur sample code will definitely help me in saving values to the config file. Thnks a lot.
🙂

-Ayub

[TheTraveler]

Unfortunately, there isn't a pre-made dialog that asks for that information. I created a custom dialog that asked for the database server, database instance name, user name, password, and the database name to create the database connection string. So with the connection string made, I used the IS code to modify the web.config file. If you need help creating a custom dialog, let me know.

[ayub_yousuf]

Thnks Traveler,
I've checked the link u've provided earlier for modifying a web.config file, i've used the sample code but it isnt doing anything, how can i use that code. And definitely i'll welcome any help from ur side regarding custom dialogs. It will be more helpful if u'll send me a sample project on my email address ayub_yousuf@hotmail.com

--Ayub

[TheTraveler]

Here is a more practical version of the example straight from one of our installations.

///////////////////////////////////////////////////////////////////////////////
//                                                                           //
// Global Constants                                                          //
//                                                                           //
///////////////////////////////////////////////////////////////////////////////
#define ERROR_SUCCESS 0
#define ERROR_XML_FILE_CREATION "Setup could not find the web.config file %s"
#define ERROR "Error"
#define ERROR_XML_FILE_STRUCTURE "The web.config file structure is corrupted.%s"
#define ERROR_XML_FILE_LOAD "The web.config file was not loaded.%s"

///////////////////////////////////////////////////////////////////////////////
//                                                                           //
// Global Variables                                                          //
//                                                                           //
///////////////////////////////////////////////////////////////////////////////
STRING  m_strDBDataSource;
STRING  m_strDBUserName; 
STRING  m_strDBPassword;
STRING  m_strDBName;

///////////////////////////////////////////////////////////////////////////////
//                                                                           //
// Prototypes                                                                //
//                                                                           //
///////////////////////////////////////////////////////////////////////////////
prototype ModifyWebConfig(BYVAL STRING, BYREF STRING);

///////////////////////////////////////////////////////////////////////////////
//                                                                           //
// OnBegin                                                                   //
//                                                                           //
///////////////////////////////////////////////////////////////////////////////
function OnBegin()
    STRING  strFileName;
    STRING  strError;
begin
    m_strDBDataSource = "SQLServer/InstanceName";
    m_strDBUserName   = "MyUserName"; 
    m_strDBPassword   = "MyPassword";
    m_strDBName       = "MyDatabaseName";
    
    strFileName       = "C:\\Temp\\web.config";
    
    ModifyWebConfig(strFileName, strError);
    if strError != "" then
        MessageBox(strError, SEVERE);
    endif;
    abort;
end;

///////////////////////////////////////////////////////////////////////////////
//                                                                           //
//                                                                           //
//                                                                           //
///////////////////////////////////////////////////////////////////////////////
function ModifyWebConfig(strFileName, strError)
    OBJECT oDoc, oNode, oNodeList;
    NUMBER i, nSize;                  
    STRING  strNamedItem;
    STRING  strValue;
begin   
    ///////////////////////////////////////////////////////////////////////////
    // Check to see if the file exists...                                    //
    ///////////////////////////////////////////////////////////////////////////
    if Is(FILE_EXISTS, strFileName) = TRUE then
        MessageBox("XML file exists",0);
    else
        MessageBox("XML file is missing",0);
    endif;
          
    ///////////////////////////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////
    nSize = 300;  
    i = 0;
    strValue =           "Data Source="     + m_strDBDataSource;
    strValue = strValue +";User Id="        + m_strDBUserName; 
    strValue = strValue +";Password="       + m_strDBPassword;
    strValue = strValue +";Initial Catalog="+ m_strDBName; 
    strValue = strValue +";Persist Security Info=True";
    strValue = strValue +";Connection Timeout=30";
                           
    ///////////////////////////////////////////////////////////////////////////
    // get values from public properties
    ///////////////////////////////////////////////////////////////////////////
    set oDoc = CreateObject("Msxml2.DOMDocument.4.0");  
    if (IsObject(oDoc) = FALSE) then
        MessageBox(ERROR_XML_FILE_CREATION, 0);
        return -1;     
    endif;  
    oDoc.async = FALSE;  
    oDoc.setProperty("SelectionLanguage", "XPath");

    ///////////////////////////////////////////////////////////////////////////
    // if success, traverse file and substitute value                        //
    ///////////////////////////////////////////////////////////////////////////
    if oDoc.load(strFileName) then
        ///////////////////////////////////////////////////////////////////////
        // get list of matching nodes                                        //
        ///////////////////////////////////////////////////////////////////////
        set oNodeList = oDoc.getElementsByTagName("*");
        if (oNodeList.length > 0) then   
            for i = 0 to (oNodeList.length - 1);            
                set oNode = oNodeList.nextNode;
                try
                    strNamedItem = oNode.attributes.getNamedItem("key").value;
                catch
                    strNamedItem = "";
                endcatch;
                
                try
                    if strNamedItem = "SQLConnection" then
                        oNode.attributes.getNamedItem("value").value = strValue;
                    endif;
                catch
                endcatch;
            endfor; 
        else
            MessageBox(ERROR_XML_FILE_STRUCTURE,SEVERE);
            return -1;
        endif;   
    else
        MessageBox(ERROR_XML_FILE_LOAD,SEVERE);                         
        return -1;
    endif;

    oDoc.save(strFileName);
    set oDoc = NOTHING;
end;

The way this code works is simple. It looks for the key SQLConnection. Once it finds it, it replaces the value for this key with the one we created in code. If your Key for your Database Connection string is different, then you need to modify the string it looks for in order for this function to work. Also keep in mind, Install Shield (IS) script language is not case sensitive when comparing strings, ( "MYSTRING" = "mystring") results TRUE.

Let me know if this works for you.

[cspanellis]

For some reason my .load call always returns false. If I run similar code in vbscript it works fine, but not installscript.

Any thoughts?

Thanks