Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
How to change datasource connection string in SharePoint designer
Posted on 2011-02-23
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
[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
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?
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 . . !
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 . . !
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Note: There are two main ways to deploy InfoPath forms: Server-side and directly through the SharePoint site. Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data.
But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month11 days, 7 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.