Change Connection-Property / Connection-String of interactive generated TableAdapter at RunTime

Hi experts
On the Main-Form of a Project we have an Dataset and various DataTable-Objects.
We have generated the Dataset and the DataTable-Objects interactive.
We need the objects on the Form to make changes interactive and to link and design the various Datagrids on the Form.
At Design-Time we have placed the Connection-String in the Settings (including username and password) an then manually set the Connection-Property of the TableAdapter-Objects to the Connection-String in the Settings.
Everything works fine...
Now, we want to exclude the Password from Settings for Security (generate the password in Code) and we want to change the Database dynamically.
Now we generate dynamically a Connection-string in the Code with the generated password.
We change then the Connection-Propertys of the TableAdapter in the .New-Event of the Main-Form (this works).
Obviously the (interactive placed) Objects on the Form automatic wants to make Connections  to the Database when the Main-Form loads.
At this time, the new (generated) Connection-String with the Password is not in Place -> an Error occurs.
If we change the ConnectionModifyer-Property of the TableAdapters from "Friend" to "Public" and modify the InitializeComponent-Event as follow:
- Me.XXTableAdapter.Connection = conn_string1
- Me.YYTableAdapter.Connection = conn_string1
it works (for one Time:-) without error.
The Problem is, that the Code in the InitializeComponent-Event is overwritten, everytime a new Version of the .exe is generated:-(.
Where do we have to place the Code (Me.XXTableAdapter.Connection = conn_string1)
for solve the Problem?
Thank you for a Solution:-)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin McDanielSr. DeveloperCommented:
It sounds like your logic "works" because you can connect to the database at least once. However, you need a way to hold on to the generated password once it's created for each time after the executable is run. Is this correct?

Let me know; I think I've got a general solution in mind that will work.
HoneymoonAuthor Commented:
I need a solution to replace the connection-string in the TableAdapters at the right time (right event)
The code works in general, but the designer-tools overwrite my code.
If i place the code in the InitializeComponent-Event, it works for the next compilation. if i than make changes in other code and make an new compilation, the designer replace the InitializeComponet-code and the error occurs.
Example for one of the Tableadapters

Partial Class Hauptmaske
Public Sub new()
.... code
.. code to generate the connection-string "conn_string1"
end sub

Private Sub InitializeComponent() ' this code is generated from the form designer
........ Code
Me.PersonendatenTableAdapter = New WPE.NET.DataSet_WPETableAdapters.PersonendatenTableAdapter ' generated code
Me.PersonendatenTableAdapter.Connection = conn_string1 ' My code
..... Code
end sub

This code works, but if i make changes in other code in the project, the code for InitializesComponent is completely replaced by the designer.
The Question is, where (in wich event) can i place my code so that it stays and is executed before the objects on the form want to make a connection.
Kelvin McDanielSr. DeveloperCommented:
I was able to find this Forms Event Lifecycle:

Move: This event occurs when the form is moved. Although by default, when a form is instantiated and launched, the user does not move it, yet this event is triggered before the Load event occurs.
Load: This event occurs before a form is displayed for the first time.
VisibleChanged: This event occurs when the Visible property value changes.
Activated: This event occurs when the form is activated in code or by the user.
Shown: This event occurs whenever the form is first displayed.  
Paint: This event occurs when the control is redrawn.
Deactivate: This event occurs when the form loses focus and is not the active form.
Closing: This event occurs when the form is closing.
Closed: This event occurs when the form is being closed.

Given this, I would try putting it in the Load event.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HoneymoonAuthor Commented:
Hello azarc3
i have tried load-event and shown-event also before i have posted the problem...
This events are to late
I need a event imediatly after InitializeComponent.
Kelvin McDanielSr. DeveloperCommented:
Try the Form's New() method...

1. Open the Designer.vb code

2. Click somewhere inside the InitializeComponent() method

3. In the Document Window (where your code is), make sure your {Form_Name} is selected in the LEFT drop down (at the top of the Document Window).

4. In the RIGHT drop down, select the New method; the code below should be generated

