How do I reconnect ADOConnection or ADODataset after connection failure

How do I reconnect ADOConnection or ADODataset after I get EOleException with Message '[Microsoft] [ODBC SQL Server Driver] Communication link failure'

try
  ADOConnection.open;
except
  ADOConnection.Close;
end;

When I reconnect, The message be rompted constanly. I have to shutdown the application completly and and start again.

erwingosepaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xr1140Commented:
You receive that error message because the connection settings are not correctly defined, so before you  reconnect try resetting those settings.
0
calinutzCommented:
Do you have a network problem by any chance?
I mean... your application connects to the database and sometimes you receive this error message while being connected?
Or what exactly happens?
Try
ADOCOnnection.Connected:=false;
ADOCOnnection.ConnectionString:='here place your connection string';
ADOCOnnection.Connected:=true;

Regards

0
erwingosepaAuthor Commented:
What settings are you refering to the ODBC setting? Please provide how the setting should be. or example of the settings. xr1140
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

erwingosepaAuthor Commented:
calinutz, my problem is to restore when the connection is broken. The network problem is initialted by myself. I wanto to restore the connection after the failure but the network connection is up but it error message is keep indicating communication failure.
0
xr1140Commented:

When the ODBC application calls SQLDisconnect the first time, the connection is saved to the pool. Any subsequent SQLConnect/SQLDisconnect that matches the required condition will reuse the first connection. Connection remains in the pool depends on the timeout property of the ODBC driver. The default timeout is 60sec.


restore the network and try to reconnect after 60 sec or destroy your ConnectionObject and try again
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikelittlewoodCommented:
How about putting a timer object onto the form and set it off when you get a disconnection error.
Let it try try and reconnect every 5 seconds or something for a maximum number of trys.
0
erwingosepaAuthor Commented:
can give me workable example in delphi?
0
mikelittlewoodCommented:
Maybe I'm leading you down the wrong path but when you get an unwanted disconnect message, set the timer off to try and reconnect. If it fails to reconnect, set the timer off again. Set the timer to 5 secs or something

procedure TForm1.ADOConnection1Disconnect( Connection: TADOConnection;
  var EventStatus: TEventStatus);
begin
   if eventStatus in [esUnwantedEvent] then
      timer1.Enabled := true
end;

procedure TForm1.Timer1Timer(Sender: TObject);
begin
   timer1.Enabled := false;
   try
      ADOConnection1.Connected := true
   except
      timer1.Enabled := true
   end;
end;
0
erwingosepaAuthor Commented:
mikelittlewood:

I still get this error "EOleException with Message '[Microsoft] [ODBC SQL Server Driver] Communication link failure'    ' on ADODataset which is connected to ADOConnection.
0
xr1140Commented:

try to deactivate the connection pooling. Navigate to \HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\
driver-name\ and set the CPTimeout to a lower value (the value is in seconds)
0
mikelittlewoodCommented:
hmm it must be holding something in the ADOConnection somewhere.
Instead of having the ADOConnection object on your form at design time, have you tried creating the ADOconnection object at runtime instead, freeing it when you get the problem, then recreating the entire connection object instead?
0
xr1140Commented:

i`m sure it has to do your connection pooling feature or the ODBC driver. i think your connection string is wrong and cause that error to appear ... and because connection is kept in the pool every attempt after that has the same fate because the ODBC driver will reuse the existing connection. Therefor i suggest :

(first and foremost make sure your connection string is formed correctly then)

1. create the ADO connection at run-time and destroy/recreate when the error occur
 
or

2. disable the connection pooling feature as i`ve explained in my previous post.

or

3. try to release/recreate the ADOConnection.ConnectionObject
0
se_workCommented:
Hi,

did you try to catch the error?
the try.. except ... end block doesn't trap the error.
you have to deal with; like displaying the error. in the except part.

if not the error message will be displayed by the caller of the procedure ...

Something like

  try
    ADOQuery1.open;
  except
    on E: EOLEException do
    begin
      result := false;
      showmessage ('DB Connection Error.'+E.message);
    end
  end;

May be it will help you to reconnect...
If you want no interaction, just display the E.message anywhere on your form...

best
0
Steven-FernandezCommented:
Try this:

1.

Try

//Connection code here...

except

//Connection code here again

end;

Just an idea!


0
goldengamingCommented:
I am having the same issue. I am forcing the error as well. I started off using a BDE Stored Procedure and it tells me a similar error. I tried the ADO thinking I'd be better off seeing that there are connection options for me compared to using the storedproc from the BDE component. Has anyone come up with a final solution to this issue.
0
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.