locked
changing the connectionstring at runtime using an Open dialog box? RRS feed

  • Question

  • Hello,

     

    I’m using Visual Studio 2008 Professional and I’m writing up a program using Access 2003. Using ADO is a bit new to me and I’m perplexed with a problem. One of the requirements for this project is that I need to do use an Open dialog box in case the database cannot be found which I could do with a "Try" and "Catch" statements. This was required if the database is moved elsewhere on the network and it the path needs to be changed.

     

    I’ve made up the forms by “Add New Data Source …” and that worked perfectly, but then how and where could I put in the code for an open dialog box so I don’t disturb the Dataset.Designer.cs file?

    Steve
    Wednesday, July 23, 2008 10:59 AM

Answers

  • Well, I came close on what you said, but I had to do some more research on the subject. What's missing was a couple of things. I'm only writing this up because someone else in the future would probably have the same problem as I have.

     

    If you had knowledge that this was an age old issue when it comes to designing the database in Visual Studio and the connection string, you could have warned me about it.

     

    First of all, you're incorrect on the connection string in the "settings.settings" file. The scope of connection string is set to "Application" and that cannot be re-written once it's been set (or reset) and compiled. I thought I would experiment and change the settings within the xml file of "app.config" but it didn't work.

     

    The workaround is creating another user setting and then set the "DataDirectory" macro by using:

     

    AppDomain.CurrentDomain.SetData("DataDirectory", newpath)

     

    where newpath is a string pointing to the correct directory ONLY where the database is stored, NOT THE FULL PHYSICAL PATH!

     

    More info could be found here: Smart Client Data : Working with local databases

     

    or http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx if you can't access it that way on the link.

     

    I had to create a new class and use it in the Form_Load handler event in order to correct this problem, and at the same time, store the path in the user.config file once the path has been corrected so you don't repeatedly ask the user to correct the pathway again when the application is executed at multiple times.

     

    I hope that helps anyone else that's having the same problem and saves them the aggravation!

     

    Steve

     

    Thursday, July 31, 2008 10:30 AM

