How to change datasource connection string in SharePoint designer
Hi All,
In my Sharepoint (MOSS-2007) ASPX page I have a DataFormWebPart with SPSqlDataSource. and it uses a connection string like "Data Source=Server001;Initial Catalog=DB1;User ID=xxxx;Password=xxxx;"
When moving from a PROD environment to a DEV environment, I wish to change the connectionString to make use of a different database. To do this, I have defined a connection string in the web.config
<connectionStrings>
<add name="MyDB" connectionString="Data Source=Server001;Initial Catalog=DB1;User ID=xxxx;Password=xxxx;" providerName="System.Data.
</connectionStrings>
I am modifying using Sharepoint Designer, I am trying to modify connectionString="<% connectionString:MyDB %>" to get connetion string from web.config.
After save and close page and reopen it again it is reverting back to old value like(connectionString="Dat
I have noticed that this is a particular problem for DataSources being used for DataFormWebPart. I have tried changing from SPSqlDataSource to SqlDataSource, but I get the same issue (close apsx in designer; re-open it and the code has changed back to the old long-hand version - i.e. it has translated the connectionString from the web.config)
Just to make it really confusing I have examples of SqlDataSource being used outside of a DataFormWebPart (for GridView and FormView) and these ones work precisely as expected.
(close apsx page in designer; re-open it and the code is still using connectionString="<% connectionString:myDB %>" ...which is what I want)
At the moment this issue seems to be happening to DataSources within the DataFormWebPart.
I really need to solve this, as I need to apply a change of "Data Source" to many examples of [SPSqlDataSource] and [SqlDataSource] on over 70 ASPX pages.
Thanks for looking - please help
In my Sharepoint (MOSS-2007) ASPX page I have a DataFormWebPart with SPSqlDataSource. and it uses a connection string like "Data Source=Server001;Initial Catalog=DB1;User ID=xxxx;Password=xxxx;"
When moving from a PROD environment to a DEV environment, I wish to change the connectionString to make use of a different database. To do this, I have defined a connection string in the web.config
<connectionStrings>
<add name="MyDB" connectionString="Data Source=Server001;Initial Catalog=DB1;User ID=xxxx;Password=xxxx;" providerName="System.Data.
</connectionStrings>
I am modifying using Sharepoint Designer, I am trying to modify connectionString="<% connectionString:MyDB %>" to get connetion string from web.config.
After save and close page and reopen it again it is reverting back to old value like(connectionString="Dat
I have noticed that this is a particular problem for DataSources being used for DataFormWebPart. I have tried changing from SPSqlDataSource to SqlDataSource, but I get the same issue (close apsx in designer; re-open it and the code has changed back to the old long-hand version - i.e. it has translated the connectionString from the web.config)
Just to make it really confusing I have examples of SqlDataSource being used outside of a DataFormWebPart (for GridView and FormView) and these ones work precisely as expected.
(close apsx page in designer; re-open it and the code is still using connectionString="<% connectionString:myDB %>" ...which is what I want)
At the moment this issue seems to be happening to DataSources within the DataFormWebPart.
I really need to solve this, as I need to apply a change of "Data Source" to many examples of [SPSqlDataSource] and [SqlDataSource] on over 70 ASPX pages.
Thanks for looking - please help
Who is Participating?
the data connection reside in a dataconnection Library.
In your DEV you have to create a Dataconnetion libray if you don't have one - >
On Designer go to Dataview - > manage connections.
Copy from Prod - > into DEV
Modify DEV connection in the libary.
Modify You pages
A trick that I use all the time , I have 2 connections DEV and PROD on all the time , and simple use anyone in PROD or DEV as needed.
so you don't mix the name of PROD and DEV or vice versa.
Best of Luck
In your DEV you have to create a Dataconnetion libray if you don't have one - >
On Designer go to Dataview - > manage connections.
Copy from Prod - > into DEV
Modify DEV connection in the libary.
Modify You pages
A trick that I use all the time , I have 2 connections DEV and PROD on all the time , and simple use anyone in PROD or DEV as needed.
so you don't mix the name of PROD and DEV or vice versa.
Best of Luck
Under [Data View] there is an option to [Manage Data Sources...] so I guess this is what you mean
It only shows 72 connections, yet in my aspx pages I have over 600 DataFormWebParts that use SPSqlDataSource or SqlDataSource.
I don't see any relationship between these as most were manually keyed anyhow (i.e. not via wizard using a Library entry).
I have also tested this by deleting a [Database Connection] in the [Data Source Library] to see if this impacts a DataFormWebPart that was generated using it - no impact.
Therefore, I have determined there is no relationship maintained (from the Library entry) after the point of DataFormWebPart creation.
Instead, when doing what I described above, "<% connectionString:myDB %>" does get interpreted.
When the page is re-loaded it has the value of MyDB from the web.config
However, the code is then using the long-hand version from then on (i.e. "Data Source=Server001;Initial Catalog=DB1;User ID=xxxx;Password=xxxx;") which means it is no longer being dynamically managed from the web.config
This is what I am trying to solve
I want the code to always use "<% connectionString:myDB %>" so I can manage what database is being used via a single entry in the web.config (rather than update 600+ entries on 80 aspx pages)
It only shows 72 connections, yet in my aspx pages I have over 600 DataFormWebParts that use SPSqlDataSource or SqlDataSource.
I don't see any relationship between these as most were manually keyed anyhow (i.e. not via wizard using a Library entry).
I have also tested this by deleting a [Database Connection] in the [Data Source Library] to see if this impacts a DataFormWebPart that was generated using it - no impact.
Therefore, I have determined there is no relationship maintained (from the Library entry) after the point of DataFormWebPart creation.
Instead, when doing what I described above, "<% connectionString:myDB %>" does get interpreted.
When the page is re-loaded it has the value of MyDB from the web.config
However, the code is then using the long-hand version from then on (i.e. "Data Source=Server001;Initial Catalog=DB1;User ID=xxxx;Password=xxxx;") which means it is no longer being dynamically managed from the web.config
This is what I am trying to solve
I want the code to always use "<% connectionString:myDB %>" so I can manage what database is being used via a single entry in the web.config (rather than update 600+ entries on 80 aspx pages)
A further update - which is really starting to confuse me
Having set the "<% connectionString:myDB %>" in the aspx page, it appears it is working as expected in the first place. However, I am confused by the fact that it is never shown in Designer aftr the page is re-loaded (as the interpreted value is shown).
To test this, I have been modifying he value in the web.config and can confirm that the DataFormWebPart points to the DataSource specified in the web.config each time I change it.
...pretty impressive as this is what I want
However, this does mean I am still confused as when I use Designer to look at the aspx page, it no longer has any mention of "<% connectionString:myDB %>" (just the "latest version of" the value interpreted from the web.config)
From a support PoV, how can I ever be sure that the "<% connectionString:myDB %>" is always being used?
Having set the "<% connectionString:myDB %>" in the aspx page, it appears it is working as expected in the first place. However, I am confused by the fact that it is never shown in Designer aftr the page is re-loaded (as the interpreted value is shown).
To test this, I have been modifying he value in the web.config and can confirm that the DataFormWebPart points to the DataSource specified in the web.config each time I change it.
...pretty impressive as this is what I want
However, this does mean I am still confused as when I use Designer to look at the aspx page, it no longer has any mention of "<% connectionString:myDB %>" (just the "latest version of" the value interpreted from the web.config)
From a support PoV, how can I ever be sure that the "<% connectionString:myDB %>" is always being used?
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
I have done 2 things to confirm this:
1. I change the connectionString in the web.config to use a different DB, then reload apsx page and can see that different data is loaded (and this changes back when I change the web.config again)
2. I load the aspx page in NOTEPAD (this shows "<% connectionString:myDB %>"), then load the same page in DESIGNER (this shows "Data Source=Server001;Initial Catalog=DB1;User ID=xxxx;Password=xxxx;") confusing, but at least it is getting info from the web.config.
So, this leave one final question to be answered...
Why does DESIGNER show the interpreted value instead of "<% connectionString:myDB %>"
...bear in mind this is only an issue when the SqlDataSource is part of a DataFormWebPart, as when SqlDataSource is defined outside of a DataFormWebPart (i.e. for FormView or gridView use) then DESIGNER will still show "<% connectionString:myDB %>"
Please help as this is still very confusing for me . . !