How to have a user input user and password for a connectionstring

[Smog]

I work at a help desk and I have made a utility that allows some of the lvl 1 tech sto do some basic sql to fix basic issues without knowing sql. The problem is I can't have the user and pw hardcoded I need to allow the techs to input the correct user and pw. mainly because the user and pw are different depending on what customer they are helping. i have tried to update the connection string with user inputed info but ti tells me that the value is readonly even tho it has a set method

system.configuration.configurationmanager.connectionstrings("data").connectionstring = new connection string

It tells me that the configuration is read only and throws an exception. is there a better way of doing this or how can i get past this error message.

 

[jmcilhinney]

If the tech has to input the credentials each time then you don't want to change what's stored in the config anyway. Just create a ConnectionStringBuilder of the appropriate type for your ADO.NET provider (e.g. SqlConnectionStringBuilder, OleDbConnectionStringBuilder) from your existing connection string, then set its UserID and Password properties, then get its ConnectionString property for the new connection string.

 

[cjard]

The connectionstring is read only within code because it is an Application Scope setting. If you made it user scope, it would become writable

 

[Smog]

Ok cool I looked up some code samples of the sqlconnectionbuidler and that will work for sure. I am using datagrid views to grab the data. To use these you create a connection then a data set, binding adapter and a table adapter. I am pretty new to Database access with vb.net. How do I populate the dataset using the connection string from the sqlconnectionbuilder?

 

[jmcilhinney]

If you're using TableAdapters then you first have to make sure that they expose the ir Connection publicly. Open your DataSet in the designer, select one of your TableAdapters and make sure its ConnectionModifier property is Public. You can then do this in code:

myTableAdapter.Connection.ConnectionString = myConnectionStringBuilder.ConnectionString