All replies

  • Hi!

    Make a new cs file. There is no problem in doing this. Then read the new connection string and store it in the app.config file. If the database was moved, probably it will stay at the new location for a while, so you don't need to verify the old location and bug the user to enter the new location every time he uses the application.

     

    Regards, 

    Thursday, July 24, 2008 8:22 AM
  • Hi naicul,

     

    I would think so as well, however, it is not the case. What happens is that the program crashes with a system.io.filenotfoundexception hence why I would need to use a try and catch statements.

     

    I know what you mean by the CS file. I've seen it created after I use the "Add New Data Source …” to create the connections to the database and DataSet.cs file is added to the solutions manager along with the DataSet.Designer.cs file. It's basically doing the similiar framework for building a form1.cs. But what is perplexing me is HOW to use the Dataset.cs file and keeping the integrity DataSet.Designer.cs since Visual Studio 2008 did the work for me. Another words, I don't want to add a try and catch statement in DataSet.Designer.cs file and I rather like to place it elsewhere in the project, like maybe in Form1_OnLoad() function?

     

    I have a feeling I would need to do either a virtual or an override function to use and open it up, and most likely I would have to use article 310083 (http://support.microsoft.com/kb/310083) that uses MSDASC.DataLinks and ADODB._Connection so that way it would use an open dialog box but not as a button event. From there, I could find another way of storing the connection for a later date, say like saving it into a crypted file.

     

    I'm hoping that someone has come across this kind of problem and may have a solution worked out?

     

    Steve

     

    Thursday, July 24, 2008 2:02 PM
  • Steve,

     

    What does your Dataset.cs file contain?

    Thursday, July 24, 2008 2:11 PM
  • At the moment, nothing.

     

    What IS filled with code is DataSet.Designer.cs file which was created by VS2008.

     

    Steve

    Thursday, July 24, 2008 2:33 PM
  •  deafie wrote:
    At the moment, nothing.

     

    What IS filled with code is DataSet.Designer.cs file which was created by VS2008.

     

    Steve

    Oh... And why do you need a Try/catch in there?!
    Thursday, July 24, 2008 2:35 PM
  • Use the DataSet.cs file to get the data from the database programmaticaly into the dataset like this: http://asp.dotnetheaven.com/howto/doc/adoplus/GetDataFromDB.aspx. There you can use the try/catch statement.

     

    Do not modify the autogenerated code. If you know the database will move, do not use a wizard that will generate the code, but write the code yourself, like in the above mentioned link.

    Thursday, July 24, 2008 2:43 PM
  • Because my reasoning is that since the designer created this file and placed it under the DataSet.xsd in the solutions pane, and that there's also this as well:

     

    Code Snippet

     

    namespace computer_database

    {

    public partial class DataSet

    {

    }

    }

     

     

    but as I said, there's nothing inside of it. I'm guessing I should put some kind of virtual or override Open() function to extend on this class, but again, how would I make that kind of interface?


    Steve

     

     

     

     

    Thursday, July 24, 2008 2:47 PM
  •  

    Read the reply above your post.
    Thursday, July 24, 2008 2:51 PM
  •  naicul wrote:

    Use the DataSet.cs file to get the data from the database programmaticaly into the dataset like this: http://asp.dotnetheaven.com/howto/doc/adoplus/GetDataFromDB.aspx. There you can use the try/catch statement.

     

    Do not modify the autogenerated code. If you know the database will move, do not use a wizard that will generate the code, but write the code yourself, like in the above mentioned link.

     

    The purpose of using the designer is to cut down on the time. I'm using the DataSet on the form itself. All of this is tied into each other.

     

    If I did it programmatically as suggested, then I wouldn't been able to create the controls properly to use on the form itself.

     

    Besides, all the code that was autogenerated is correct. The database will never change. IF the database does need to be modified at a later date, then yes, I would have to update the entire project and rebuild it again to reflect the changes, ie: added a new table, or a field was deleted.

     

    All I need is some way of changing the path to the database and prompting a user to point the connection to the right place. Another thing that I've noticed is this:

     

    Code Snippet

     

    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="|DataDirectory|\computer.mdb""

     

     

    If the "DataDirectory" as it were is part of the settings, isn't there a way to modify it at runtime if the "default" directory wasn't found?
    Thursday, July 24, 2008 3:07 PM
  • Then put a try/catch where you try to get the data (this will probably happen in the Form_Load event handler). If the database is not found, modify your app.config file with the new connection string.

    Friday, July 25, 2008 5:26 AM
  • Well, I came close on what you said, but I had to do some more research on the subject. What's missing was a couple of things. I'm only writing this up because someone else in the future would probably have the same problem as I have.

     

    If you had knowledge that this was an age old issue when it comes to designing the database in Visual Studio and the connection string, you could have warned me about it.

     

    First of all, you're incorrect on the connection string in the "settings.settings" file. The scope of connection string is set to "Application" and that cannot be re-written once it's been set (or reset) and compiled. I thought I would experiment and change the settings within the xml file of "app.config" but it didn't work.

     

    The workaround is creating another user setting and then set the "DataDirectory" macro by using:

     

    AppDomain.CurrentDomain.SetData("DataDirectory", newpath)

     

    where newpath is a string pointing to the correct directory ONLY where the database is stored, NOT THE FULL PHYSICAL PATH!

     

    More info could be found here: Smart Client Data : Working with local databases

     

    or http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx if you can't access it that way on the link.

     

    I had to create a new class and use it in the Form_Load handler event in order to correct this problem, and at the same time, store the path in the user.config file once the path has been corrected so you don't repeatedly ask the user to correct the pathway again when the application is executed at multiple times.

     

    I hope that helps anyone else that's having the same problem and saves them the aggravation!

     

    Steve

     

    Thursday, July 31, 2008 10:30 AM