Connection not reconnecting after disconnection to DB

[FrankPedro]

Hi Guys

This one baffles me. I'm trying to make my app so that it auto reconnects after the connection has been lost. I have a timer that tries to execute a simple query such as "Select 1". If an exception is thrown I know the connection has been lost. I then handle the situation by trying to close the connection and reopening it. This will keep on going until I can successfully query the database again. The problem is this. When I step through the code when the reconnection is suppose to take place, it keeps on failing and says "login failed for user...". This is after the connection is actually active and I can query the database on the backend. Instead of closing and opening the same conneciton I've even tried to destroy the object and create a new one, with all the same connection properties as defined on the form. But still, when I open the connection it gives the same msg "Login failed for user...". I have made a test application where it actually works. So I don't know why in the actuall app it is holding the connection or something?

Here is the default properties of the connection:

procedure TfrmMainDataMod.ResetConnection;
begin
try
try
if FConnectionString = '' then
FConnectionString := DBCON.ConnectionString;
if assigned(DBCON) then
FreeAndNil(DBCON);

DBCON := TADOConnection.Create(self);
with DBCON do
begin
Close;
Connected := false;
CommandTimeout := 120;
ConnectionString := FConnectionString;
ConnectionTimeout := 120;
ConnectOptions := coConnectUnspecified;
CursorLocation := clUseClient;
IsolationLevel := ilReadUncommitted;
KeepConnection := true;
LoginPrompt := false;
Mode := cmRead;
Provider := 'SQLOLEDB.1';
tag := 0;
Name := 'DBCON';
DefaultDatabase := '';
Open; // this part does not connect successfully, not matter how many times it tries.
end;
except on e:Exception do
MessageDlg(e.message, mtError, [mbOK], 0); // Error msg: Login failed for user
end;
finally

end;
end;

It would be awesome if someone can direct me in the right direction.
Thank you!

 

[majlumbo]

Maybe you can check the Windows event log and see if there's any more information as to why the connection failed.

 

[FrankPedro]

I checked the applications, security and system events for any info. There is nothing there...

 

[MelodyRogueAngel]

Hi I'm not sure if it would work, but try it!

- Add a variabl (global)

-- Var
-- iTimer : Integer;

- Add a timer

-program for timer is as follows

--...
-- inc(iTimer);
-- when iTimer = 120
-- Do
-- Open;
--...

> procedure TfrmMainDataMod.ResetConnection;
> begin
> try
> try
> if FConnectionString = ''
> then
> FConnectionString := DBCON.ConnectionString;
> if assigned(DBCON)
> then
> FreeAndNil(DBCON);
> DBCON := TADOConnection.Create(self);
> with DBCON
> do begin
> Connected := false;
> CommandTimeout := 120;
> ConnectionString := FConnectionString;
> ConnectionTimeout := 120;
>-- //Close; // As in take it out
-- Timer.Active := True;
> ConnectOptions := coConnectUnspecified;
> CursorLocation := clUseClient;
> IsolationLevel := ilReadUncommitted;
> KeepConnection := true;
> LoginPrompt := false;
> Mode := cmRead;
> Provider := 'SQLOLEDB.1';
> tag := 0;
> Name := 'DBCON';
> DefaultDatabase := '';
>-- //Open; // I've sepecified it under the timer
>- end; // 'With' I presume
> except on e:Exception do
> MessageDlg(e.message, mtError, [mbOK], 0); // Error >
> msg: Login failed for user
> end;
> finally
> end;
> end;


Thats about it_

 

[MelodyRogueAngel]

Oh yeah, just check the >'Open';
just specify what should open I didn't do that.