vs2010 update tableadapter connection string (dynamically) - pls help

[wikkedskye]

Hi Everyone.

I am working on a program using WinForms in VS2010 and SQL Server 2008 as backend database. I know my endusers will not have the same connection string for the database as I will. I've done research and figured out how to design a form that takes user information in textboxes for the connection string information (server name, authentication, database, etc). This information is written to an xml file. In the settings.vb tab of project settings, I have viewed the code and created a User String called MyConnectionString. In the code view, I have set things up to set the original connection string to now use my user-defined string. This appears to work correctly and the XML file saves. My understanding is that when I load my main form, I am supposed to read/load the XML file and then call My.Settings with the name of my user-defined connection string before any TableAdapter Fill() commands.

As I have a database created for my program, I create datasets and then drag the fields that I need onto my forms - this creates the dataset, binding source, table adapters, etc. In order to test everything out, I have created a simple test project with a few forms, and used the Contact field of the AdventureWorks database. The dataset for this can be viewed in the designer. Creating the dataset creates a connection string (which should be able to be changed based on reading/loading the XML file & calling My.Settings before the TableAdapter Fill() command.).

If I leave the tableadapter fill code alone, the adventureworks contact form loads data with no issues. If I try and make any changes, nothing happens (even if i use the user input form and set the database for adventureworks). I have set the connection modifer on the table adapter to public. I have made a call to My.Settings, I have tried using partial classes of the table adapter to tell it to use my user-defined connection string.

Does anyone have any idea what I might be missing and how to make this work? Or do I need to post samples of the code I have used or let someone view the basics of my VS2010 solution?

Would really appreciate help with this.

Thank you,

Kelly

[techgnome]

How are you updating the database? When you say "If I try and make any changes, nothing happens" I'm assuming that the changes are to the data... and when you re-start the app, the changes are gone... right? Could be two reasons... 1) you're not actually saving the changes to the database... doesn't happen automatically, you have to make it happen. 2) You're validating against the wrong data - in my signature is a link "I swear I saved my data, where'd it run off to?" ... that goes into some explanation... this will only apply if you have added code to do the saving back to the database... if you're not sure about the saving, odds are you haven't... so that's where I'd start.

-tg

[wikkedskye]

Sorry if I was not very clear. What I meant by nothing happens is the following:

I dragged the contact dataset onto a form as a datagrid. VS automatically puts the tableadapter.fill() code in the form_load event. If I leave this code alone, when I debug the program and open the form, the data is loaded into the datagrid. As soon as I try to make any kind of changes for the table adapter to use my user-defined connection (whether I set this in the partial class tableadapter accessed by viewing the code of the table adapter of the dataset in the designer, or if i put in calls to My.Settings and state to use my user-defined connection string before the tableadapter.fill() code inthe form_load event), then when the form is opened up, no data is loaded into the datagrid. I do not get any errors, so I am at a loss as to what I need to change.

[techgnome]

Ah... ok... I'm with you now... here's what I think is happening... your user-defined setting hasn't been set yet... when you created the setting, did you give it a default value? If not, then you're basically supplying an empty connectionstring... in which case, yup, you're not going to get any data.

To confirm this, put a message box jsut before the first time you try to use the setting and have it displayed... see if it returns an empty message box, or a connectionstring.. and if it does show a connection string, that it is correct.

-tg

[wikkedskye]

Thank you for your quick response. It does make sense to use something like the message box to determine whether or not the connection string has been set. (I imagine this will also help me figure out where I need to go next)

I have never used message boxes before. Would you be able to give me some idea of what the code is to use for a message box? And then is the message box code placed in the form_load event of my form so that it will appear before I try to load data or would you suggest I put it elsewhere?

Thanks much.

[GaryMazzone]

If you are loading data on form load then that is where I would place the Messagebox.

Messageboxs are used like this:

