Solved

How to change datasource connection string in SharePoint designer

Posted on 2011-02-23
6
1,286 Views
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
    <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
1
Comment
Question by:Mr_N
[X]
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
  • 3
6 Comments
 
LVL 14

Expert Comment

by:GeorgeGergues
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
0
 

Author Comment

by:Mr_N
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)
0
 

Author Comment

by:Mr_N
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?
0
 

Accepted Solution

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

Expert Comment

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

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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…
When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

628 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