[RESOLVED] Remote connection: if not whitelisted, takes forever

[chris.cavage]

Hello,

I have a program that connects remotely to a mySql database server. On form load, I have the connection made to display information on the main screen.

In my cPanel (where I host my data) I *must* whitelist the IP address from the computer that is connecting remotely to the database in order to get past my host's firewall.

I have an error handler that will step in if there is a problem with connecting to my database on form load. On error, a msgbox alerts the user that there is a connection problem, then exit sub.... so then the rest of the program still loads.

Here is what I notice:

If the internet connection itself is down, the error handler works fast, and the msgbox appears right away.

Sometimes my IP address changes from my internet service provider, and it will have to be relisted to the host's firewall in order to connect properly. If the whitelisted IP is dropped, the error handler takes forever to show the msgbox: my application takes a long time to load then.

Other forms that use connections to the database also take forever, and sometimes my application will temporarily freeze until the msgbox appears finally.

What can be done about this? Any ideas? Thanks.

[LaVolpe]

Just a thought. The ADO connection object as a ConnectionTimeout property, you may want to play with that?

The ConnectionTimeout property sets or returns how many seconds to wait before cancelling a connection attempt and generating an error. The default is fifteen seconds. However, heavy server use or high network traffic can easily cause delays greater than fifteen seconds. If you set ConnectionTimeout equal to zero seconds, the program will wait indefinitely or until the connection is completed.

[chris.cavage]

I will read about this now, thanks.

I found some quick google sources for others who don't know about the timeout property, like me:

http://www.w3schools.com/ADO/prop_co...iontimeout.asp
http://docs.sun.com/source/817-2514-10/Ch11_ADO30.html

Maybe I can set the timeout to about 3 seconds and then generate the error, that way there is no significant lag time. Read that the default is 15 seconds also - maybe that's what's going on now.

I will work on this tonight and post back when I make progress. Thanks, LaVolpe.

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

Changing the ConnectionTimeout is the right way to go, it should reduce the delay.

Note however that web requests can be delayed randomly, so 3 seconds to complete the connection (which takes a few steps) may not be enough - under some circumstances it can take nearly 10 seconds on my computer when the database is local.

[chris.cavage]

Thanks for moving the thread. I was uncertain where it should go. I was 50/50. :-)

Ok, I will avoid three seconds... if I set the timeout to 10 seconds, then I am kind of back to the beginning, (or no?), since it is taking about that long to show the msgbox saying the connection is down.

What is driving me to do this really is: if a form that gets database information is loading (while my program is already running) and the whitelisted IP is dropped beforehand, I don't want the user wondering if is going on for 10 seconds - if the user clicks around randomly on other command buttons, errors can happen.

Thanks for the help.

[si_the_geek]

What you can do is connect asynchronously, by specifying adAsyncConnect in the Options parameter of .Open , followed by a loop (containing DoEvents) based on the .State property.

Note however that you will need to disable buttons etc, as DoEvents will allow their events to fire.

[chris.cavage]

I will try all this.

I was thinking, is there a way to just make sure that my IP stays the same? That way I wouldn't ever have to worry about having to relist the IP to get past the firewall.

[si_the_geek]

You might be able to use wildcards in your whitelist, otherwise your best bet is to check with your ISP - they will probably have the option for you to pay extra for a fixed IP address.

[chris.cavage]

I am working on this:

Setting the connection timeout property to 15 seconds.
I want the user to wait until the timeout expires or not... If the timeout expires, then I was a Label1.Caption to say "connection timeout".

I have a way to recognize when the connection is made to load the Label1.Caption from a field from the connection.

So when the form loads, I want it disabled until the connection can be established or not.
How can I get a way that if the connection is timed out (cannot connect) then enable the form and set Label1.Caption = "connection timeout"

*the connection is done to load a number into Label1.Caption on the form from LoadDataInControls

I also have my connection done "adasyncconnect".

 
Me.Enabled = False
Set cn = New ADODB.Connection
    cn.ConnectionString = "string"
    cn.ConnectionTimeout = 15
    cn.Open , , , adAsyncConnect
    
    If cn.State = adStateConnecting Then
   Debug.Print "Still Connecting"
   End If

Me.Enabled = True

Private Sub cn_ConnectComplete(ByVal pError As ADODB.Error, adStatus _
   As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
   'It's safe to perform an operation on the connection object
   List1.AddItem "ConnectComplete event"
   Call LoadDataInControls
End Sub

Private Sub LoadDataInControls()
 
     Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM `mydb`ORDER BY `effective_date` DESC", cn, adOpenDynamic, adLockOptimistic
    
   Label1.Caption = rs.Fields("number")
   
   rs.Close
   cn.Close
     
    
End Sub

[si_the_geek]

I'm not sure which event a timeout fires - it may be _ConnectComplete (with adStatus and pError set appropriately), or it may be _Disconnect, or even _InfoMessage

I personally don't tend to use the events, I prefer to have a loop checking the .State, as that allows a 'please wait' animation of some sort.

[chris.cavage]

Yeah I was messing around with the code I had in my last thread all last night... I could get it to work ok, but it's not what I want just yet.

Can you please tell me about the loop to check the .state? It was recommended to me to show an animation like you suggested too. Do you have a sample of code that I can see how the loop would work in that instance?

Thanks.

I also called Verizon Internet Service... they said getting a static IP would solve my issue: DSL connection only (static IP) for $90 a month! :-)

