Link to home
Start Free TrialLog in
Avatar of Mr_N
Mr_NFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.SqlClient" />
    </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="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
Avatar of GeorgeGergues
GeorgeGergues

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
Avatar of Mr_N

ASKER

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)
Avatar of Mr_N

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Mr_N
Mr_N
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.