Dynamic Connection String

[scottsanpedro]

Hi,
I'm looking to change the connection string (To Sql Server) on the fly based on the computer name.
Just a normal IF ELSE based on that.
I want to leave the connection string settings in app.config if possible.
I have looked everywhere to see if I can do this!!
I have tried adding a new connection string in the my.settings files and try to code it to pick a different one based on the computer name.
Any ideas and different approaches??
Thanks to all in advance
Scott

 

[Mahey]

Hi,

For SQl Connctions here I gave some good samples...I think it will help you much.

How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0.

http://msdn.microsoft.com/en-us/library/ms998292.aspx

How To: Connect to SQL Server Using SQL Authentication in ASP.NET 2.0.

http://msdn.microsoft.com/en-us/library/ms998300.aspx

How to configure SQL Server 2005 to allow remote connections.

http://support.microsoft.com/kb/914277

otherwise, you might try that IP with your username and password and see if the security allow for outside access.like this similar thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?Post&Site

one of the connection string examples here:

http://www.connectionstrings.com/?carrier=sqlserver2005

.

 

[scottsanpedro]

Hi,
Many thanks for your post.
I have already got a connection to the server, this isn;t a problem. I wanted to be able to change the connection on the fly.
I think I now understand that the app.config just runs at startup so maybe this cannot be changed onthe fly.
Maybe my only choice is to hardcode the connection string and change it then?
Thanks but still not sure
Scott

 

[Mahey]

Hi,
As per your post what I understand, you want to assign the connections as per the computer name is it right?

imports system.net
nm1 = Dns.GetHostName
or
nm1=My.Computer.Name

will give the username for the machines.
if nm1="mahey"

elseif nm1="scott"

endif

---------------------------

My opinion...

1.once you created multiuser exe, your application has to take the databases as per the requirement ...right?...

Since me also new in vb.net,Just trying to help

 

[scottsanpedro]

yes your right.
I was using the app.config xml file for the connections which I can manually change when I (in this case) move it from my virtual PC to my actual.
I thought that I could set both strings up and have them change based on my computer name.
I can do this if I use hard coded connection strings. The app.config seemed a better place.
Thanks for you help and input, I'm the same as you. Been in databases for many years and now porting over to ADO.net
Scott

 

[Kliot]

In my app I have a Preferences file where users can select a connection, it modifies the app.config file at runtime even though it is a read only setting. You should easily be able to do what you want with this technique.

Thanks to the VB Team they have a nice video explaining how to do this at

http://msdn.microsoft.com/en-us/vbasic/cc307956.aspx

They have a number of excellent videos on their site.

Perrin

 

[scottsanpedro]

thats pretty much what I was looking for.
Excellant
Scott

 

[scottsanpedro]

HI again,
Nearly was what I was looking for. Actually I think it still is but I am getting problems.
I have run through the video (BTW I'm using VS 2008) using the following;

#If Not Debug Then
        My.Settings.RunTimeConnectionString = My.Settings.ScottMCECOn
#End If
        My.Settings.Reload()

The correct connection string gets posted to the text box, but the old connection is used. Only on a refresh is seems to work.
When I run the exe on the other computer it just errors.
Not sure if there is a way to refresh the app.config. I tried;

My.Settings.Reload()

Thanks again
Scott

 

[Kliot]

Scott,

Can you post your RunTimeConnectionString property, I'm thinking the problem is in there.

You shouldn't need to refresh the app.config but are you saving the settings to the app.config? my.settings.save Depending on your code you may need to.

You also need to make sure that after the change is made the connection string is re-read.

Perrin

 

[scottsanpedro]

Hi Kliot,
Thanks so much for getting back to me.
Here is the code I'm using

 Partial Friend NotInheritable Class MySettings
        Public WriteOnly Property RunTimeConnectionString() As String
            Set(ByVal value As String)
                My.Settings("HomeConnectionString") = value
            End Set
        End Property
    End Class

Here is the call made in the load event

Private Sub Team_Viewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        
        My.Settings.RunTimeConnectionString = My.Settings.ScottConnection
        Me.TblTeamViewerTableAdapter.Fill(Me.HomeDataSet.tblTeamViewer)

        Me.Text = General.ConTitle
        Me.TextBox1.Text = My.Settings.HomeConnectionString
    End Sub

I have two string in the settings file.

Name:HomeConnectionString
Data Source=GMCSCOTTVM;Initial Catalog=Home;Integrated Security=True

and

Name:ScottConnection
Data Source=SCOTTMCE;Initial Catalog=Home;Integrated Security=True

When I run it on the virtual machine(GMCSCOTTVM) it works the first time though shows the SCOTTMCE in the connection string in the textbox1.
I then run it again, and its fails. Its like there is a no re-read maybe?
Cheers agains
Scott

 

[Kliot]

Scott,

Sorry it takes me a day between posts, I'm on on the road this week and have very limited computer time.

I think the problem is you are trying to change my.settings and that is what we are trying to avoid, try changing your RunTimeConnectionString property from

My.Settings("HomeConnectionString") = value

to

Me.Items("HomeConnectionString") = value

This way we can get around the readonly property of My.Settings

Perrin

 

[scottsanpedro]

Yep. That was it. I must of missed that bit.
Just for completness if anyone else reads.

Item only works singluar

Me.Item("HomeConnectionString") = value

Many thanks for your time on this. All fun and games! (Now and again)

Scott