Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

ID: 34964586
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

ID: 34968017
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

ID: 34968118
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
ID: 34968468
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

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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