PDA

View Full Version : Change SQL Connection details in web.config



swhittaker
06-17-2008, 03:32 AM
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
06-18-2008, 06:33 AM
Well i have figured it out and was to do with having null values in the username and password section.

ayub_yousuf
08-19-2008, 07:23 AM
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
08-19-2008, 05:50 PM
Here is a link (http://community.macrovision.com/showthread.php?t=180310&page=2&highlight=web.config) 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
08-19-2008, 11:31 PM
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
08-20-2008, 03:20 PM
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
08-20-2008, 11:28 PM
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
08-21-2008, 08:59 AM
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
05-15-2009, 08:14 PM
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