How to pass two connection string ? ASP.Net

[tdong]

Currently is what we have. How do I pass another connection string if the first connection string failed ? Thanks. We run the sql server at our place and we have 2 ips that is why I need to use the ip if one doesn't work

Public Sub New()
strConnect = oStat.ConnectionString
Try
oConnection = New SqlConnection(strConnect)
oConnection.Open()
bConnected = True
Catch ex As Exception
bConnected = False
UpdateEventLog("Connection to SQLServerFailed")
End Try

End Sub

 

[jubble]

you just need to create a new instance of the connection object that refers to the second connection string

the second connection string can be passed via another property in the class

add the following code after the Catch statement...

oConnection = nothing
oConnection = New SqlConnection(me.SecondConnectionString)
oConnection.Open()

and readjust the bConnected and UpdateEventLog lines

this would be best wrapped in a try catch block in the same way as the original

 

[tdong]

Thanks I got this working is it efficient ? do I need to set oConnection = Nothing in the catch ?
Public Sub New()
strConnect = oStat.ConnectionString
bConnected = False
Try
oConnection = New SqlConnection(strConnect)
oConnection.Open()
bConnected = True

Catch ex As Exception
'try another connection since we have two ip at newshore
Try
oConnection = New SqlConnection(strConnect2)
oConnection.Open()
bConnected = True
UpdateEventLog("Connection to SQLServer Failed but Retry ok")
Catch ex2 As Exception
bConnected = False
UpdateEventLog("Connection to SQL Server Failed")
End Try
End Try

 

[jubble]

it is good practise to set objects = nothing when finished with them

that looks OK except I can't see where strConnect2 is coming from...obviously modular or it wouldn't build correctly.

With that in mind if strConnect2 was an empty string I think you would get a different error than if the connection failed so you might want enhance the error catching so that it differentiates between specific errors (sqlexception or whatever) and can inform the user as to how to rectify the problem but that really depends who you're writing the application for.

 

[tdong]

thanks the strconnect2 will be in the oStat.connectionstring2 since I was testing so I create the strconnect2 in this class heheh