How to test ADOConnection ConnectionString before connecting to SQL Server database in Delphi 10.2

Question

I am working on an application on Delphi 10.2, And it connects to a SQL Server 2014 database. What can I do to test the connection with the database having the connection string before the application starts?

I have used ADoconnection as an interface to connect to the aforementioned database, did some coding with try-catch or try-except to rule out the unwanted SQL Server errors, and using a gauge Bar to indicate the advancement of my start procedure (which progresses with query activation and form creation).

So when the connection string is not ok, I will get an error of

Login failed for user 'admin98'

(admin98 is the name of the SQL Server user); and when the connection string is ok, the gauge bar progresses and in the halfway through I have again the same error.

NOTE: I used freeInstance or NewInstance or other things like that but it didn't work.

This is the function that connects to the database and catches errors if encounters

function DBConnect: Boolean;
var
 conStr : string;
begin
 conStr:= 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=admin'+Fstart_.year_combobox.Text+';PassWord=000;Initial Catalog=student'+Fstart_.year_combobox.Text+';Data Source='+Fstart_.StringTemp+';';
 DataModule1.ADOConnection1.Close;
 DataModule1.ADOConnection1.ConnectionString:= conStr;
 DataModule1.ADOConnection1.LoginPrompt:= False;
 if (NOT DataModule1.ADOConnection1.Connected) then
  begin
   try
    DataModule1.ADOConnection1.Open;
    Result:= True;
   Except on E:Exception do
    begin
    if e.Message = 'Login failed for user '+chr(39)+'admin'+Fstart_.year_combobox.Text+chr(39) then
     //showmessage
    if e.Message = '[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied' then
     //showmessage
     DataModule1.ADOConnection1.Close;
     DataModule1.ADOConnection1.ConnectionString:= '';
     Result:= False;
    end;
   end;
  end;
end;

The thing is that it has to work and I shouldn't restart the application so I need to either test before connection or reset the whole connection.

I expect the successful connection but I get the error again even when I change the combo box value and I know that the user exists in SQL Server

Answer 1

This Answer worked for me and I found out that I need 5 seconds of sleep to get the thread out of RAM

function DBConnect: Boolean;
var
 conStr : string;
begin
 ini:= TMemIniFile.Create(GetCurrentDir + '\Settings.ini');
 Fstart_.StringTemp:= ini.ReadString('Server Directory', 'Server Name', KeyFileString);
 Application.CreateForm(TDataModule1, DataModule1);
 DataModule1.ADOConnection1.Close;
 conStr:= 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=admin'+Fstart_.year_combobox.Text+';PassWord=123;Initial Catalog=darman'+Fstart_.year_combobox.Text+';Data Source='+Fstart_.StringTemp+';';
 DataModule1.ADOConnection1.ConnectionString:= conStr;
 DataModule1.ADOConnection1.LoginPrompt:= False;
 try
  DataModule1.ADOConnection1.Open;
  Result:= True;
 Except on E:Exception do
  begin
   if e.Message = 'Login failed for user '+chr(39)+'admin'+Fstart_.year_combobox.Text+chr(39) then
     // Showmessage 
    [mbOK],0);
   if e.Message = '[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied' then
    // ShowMessage
   DataModule1.ADOConnection1.Close;
   DataModule1.Destroy;
   Sleep(5000);
  end;
 end;
end;