Even though that would solve my issue, I still want to figure this out. Just to know...

[si_the_geek]

Instead of this:

    If cn.State = adStateConnecting Then
   Debug.Print "Still Connecting"
   End If

...something like this:

Dim lngLoop as Long
    Do 
      Label1.Caption = "Loading... " & Choose(lngLoop Mod 4 + 1, "|", "/", "-", "\")
      Sleep 300
      DoEvents
      lngLoop = lngLoop +1
    Loop While cn.State = adStateConnecting
    'check .State here to see if it is connected

The 'animation' is extremely simple, but is enough to let the user know that something is happening, and can easily be replaced.

To use Sleep you will need to add this to the Declarations section:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

[chris.cavage]

I see... ok thanks a lot. I can't wait to try this code tonight...

Can you please explain what the Sleep does in your code?

Thanks again.

[chris.cavage]

Sleep is just for the animation, I got it. I read a tutorial on Sleep.

Thanks.

[chris.cavage]

Here is my code for this (it is done on Form load):

Private Sub Form_Load()
Set cn = New ADODB.Connection
    cn.ConnectionString = "string"
    cn.CursorLocation = adUseClient
    cn.ConnectionTimeout = 30

    cn.Open , , , adAsyncConnect
    
    Dim lngLoop As Long
    Do While cn.State = adStateConnecting
      Label2.Caption = "Loading... " & Choose(lngLoop Mod 4 + 1, "|", "/", "-", "\")
      Sleep 300
      DoEvents
      lngLoop = lngLoop + 1
    Loop

End Sub
       
Private Sub cn_ConnectComplete(ByVal pError As ADODB.Error, adStatus _
   As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)

   Set rs = New ADODB.Recordset
   rs.Open "SELECT * FROM `mydb`ORDER BY `effective_date` DESC", cn, adOpenDynamic, adLockOptimistic
    
   Label2.Caption = rs.Fields("number")
   
   rs.Close
   cn.Close
End Sub

When I run my project, nothing loads for about 15 seconds and I get this error:

Run-time error '3709':
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
...after I select debug the line "rs.open...." is highlighted.

I intentionally removed my IP from the whitelist in order to build my connections around this. I was suprised to see that my form did not load right away with the adAsyncConnect

What am I doing wrong? Thanks.

I want the form to load right away, have label 2 say "loading" as the connection is established. Once connection is established call loaddataincontrols. If the connection times out then do nothing.

[isnoend07]

i have a couple routines that are similar that may help. I have a fading form that displays messages when emails with attachments are sent. With each email a message displays that the message was successful or not and message form disapears. after all the meassages are sent if successful a long save routine starts that contains the details of the email # ,to etc. the save routine is very lengthy and uses the same message form. I had to put code to wait until the emails were done to start the save. My fix was to put a global flag on the unload of the message form, eg; gMessageFormUnloaded as boolean. Then to stall the save routine:
Do until gMessageFormUnloaded = true
doevents
loop
I have another routine that works the same way for program updates. In sub main I check the version of an online text file. This also uses a do loop to stall until the check is made as to determine which form to load.
hope this helps

[si_the_geek]

Sleep is just for the animation, I got it. I read a tutorial on Sleep.

Partly, but in addition it stops your program from using the CPU when it doesn't need to.

Using just DoEvents in a loop is fairly CPU intensive, so you shouldn't do it unless you really need to.

When I run my project, nothing loads for about 15 seconds

It is loading, it just isn't becoming visible - because that only happens automatically at the end of Form_Load. To make it happen sooner, use: Me.Show

and I get this error:

Run-time error '3709':
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

There was a comment at the end of my sample that you were supposed to replace with code.

It should be a If statement which only allows the code you have got in _ConnectComplete (which can all be moved to Form_Load, after the loop) to run if the connection is valid.

[chris.cavage]

I didn't know that the form shows after it is completed loading... the Me.Show helped. I can see the visualization now of "Loading..."

I added an if statement after your loop in form_load

   
     Dim lngLoop As Long
    Do While cn.State = adStateConnecting

      Label2.Caption = "Loading... " & Choose(lngLoop Mod 4 + 1, "|", "/", "-", "\")
      Sleep 300
      DoEvents
      lngLoop = lngLoop + 1
    Loop

If cn.state = adStatusOK Then
Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM `mydb`ORDER BY `effective_date` DESC", cn, adOpenDynamic, adLockOptimistic    
    Call LoadDataInControls
    
    rs.Close
    cn.Close
    cmdReconnect.Visible = False
ElseIf cn.state = adStatusErrorsOccurred Then
Label2.Caption = "Not Connected to Server."
cmdReconnect.Visible = True
End If

However I do get the same error mentioned before and debug highlights the "Do While cn.State = adStateConnecting"

[si_the_geek]

I have no idea why I'm afraid, because it shouldn't be possible to get that error on that line - as the .State property should be available as long as the connection object is initialised (Set), if it wasn't then you would get a "..Not Set" error.

One issue I can see is that your If statements use the wrong values to compare against. Due to the way enums work it is entirely possible that adStatusOK has the same numeric value as adStateClosed (or one of the other adState* values), thus doing the wrong thing.

[chris.cavage]

interesting, thanks. whats more is that i can use the connectcomplete and put the if...then statement there and it works as it should.

I can do this but i want to use your looped animation. is there a way that I can end the loop with the loading text in the connectcomplete event?

also do certain servers allow the connectiontomeout property to work? When I try to use this property the default connection time of about 15 always takes precedence.

[si_the_geek]

That's odd, getting the error in one place should mean it occurs in the other too - the location of the code should not matter at all.

To get the animation to work with the event you may be able to just move the If statement etc to the event. If that doesn't do it, move the Label2.Caption line to a timer with an apt interval and remove the rest of the loop - instead enable the timer, and at the start of connectcomplete disable it.

ConnectionTimeout should work for all databases, but only during a connection attempt (cn.Open), not during other actions (where CommandTimeout is used).

[chris.cavage]

Thanks for the help.

I was thinking about a timer late last night, but I also tried another method to see if I could answer my own question.

I have a general declaration: Dim checkconnection As Boolean

When the connectcomplete happens, checkconnection = true.

What do you think of this? It works fine.

However, my connectiontimeout does NOT work. I have it set to 0 so it should continuously show the loading animation, but it does not. Sticks to the default timeout.

I want to still figure that out.

 Set cn = New ADODB.Connection
    cn.ConnectionString = "string"
    cn.ConnectionTimeout = 0
    cn.Open , , , adAsyncConnect
    
    checkconnection = False
      Dim lngLoop As Long
    Do
      Label2.Caption = "Loading... " & Choose(lngLoop Mod 4 + 1, "|", "/", "-", "\")
      Sleep 300
      DoEvents
      lngLoop = lngLoop + 1
    Loop Until checkconnection = True

At the end of my connectcomplete event, I also set cn=Nothing (I think I need to do that even if I get errors (the connection cannot open bc my IP is not whitelisted).

Thanks.

[si_the_geek]

If the connection fails setting cn=Nothing is the right thing to do. It is also the right thing to do after you have finished working with it (assuming it connected!), so if all of your work is inside that event then that is the place to do it.

I don't understand the point of the checkconnection variable, as you are checking exactly the same thing as before (ie: cn.State = adStateConnecting), just in a different way.

Note that whether or not you use the connectcomplete event, you should still check the .State is valid before using it (I think adStateOpen is the value you want).

[chris.cavage]

I was using the checkconnection variable because when I used this:

      Dim lngLoop As Long
    Do
      Label2.Caption = "Loading... " & Choose(lngLoop Mod 4 + 1, "|", "/", "-", "\")
      Sleep 300
      DoEvents
      lngLoop = lngLoop + 1
    Loop While cn.State = adStateConnecting

I got an error: Runtime error 91: Object variable or With block variable not set

...on line: Loop While cn.State = adStateConnecting

I dont know how to work around that properly.

That loop with adstateconnecting just wasn't working, so I wanted to get the same result, just in a different way.

The variable gave me a way to successfully end the Loading loop from connectcomplete event when the connection attempt was done.

[si_the_geek]

I can only assume that you still had code in the connectcomplete event, and that code set it to Nothing.

The code you had in post #18 (apart from the issue I mentioned in post #19) should do what you want without the connectcomplete event - I have used that kind of thing hundreds of times without problems.

[chris.cavage]

Here is what I just did:

I added my IP to the hosted server's whitelist. I originally removed it to work on this thread (to plan for errors and speed up my program).

I then used the code from #18 and it works just fine.

Seems like I get a load of issues if my IP is dropped from the server's whitelist.

Since I am planning for errors as a result of the dropped IP, I should use the variable checkconnection and the connectcomplete event (as they gave me no problems).

I am also assuming that the connectiontimeout property will work now because my IP address is now whitelisted with the server.

[si_the_geek]

.ConnectionTimeout is a maximum amount of time that ADO will keep trying, presumably there is a timeout on the server (or internet) side too, which would also be a limiting factor.

[chris.cavage]

Ok, thanks for the help with this long thread.

I have been testing the code (referencing thread #26) for some time now. It seems to be working fine (still working on the connection timeout, but it's not that big of an issue right now).

I will post back if anything changes on this thread. Thanks again for the help! I will mark this resolved as of now.

I learned a lot about adAsyncConnect in the process.