How to change datasource connection string in SharePoint designer

Posted on 2011-02-23
1 Endorsement
Last Modified: 2012-08-14
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
      <add name="MyDB" connectionString="Data Source=Server001;Initial Catalog=DB1;User ID=xxxx;Password=xxxx;" providerName="System.Data.SqlClient" />

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="Data source=; Initialcatalog=*; user name=; password="). I don't know why !

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
Question by:Mr_N
  • 3
LVL 14

Expert Comment

Comment Utility
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

Author Comment

Comment Utility
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)

Author Comment

Comment Utility
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?

Accepted Solution

Mr_N earned 0 total points
Comment Utility
I can confirm that the  "<% connectionString:myDB %>"  is working - even though it is not shown to me in Designer (very confusing).

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 . . !
LVL 17

Expert Comment

Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When installing SharePoint 2010 RTM I came across a strange error, I was getting timeouts during the installation. I searched the web and found the best solution to be found here (…
For SharePoint sites, particularly public-facing ones, there are times when adding JavaScript, Meta Tags, CSS Styles or other content to the page <head> section is more practical than modifying master pages.  For instance, you could add the jQuery l…
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now