5. Add your code to set the {TableAdapter_Name}.Connection.ConnectionString AFTER the InitializeComponent() call.
Public Sub New()

        ' This call is required by the Windows Form Designer.

        ' Add any initialization after the InitializeComponent() call.

    End Sub

Open in new window

Kelvin McDanielSr. DeveloperCommented:
I've got a good test project working now, where I...

1. Put my ConnectionString code (which is where your ConnectionString gen code would go) in the Form's New() method

2. Deliberately set the ConnectionString in the Designer to Nothing

3. Put my Fill code in a button's Click() event.

4. Put code to blow out the TableAdapter's ConnectionString, Fill the table (and deliberately error), then Reset the ConnectionString after a Try..Catch block in a different' button's Click() event

This works consistently for me. I know it's not getting the data from the Settings file because that ConnectionString is bogus.

Good luck!
Kelvin McDanielSr. DeveloperCommented:
It that the part of the TableAdapter's initialization is to perform an initial Fill(), so you may need to add another line to that New() method to... encourage your real connection to be used, like so:
Public Sub New() 
    ' This call is required by the Windows Form Designer. 
    ' Add any initialization after the InitializeComponent() call. 
    Me.{TableAdapter_Name}.Fill(Me.{DataSet_Name}.{DataSet_Table_Name}) ' <-- this is the new line.

End Sub

Open in new window

HoneymoonAuthor Commented:
The Code IS actually from the New-Event (see my example-code from 21.04.)
The Error occurs IN the InitializeComponent-event wich is run from New-event.
If i paste the Code after InitializeComponent() in the new-event (first command) it's too late (that was my first try)
It works only, if i paste my code in the InitializeComponent-Event, wich is overwritten from Designer everytime i make a Change in the Designer (new Button, new Label. etc.)
How can i set the ConnectionString in the designer to nothing?
Kelvin McDanielSr. DeveloperCommented:
My mistake for not noticing your use of New() before...

... which is the part that I'm not understanding; the ConnectionString in the Properties Pane (and used in my InitializeComponent()) is definitely invalid but doesn't throw an error. Then I correctly set it it immediately after InitializeComponent() completes. I'm also manually calling Fill() on my TableAdapter.

I think the difference in our results is that I left the original string in there while I got everything working, then altered 1 character to make sure it was invalid. IMPORTANT: I did NOT physically remove the ConnectionString though.

When I run this without updating the ConnectionString nothing happens... as is expected I get an empty grid. Then I go back and uncomment the code in the New() event that resets the ConnectionString and everything's gravy.

Have you tried leaving an invalid ConnectionString in there?

I was setting my ConnectionString to Nothing in order to make sure it wasn't the source of what I was looking at. You don't want to do this as it will DEFINITELY throw an exception (unless you catch and swallow it).
    Me.Aspnet_UsersTableAdapter.Connection.ConnectionString = Nothing
    Me.Aspnet_UsersTableAdapter.Fill(Me.DataSet1.aspnet_Users) ' <-- This will throw an exception
Catch ex As Exception
    'Exception is swallowed... which is a BAAAAD practice unless there's absolutely no other way
End Try

Open in new window

HoneymoonAuthor Commented:
Hi azarc3
I have found now a solution myself...
I Fake the Get-Method of the ConnectionString-Setting
- Normaly the get-method is als follows:
Return CType("XX_ConnectionString"),String)

Now, i generate the Return-String
Dim ConString as String
ConString = "AAA" & "BBB" & "CCC" & "DDD" (Server, Catalog,  User,  Password-> static)
Return ConString
The Connection-Property of the DataTables is connected to "XX_Connection_String" (as before).
At Start-Time, the Application reads the XX_Connection_String" from the Settings automatic (as before).
But now, the XX_ConnectionString do not return the Text in the Setting but the generated ConnectionString from the get-Method to the Setting "XX_ConnectionString".
So the Passwort is not loger saved in the settings (application.exe.config), all works and the Code is not overwriten every time, we make an Change on the Form.
Thanks anyway for your Time

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.