ADO Connection Error handling

[williamowens3]

I'm attempting to connect to various SQL DB's on different Servers
by using the IP of each server.
The problem is that if the IP has changed and I attempt to connect I get the old -2147467259 error when the app is attempting to Open the connection.

Is there a way to trap this error so that if the Open doesn't occur
I can handle it?

Also can I incorporate the "ping.exe" into my code to update the
DB ServerIP field???

Dim cSQLConn As Connection
Set cSQLConn = New Connection
cSQLConn = "Provider=SQLOLEDB" & _
";Data Source=" & rsServer.Fields("ServerIP").Value & _
";User ID=" & rsServer.Fields("ServerDBLogin").Value & _
";Password=" & rsServer.Fields("ServerDBPassword").Value & _
";Initial Catalog=" & rsServer.Fields("DatabaseName").Value
cSQLConn.CommandTimeout = 400
cSQLConn.Open

Thanks

Bill

[Mega_Man]

On Error goto Errorhandler
 
Dim cSQLConn As Connection
Set cSQLConn = New Connection
cSQLConn = "Provider=SQLOLEDB" & _
";Data Source=" & rsServer.Fields("ServerIP").Value & _
";User ID=" & rsServer.Fields("ServerDBLogin").Value & _
";Password=" & rsServer.Fields("ServerDBPassword").Value & _
";Initial Catalog=" & rsServer.Fields("DatabaseName").Value
cSQLConn.CommandTimeout = 400
cSQLConn.Open
 
ErrorHandler:
Select Case err.Number
    case -2147467259 
    msgbox "Error opening connection"
    case Else
    msgbox "Different error"
End Select

As for pinging a I.P address, you need loads of APIs. Do a search in the general VB questions forum. There are loads of eamples.

Mega.

[williamowens3]

Mega_Man

Thanks for the reply...
After attempting several error handling options this one seem to work best...

Dim cSQLConn As Connection
Set cSQLConn = New Connection
cSQLConn = "Provider=SQLOLEDB" & _
";Data Source=" & rsServer.Fields("ServerIP").Value & _
";User ID=" & rsServer.Fields("ServerDBLogin").Value & _
";Password=" & rsServer.Fields("ServerDBPassword").Value & _
";Initial Catalog=" & rsServer.Fields("DatabaseName").Value
cSQLConn.CommandTimeout = 400
On Error Resume Next
cSQLConn.Open
If cSQLConn.State = 0 Then
Goto ErrorHandler
End If

ErrorHandler:
Select Case err.Number
case -2147467259
msgbox "Error opening connection"
case Else
msgbox "Different error"
End Select

I tried the option you offered and it works the first time but since
this is part of a loop statement where I make several connections
to different servers, the error handling fails when on the 2nd through N attempts.

Thanks for the help

Bill

[Mega_Man]

You might want to try placing a doevents just after you Connection.Open statement. This should ensure that the error trapping works every time. The other thing you can do in your loop is to only establish a connection after the last one has connected.

If Connection.State <> adStateConnected
' Dont establish another connection.

Mega.

[JPnyc]

As long as we're on the subject, I placed an errorhandler in a form, and I want to send there on error from other forms,subs. Do I call it like any procedure?

[John McKernan]

Originally posted by DUNSEL
As long as we're on the subject, I placed an errorhandler in a form, and I want to send there on error from other forms,subs. Do I call it like any procedure?

Not real sure what you're asking. Can you give some more info or post a sample of your code?

[JPnyc]

Basically asking the syntax. Near the top of the sub, the words, On Error, then Goto, then the label of the error handler starting with formname.errorhandlername?

[Mega_Man]

If you want to call an error handler from anywhere in you project (irrespective of form), then place all you error handling code in a public module.
You can then call it like such.

On Error Goto ErrorHandler
'code
'code
ErrorHandler:
Call ErrorRoutine ("module name")
 
Public Sub ErrorRoutine (Dim Error As String)
msgbox "A Error has occured in module " & Error
End Sub

Mega.

[John McKernan]

OK, I'm sorry but I might still be a little confused here, but a normal error handling routine would look like this:

Public Sub SomeSub()
   On Error Goto ErrHandler
 
    'sub code goes here
 
    Exit Sub  ' usually so you don`t execute the error handler unless there is an error
ErrHandler:
    'Error handling code here
End Sub

Now, if you to call this ErrHandler from another form or sub, you cannot.

You can, however, write a general error handler as a stand-alone sub or function and then call that from other forms, modules.

Did this help? Like I said, I think I'm still a little confused about what you are trying to do.

[JPnyc]

Thank you. I knew it needed to be called near the beginning of subs where errors might be likely, and then labeled, but was unsure of the syntax.

[John McKernan]

Originally posted by DUNSEL
Thank you. I knew it needed to be called near the beginning of subs where errors might be likely, and then labeled, but was unsure of the syntax.

Glad it helped.

[John McKernan]

Originally posted by DUNSEL
Thank you. I knew it needed to be called near the beginning of subs where errors might be likely, and then labeled, but was unsure of the syntax.

Yes, it does have to be labelled (unles you use line numbers), but the On Error Goto does not have to be the first line in a sub/function. It just has to appear before yuo anticipate the error.

[JPnyc]

I wanted to set up a central public error handler, and wasn't sure if I needed to call the form name or not, like you do in any sub call from another form.

[JPnyc]

You've all been most helpful. Can any of you direct me to the best book/site for learning VB procedures/methods/operators on my own? I have basic programming knowledge, but very sketchy on VB. Thanks.

[John McKernan]

A public central error handler should be placed in a standard .bas module.

Public subs/functions in modules do not require the Module name. For example, assuming a module named modExample and a sub name MySub, either calling syntax is fine:

MySub
modExample.MySub