March 7, 2008 at 4:28 am
Hi Guys,
Hope you can help me. I am a VB programmer and have no problems with connecting to a SQL database.
When I distribute my project the server name will be different in the connection string which I now change manually before building.
I would like to find out how to test in code wether the SQL connection is successful or not without waiting for a timeout and the compiled program bombing out so I can display my own error message.
Regards,
John.
March 7, 2008 at 6:13 am
A well known concept in software industry is configurability, meaning your app reads an external config file (INI, XML,...) or registry. Or asks the user and saves his input as configuration parameter.
March 7, 2008 at 10:56 am
Thankyou Robert for your reply.
I realise that but if I ask the user to enter these details, how would I then in code check that their input is a valid connection?
Answering this question would also answer my original question.
I just want to check a connection string and see if it is valid or not.
Thanks for your input.
March 7, 2008 at 2:10 pm
I'm not sure this is possible without a timeout. You'll notice that if you put an invalid server name into a connection string builder dialog box, you'll wait for some time before the code tells you it is not valid. One thing I do is simply try to execute the sql statement "Select getdate()" inside of a try catch block, and an error means the connection was not successful. Catch the error and display a friendly message.
March 7, 2008 at 4:07 pm
The following example tries to answer the following 3 questions.
1) Is the server on the network?
2) Is the connection string in a valid format?
3) Can a connection be made to SQL?
Example:
' Add Imports System.Net.NetworkInformation
Try
Dim myPing As New Ping
Dim myReply As PingReply
myReply = myPing.Send("mySQLServer", 500)
If myReply.Status <> NetworkInformation.IPStatus.Success Then
Throw New PingException("Server did not respond.")
End If
Dim myconnection As New SqlClient.SqlConnection
myconnection.ConnectionString = _
"Persist Security Info=False;Integrated Security=SSPI;database=myDB;server=mySQLServer;Connect Timeout=3"
myconnection.Open()
Catch pingex As PingException
MsgBox("Unable to ping.")
Catch argex As ArgumentException
MsgBox("Invalid connection string.")
Catch sqlex As SqlClient.SqlException
MsgBox("Unable to connect.")
End Try
March 7, 2008 at 7:43 pm
Perfect Todd, just the sort of answer I was looking for.
Thanks for all your help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply