Solved

ADO connection

Posted on 2002-03-12
6
468 Views
Last Modified: 2013-11-23
I want to connect to an Access database at runtime using ADO. I would like to specify the path of the database, say in a text box and then try to connect to it. If the path specified is invalid,I want to show the message in a message dialog box instead of having the system display the error. What I am having is that if I specify a wrong database path, my application just hangs just after displaying the error. How can I trap the error and give the user a meaningfull message.
0
Comment
Question by:gi7mhl
6 Comments
 
LVL 7

Accepted Solution

by:
Motaz earned 100 total points
Comment Utility
var
  Dir: string;
begin
  Dir:= 'd:\database\first.mdb';
  AdoConnection1.ConnectionString:=
    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +
   Dir + ';Persist Security Info=False';
  try
     AdoConnection1.Connected:= True;
  except
  on e: exception do
  begin
     ShowMessage('Unable to connect: ' + E.Message);
  end;
  end; // try
end;

0
 
LVL 7

Expert Comment

by:Motaz
Comment Utility
This article about Exception handling from my Delphi Programming Guide book (www.geocities.com/motaz1)

----------------------------

Exception handling is one of the most important programing tools features. If the tool that we are using to develop applications does not provide any mean of handling errors that occurs at run time, then we can not rely on this tool to build robust applications.

There are two types of errors: Compile time error, such as "Type mismatch", "unknown identifier", etc. This type of errors prevent the compiler to resume the compilation procedure and producing the executable file, example:

var
  Name: string;
begin
  Name:= 12; // Type mismatch
end;

The second type of errors is Run-time error. In this type, the program compiled and run succussfully, but unexpected user input or any unexpected event may crash the application execution such as "divistion by zero". Example:

var
  i, x: Integer;
begin
  i:= StrToInt(Edit1.Text);
  X:= 5 div i;
  Label1.Caption:= IntToStr(X);
end;

The previous example compiles succesfully, but when the user enter 0 in the edit box, you will get "division by zero error" and the rest code of procedure will not execute.

Another example is I/O erros. Some times we tries to open files that does not exist, or the disk that we write into is full, or we attempt to read from file but end of file is reached, or any other I/O error.

In all the previous cases, Delphi has the same default exception handling to treat all run-time erros. If we forget to handle any exception, Delphi will use it's default handling. The default exception handing is:

1. Display the proper error message, as the attached picture.

2. Exit from the current procedure or function, for example if Division by zero occured at the previous example the next line will not be executed : Label1.Caption:= IntToStr(X);

This handling is not enough in most of programming cases, for example, suppose that we have a memo, and a text file. We want to read from this file and append it's contents to the memo. Befor this operation we hide the memo for fast reading, and we display it again after finishing. The code is:

var
  F: TextFile;
  Line: string;
begin
  Memo1.Visible:= False;
  Memo1.Clear;
  AssignFile(F, 'Data.txt');
  Reset(F);
  while not Eof(F) do
  begin
    Readln(F, Line);
    Memo1.Lines.Add(Line);
  end;
  CloseFile(F);
  Memo1.Visible:= True;
end;

Suppose that the file 'Data.txt' does not exist or an error occurs while reading from file. Delphi will rais I/O error. Because we did not add our own exception handling, Delphi will handle the exception for us, and it will rais the error "File not found" or "Error while reading file" and will exit the  procedure after the statement Reset(F); if the file does not exists or after Readln(F, Line);
statement if an error occurs while reading, also the rest of procedure statements will not be executed. The most important statements on the rest of code are:

  CloseFile(F);
  Memo1.Visible:= True;

The result will be: leaving the file in use at the case of second error and the memo will be invisible. This will be a strange behaviour of our application.


try .. finally block:

Try Finally block is ued to ensure that the finally block will execute. In finally block you can free resources, close tables and files, etc. The statements that exist in finally block will be executed in normal cases and in error cases. The structure of try finally is:

try
  Statement(s);
finally
  Statement(s);
end;

Example:

var
  F: TextFile;
  Line: string;
begin
  try
    Memo1.Visible:= False;
    Memo1.Clear;
    AssignFile(F, 'Data.txt');
    Reset(F);
    while not Eof(F) do
    begin
      Readln(F, Line);
      Memo1.Lines.Add(Line);
    end;
    CloseFile(F);
  finally
    Memo1.Visible:= True;
  end;
