none
Designer Connection String Issue when migrating away from VS 2005 RRS feed

  • Question

  • We recently moved all of our connection strings to machine.config on each web server to allow one point of change when we needed to change our data server for maintenance.  In VS 2005 the connection string was not available to be selected when creating a SQL data source in the designer but if we manually entered the name the designer would pick it up and allow further configuration of the data source.  We have recently moved to VS 2008 and are now finding that this does not work.  We can not use the designer to access any connection strings that are not in the root web.config.  I've done a test installation of VS 2010 and found the same thing to be true.

    Is there any workaround to this?  We are losing a substantial amount of functionality by not being able to access these connection strings in the designer.  I am a little surprised that after many, many hours I've only found one reference to this issue and that was never answered.

    VS 2005 was the MSDN Professional version as are the VS 2008 and VS 2010 applications.  These are being run on XP Pro and are accessing SQL 2005 Enterprise.

    Thanks in advance for any guidance.

    Dave

    • Moved by Brittany Behrens Saturday, July 24, 2010 12:11 AM Believe this is also a database tools issue - problem in a VS designer. (From:Visual Studio Editor)
    • Moved by Edwer Fang Monday, July 26, 2010 2:39 AM (From:Visual Studio Database Development Tools (Formerly "Database Edition Forum"))
    Friday, July 23, 2010 2:40 PM

Answers

  • Hi Dave,

    As far as I know, the SqlDataSourceDesginer object (the designer of the SqlDataSource control) uses the object which implements the IDataEnvironment interface to access the connection list of the application at design time and display them in the Combobox in the wizard. And it only gets the connections declared in the web.config file of the application, not from the machine.config file which is shared by all applications on the server. But you can get the connection string stored in machine.config at run time like this:


     Configuration machineConfig = ConfigurationManager.OpenMachineConfiguration();  

     foreach (ConnectionStringSettings connectionstring in machineConfig.ConnectionStrings.ConnectionStrings)  

                 {    

                                        // u can have multiple connection strings      

                                        // read the connection string here             

                 } 

    In addition, I think you are using GridView control and ASP.NET, I suggest you open a new thread with this question in ASP.NET forums(http://forums.asp.net/) because there are more ASP.Net specialists and you will get better support.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, July 27, 2010 6:14 AM
    Moderator

All replies

  • Hi Dave,

    Could you please explain a little about your problem? What kind of designer are you using to add data source in VS2005 and VS2008 ? In vs2008, we can also add new data sources, make configuration of the data sources and save the connectionstrings to the configuration file.

    So could you please clarify this ? Thank you.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, July 26, 2010 8:47 AM
    Moderator
  • Alex,

    In many cases we use the data source and gridview in its very basic form.  A gridview is pulled onto the form.  Next a data source is created using the <new data source> option under the gridview's Choose Data Source. In the past we stored the connection strings in the root web config.  These connection strings would be available to select while using the data source wizard.  We would than build the query using the wizard. (SQL 2005)  Recently, do to the number of web sites, it became very beneficial to store these connection strings in the machine.config.  While using VS 2005 we found that we would need to copy & paste an existing data source that pointed to a connection string in the machine.config.  We could than use the data source wizard to build the query and it would see the connection string in the machine.config.  Very shortly after this we migrated to VS 2008.  We found that no matter what we would do we could not use the data source wizard to build our queries.  The wizard would fail to connect to the connection string in the machine.config.  I've also installed VS 2010 and found the same results.  It seems our only workaround will be to have a local web.config that contains all connection strings and another version on our development and live servers that does not contain any connection strings.  This opens up a few obvious potential problems.

    We do a considerable amount of "hand built" data sources as well but many times we will use the wizard to get us started and than make modifications from that point.

    Thanks,

    Dave

    Monday, July 26, 2010 12:10 PM
  • Hi Dave,

    As far as I know, the SqlDataSourceDesginer object (the designer of the SqlDataSource control) uses the object which implements the IDataEnvironment interface to access the connection list of the application at design time and display them in the Combobox in the wizard. And it only gets the connections declared in the web.config file of the application, not from the machine.config file which is shared by all applications on the server. But you can get the connection string stored in machine.config at run time like this:


     Configuration machineConfig = ConfigurationManager.OpenMachineConfiguration();  

     foreach (ConnectionStringSettings connectionstring in machineConfig.ConnectionStrings.ConnectionStrings)  

                 {    

                                        // u can have multiple connection strings      

                                        // read the connection string here             

                 } 

    In addition, I think you are using GridView control and ASP.NET, I suggest you open a new thread with this question in ASP.NET forums(http://forums.asp.net/) because there are more ASP.Net specialists and you will get better support.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, July 27, 2010 6:14 AM
    Moderator
  • Hi Dave,

    I'm writing to follow up the post. Does the above suggestion work ?

    Please feel free to let me know if you need any help.

    Best regards,
    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, July 29, 2010 12:25 PM
    Moderator
  • Thanks Alex,

    I'm sorry. For some reason I did not get notification of yesterday's post.  I am very familiar with getting information from the configuration manager.  I just need something that will help when using the wizard.  I found it very odd that the wizard would accept a manual entry pointed at machine.config in VS 2005 but not at all in VS 2008 or VS 2010.  I'm afraid my time has run out on trying to resolve this.  We will just need to find another tool.  Thanks for your replies.

    Dave

    Thursday, July 29, 2010 1:02 PM