[RESOLVED] connection with MYSQL server sometimes drops, how to error trap this?

Hello all,

I make a connection with a mysql server and open some recordsets based on its database. Everything works fine excepts that sometimes I need to prompt the user for some input. If the user answers the question posed by my pop up box within a reasnable time frame (less then a minute) the transaction goes fine and the database updates properly.

The problem arises if the user goes away from the keyboard for lets say 5 minutes once the pop up box prompts them. Then once they return and enter the information asked of them they get a "lost connection to mysql server" message....It seems that I only have a brief period of time for my connection object to be open otherwise it drops.

Any one have any idea how to fix this?

I tried playing around with the
cn.ConnectionTimeout
and
cn.CommandTimeout

properties but without much luck...

thanks


Language used :VB6
Connection method:ADODB
Provider:ODBC
Remote Database: MySQL

Unfortunately the ConnectionTimeout only affects opening the connection in the first place, and CommandTimeout only affects the .Execute method (of the Connection or Command).

One way to solve this is to add a check to the top of the error handler - if the error is this one, simply run "cn.Open" (which I think should re-connect using the same details), followed by "resume" which will re-run the line of code that caused the error.

thanks for the tip, never knew about the "resume" handler in error trapping, good to know this.

The only problem with running cn.open again after its been closed is that all my recordeset data is gone too. so i have to reopen that as well , a bit of a hassle but if this is the way to do it then i guess i have no choice

thanks for the tips, appriciete it!

No problem :)

If you want extra info about error handlers see the first article in the "Dealing with errors" section of our Classic VB FAQs (link in my signature), which contains explanations and examples (including one similar to this kind of thing).


Oh, and as you have the answer, could you please do us a little favour, and mark this thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".

ok sure, thanks for those FAQ links, lots of good info in there, havent seen them before.