end;

The application will ensure the execution of Memo1.Visible:= True statement in all conditions.

Another example:

try
  Table1.Open;
  Label1.Caption:= Table1.FieldByName('Name').AsString;    
  // Any other Table1 manipulations
finally
   Table1.Close;
end;
   


Try .. Except block:

Try Except block is used when you need to execute certain statements if a run-time error is occured. The structure of try except is:

try
  Statement1(s);
except
  Statement2(s);
end;

If an error occure in Statement1(s), the execution will jump to except block (Statement2(s)).  Example:

var
  i, x: Integer;
begin
  try
  i:= StrToInt(Edit1.Text);
  X:= 5 div i;
  Label1.Caption:= IntToStr(X);
  except
     ShowMessage('Invalid data entry');
    Edit1.Clear;
  end; // try
  Edit2.Text:= Edit1.Text;
end;

In this case instead of showing the default error message, you can show your own message and execute another statements, such as clearing the text box that contains the wrong entry such as 0 or a letter. Except block statements will be executed only if an error occured. In normal cases the execution will not reach this block.

Another important thing is that when an error occures and you handle it with except block, the execution will resume normally after try except block or try finally block. In the previous example Edit2.Text:= Edit1.Text; will be executed.


on E: Exception:

When a run-time error occures, an instance of Exception class will be raised, this instance contains information about the error such as error message. Example:

var
  i, x: Integer;
begin
  try
  i:= StrToInt(Edit1.Text);
  X:= 5 div i;
  Label1.Caption:= IntToStr(X);
  except
  on E: Exception do
   begin
     ShowMessage('Invalid data entry: ' + E.Message);
    Edit1.Clear;
   end; // on E:
  end; // try
  Edit2.Text:= Edit1.Text;
end;

Exception class is a generic exception class, and you can be more specific on handling errors, for example EInOutError is a descendent of Exception class, and it can be used only with I/O Error. Example:

var
  F: TextFile;
  Line: string;
begin
  try
    Memo1.Visible:= False;
    Memo1.Clear;
    AssignFile(F, 'Data.txt');
    Reset(F);
    while not Eof(F) do
    begin
      Readln(F, Line);
      Memo1.Lines.Add(Line);
    end;
    CloseFile(F);
  except
  on E: EInOutError do
    begin
      ShowMessage(E.Message + #10 +
       'Error code: ' + IntToStr(E.ErrorCode));
    end; // on E:
  end; // try
  Memo1.Visible:= True;
end;

Note that ErrorCode property does not exist in Exception class.


Nested Try:

Try .. finally and Try except blocks can be nested. If an error occures in certain position of our code, the execution will jump to the nearest or the current try .. finally, or try .. except block. Example:

try
  A;
  B;
  try
    C;
  except
    D;
  end;
  E;
except
  F;
end;
 
If an error occures in A, B or E, the execution will jump to F. If an error occures in C, the execution will jump to D, then it will resume to E:


Notes:

-If you want to test try .. except or try .. finally blocks, it is recommended that you run your application separately of Delphi such as command prompt or from Start/Run, because if you run it from the debugger (Dephi IDE), the debugger will show it's message and will pause program execution, but you can press F9 to execute your handling block and resume your application.

- In the applications that run alone without user interference, services, web appliations, or any other application that does not require a user to deal with it directly using it's interface, you should not display any message, instead you can write this error message and time of error in a log file or send it via E-Mail, because if you display this message, no one will know that there is an error occures and no one can will click "Ok". Moreover the large number of error message boxes will overload the memory and resources. Also you must not rely on default exception handling, and you should add try .. finally, try .. except blocks whenever you expect run-time erros of these types of applications.
-------------------------
0
 
LVL 7

Expert Comment

by:Motaz
Comment Utility
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:inthe
Comment Utility
or check the path before you connect :

if fileexists(filepath) then
// connect procedure
else
showmessage('invalid path - no file exists');
0
 

Expert Comment

by:GStyler
Comment Utility
Or just check the directory with "DirectoryExists"
0
 

Expert Comment

by:CleanupPing
Comment Utility
gi7mhl:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now