MessageBox.Show("Something you want to display goes here",'MEssagebox Title")

[techgnome]

messagebox.show("Put what ever text you want displayed here - " & andAVariableToo)

that's it... really as simple as it gets.

-tg
Gary beat me to it.... :P

[jmcilhinney]

To change a typed DataSet connection string from the value used during development you have two definite possibilities and one maybe:

1. Edit the config file by hand before running the application. This is what you're supposed to do. When the app runs, the connection string is loaded from the config file. Whatever is found there is what gets used.

2. Make the Connection property of your TableAdapters Public and then set the Connection.ConnectionString property in code for every TableAdapter you create. This should generally be avoided if possible.

3. I have never tried this so I don't know if it will work or not. Follow the CodeBank link in my signature and check out my Protected Configuration thread. You can ignore the encryption part but it shows you how to edit the primary config file at run time. After editing, you could call My.Settings.Reload. I don;t know whether this will reload connection strings too but you could try it out.

[jmcilhinney]

I now discover that you created this thread when people were already helping you with this issue in another thread. Please post each question once and once only. If you have or need more information, post it in that same thread. Also, it's a little rude to turn your back on those already helping you and start a new conversation on the same topic. If you think that you've posted in the wrong forum, ask the mods to move the existing thread. I've now asked them to merge the two.

[wikkedskye]

Sorry if I inadvertently offended you or anyone else with my "rudeness". I was trying to find help for something that I have spent weeks trying to research and I only asked for help when I had exhausted my resources. I posted in both forums at the same time because I did not know which was the right forum and figured that if one was in the wrong place, someone would let me know. The things that you suggested, I had already tried (I talked about this in my post) and they did not work. As for turning my back on those who were trying to help me, I have thanked them and told them that I would try out their suggestions and see what happened.

I did see another post during my researching where Beth Massi from Microsoft was unsure of the precise solution. I was just hoping that somebody had come up with a solution, rather than having to move back to an older version of visual studio or look for a different software other than Microsoft to use.

[Hack]

Duplicate Threads Merged

Please do not create mutliple threads for the same topic. I deleted a duplicate of this thread in the VB.NET section yesterday only to you have you recreate the duplicate a few hours later.

One topic, one thread, one section please.

Thank you.

[techgnome]

well, first things first... seeing if you're even getting a connection string in the first place. Once you've determined you're getting a connection string, then we can work on getting it into the typed dataset and the data loaded.

-tg

[wikkedskye]

Okay, I tried the messagebox.show() but for some reason never got a message box to come up. I remembered watching a windowsclient.net video by Pat Tormey where he used a textbox to display the connection string, so I thought I would try that.

I checked to make sure that the AdventureWorksDataSet had the conenctionmodifier set to public. There is also a partial class for the table adapter with the following code:

Partial Class ContactTableAdapter
Public Property RunTimeSqlConnection() As SqlConnection
Get
Return Me.Connection
End Get
Set(ByVal value As SqlConnection)
Me.Connection = value
End Set
End Property
End Class


In my form_load event before the adapter.fill() code, I have added this code:

My.Settings.RunTimeSqlConnectionString = My.Settings.MyConnectionString

I then set a textbox with the following for one debug:

Me.Textbox1.Text = My.Settings.MyConnectionString

On the second debug, I set the textbox to:

Me.Textbox1.Text = My.Settings.AdventureWorksConnectionString

When My.Settings is set and the textbox is set to display the Adventureworks connection string, when I open the form the first time, I get nothing in the datagrid or the textbox. When I open the form the 2nd time, it shows the AdventureWorks data in the grid and the connection string in the textbox as the adventureworks connection string.

When My.Settings is set and the textbox is set to display MyConnectionString, when I open the form the first time, I get nothing in the datagrid and nothing in the textbox. When I open the form a second time, the adventureworks data is in the datagrid and the textbox is empty.

I have no idea what I might be missing or where I went wrong. As I said previously, I can go to the directory where my xml file to store the connection string is located and see that the file is saved and that it changes based on what I set the database and connection string as.

Based on what was said earlier, it sounds to me as if the connection string I want to set is not getting set for some reason. If it would help, I could definitely send my project file in a zip code in case I am completely missing anything.

[wikkedskye]

Hi Everyone,

While I have been waiting to see if anyone has any other suggestions for me, I have tried a few other things. Currently I have 2 forms in my project. One is an MDI Parent, and the other is the child form. The child forms will be the ones that will deal with data, so I have only been setting My.Settings.Connection in the Form_Load event of that form. I did try and set My.Settings in the Form_Load event of the Parent form, but that did not make any difference. I also went through and checked all my code to see if I could see anything - I did not notice anything.

Another thing that I tried was related to the block of code in the Form_Load event that is to read the information in my xml file for connection string information. (I have this xml file currently set for a different connection string than the AdventureWorksConnectionString that is created when I made a dataset).

Here is the code that I have to read the xml file:

Dim xmlDoc As New XmlDocument
        xmlDoc.Load(My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\MystykaAppData\" & "ConnectionStrings.xml")
        Dim connStr As String = ""
        Dim xn As XmlNode
        xn = xmlDoc.SelectSingleNode("ConnectionStrings")
        connStr = xn.InnerText
        
        My.Settings.RunTimeSqlConnectionString =  My.Settings.MyConnectionString

        Me.TextBox2.Text = My.Settings.MyConnectionString

In the above-mentioned code block, I have Dim connStr As String and also connnStr = xn.InnerText. I thought that maybe this connStr value needs to somehow point to My.Settings.RunTimeSqlConnectionString and or My.Settings.MyConnectionString.

I tried this:

My.Settings.RunTimeSqlConnectionString = connStr = My.Settings.MyConnectionString

and this:

My.Settings.RunTimeSqlConnectionString = connStr

connStr = My.Settings.MyConnectionString

When I try either of the above things, I finally get something to appear in the textbox that is set as Me.TextBox2.Text = My.Settings.MyConnectionString BUT it it not a connection string that populates the textbox. The text that appears is the word "False". No idea what this means - I know False and True are Boolean values, but have no idea why this would appear in the textbox.

In doing further research, I have seen that there is a way to modify the app.config file at runtime by creating a form that takes the user input for the connection string and utilizes a class file and passes parameters to keys that are in the app.config file. Everything that I have read has differening opinons on modifying the app.config file. The main issues that I have heard is 1)that if you have a computer that has more than one user set up for it, that the app.config setting will make it so that only one user can use the application unless you have each user modify the app.config connection string each time they log on and then run the application; and 2) if for some reason the user logged onto the computer does not have permission to write to program files, then they will not be able to modify the app.config file and thus will not be able to use the application with an appropriate connection string.

As one never knows exactly what setups their endusers will have, I am somewhat leery about trying this approach, so I am hoping someone here will have some other suggestions/thoughts/ideas that I can try out.

Thanks in advance for reading and any assistance.