Change Connection String based on checkbox

Hello everyone,

I'm working on a VB application in VS 2005. The application was originally built in 2003 (1.1) but I'm starting afresh - I did not want to migrate / upgrade.

The application is used for 3 sites, and each site has it's own backend SQL database (all on the same central server) and all databases are set up exactly the same - it's set this way to split the data, as certain lookups and tables are site specific.

On the original application, I have 3 radioboxes - Site1, Site2, Site3 appear on a form that is first loaded. Only one of these checkboxes can be checked, and when one is, a global variable is set to true, and another two to false -

i.e

If me.rbSite1.checked = true then

varSite1 = true

varSite2 = false

varSite3 = false

elseif me.rbSite2.checked = true then

varSite1 = false

varSite2 = true

varSite3 = falise

elseif me.rbSite3.checked = true then

....you get the point

Then on every form that had dataAdapters, I would create a connection for each of the 3 databases, and at form_load;

if varSite1 = true then

me.dataAdapter1.selectcommand.connection = me.conSite1

me.dataAdapter1.updatecommand.connection = me.conSite1

elseif varSite2 = true then

me.dataAdapter1.selectcommand.connection = me.conSite2

Now, working in 2005, I see that data access has changed.

On each "data access" form I am using Typed Datasets, BindingAdapters and dataTables.

I have added the connections I require to My Project Settings tab.

How can I set the correct SQL connection on each form

Regards,

.NET Development37

Try extending your TableAdapter to allow changing the connection string or entire connection inside the adapter's queries. This link talks about the basics for how to modify TableAdapters.

http://msdn2.microsoft.com/en-us/library/ms233697(VS.80).aspx

I have read through that article but I am still wondering how that helps to dynamically set the connection string when the designer-generated code is handling the setting of the connection. Perhaps a slightly more elaborate response is warranted here.

Thanks.

Marc.

I’m not an expert in UI but you can group the radio buttons in a group, and the group will give you the index of the selected radio button. Then you can use a global array of connection strings where the elements correspond to the radio buttons on the GUI.

Zlatko

For the TableAdapter side, my thought was to define a partial class extension to the adapter that takes a new connection string. This method should have access to the field containing the underlying DataAdapter, and you can access the connection via the commands. Something like:

partial class MyTableAdapter {
void SetConectionString(string connStr) {
dataAdapterField.SelectCommand.Connection.ConnectionString = connStr;
dataAdapterField.UpdateCommand.Connection.ConnectionString = connStr;
...
}
}

I'm not sure what the name for the generated DataAdapter field is -- you'd have to look that up in the generated code.