Solved

ADO connection

Posted on 2002-03-12
7
440 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
7 Comments
 
LVL 1

Expert Comment

by:drnadeem
ID: 6856171
Here is a way you can run ADO connection dialog box the way delphi does.

Login.ConnectionString := adodb.PromptDataSource(Handle, '');

after this you can store the string in the registry so that subsequent connections access the registry and locate the connection string. This will avoid errors and the need to trap them.

hope this helps
0
 
LVL 1

Expert Comment

by:cobramania
ID: 6856927
why don't you try using opendialog instead of edit box ?

hope you got the idea :)
0
 
LVL 6

Expert Comment

by:wimmeyvaert
ID: 6859794
A variant of drnadeem

Create as much UDL-Files as needed (Design time).


From within your application call :
  ADOConnection1.ConnectionString := adodb.PromptDataLinkFile(Handle, 'X:\somedir\somefile.udl');


With this function you can open an existing UDL-File.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:mgesiot
ID: 6860003
Hi.
The point is if you must choose the position of the Access files once or different times.
If it's only once use UDL files.
they are simple to use (even your client can check them)
and they work.

But if you want to change different times the database to use then the simplest thing to do is :

1) An open file dialog box.
2) A Recent Open Files Menu (use a string list.. simple as that)
3) A RunTime built Connection String.

Numoer 3 could look a bit tricky, but all you have to change is the name of the file, so copy a pre-prepared string and change the nema of the file you recive from
the dialog.
Just remember to set the dialog to open only exsisting files and with the right extension and use a TRY...FINALLY block when opening in case the file is corrupted or is in the wrong format.

Hope it helps
Marco

0
 
LVL 44

Expert Comment

by:CrazyOne
ID: 6862294
Try varifying the file is there

if not FileExists(text1.text) then
  ShowMessage('Nope')
else
  OpenYourDataBase;
0
 
LVL 1

Expert Comment

by:Computer101
ID: 6863085
For reasons stated below, proposed answer rejected.

Thank you
Computer101
Community Support Moderator


Comments
Comments are intended to be used as a collaboration tool. Many Experts choose to post their solutions as comments only.

Answers
An answer is a specific solution to a question and should be submitted if it will solve the questioner's problem and doesn't duplicate a previous comment.

Comment Vs. Answer
If you are unsure of your solution, post it as a comment. Members can accept comments as solutions and award you Expert Points for them.

For more tips on comments and answers, click here.

0
 
LVL 2

Accepted Solution

by:
gallaghe earned 100 total points
ID: 6864676
Here is another idea (beating the dog to death).

Create a constant for your connection string as shown here
const
  ConnectStr:WideString =
  'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'Password="";' +
  'User ID=Admin;' +
  'Data Source=%s;' +
  'Mode=Share Deny None;' +
  'Extended Properties="";' +
  'Jet OLEDB:System database="";' +
  'Jet OLEDB:Registry Path="";' +
  'Jet OLEDB:Database Password="";' +
  'Jet OLEDB:Engine Type=4;' +
  'Jet OLEDB:Database Locking Mode=0;' +
  'Jet OLEDB:Global Partial Bulk Ops=2;' +
  'Jet OLEDB:Global Bulk Transactions=1;' +
  'Jet OLEDB:New Database Password="";' +
  'Jet OLEDB:Create System Database=False;' +
  'Jet OLEDB:Encrypt Database=False;' +
  'Jet OLEDB:Don''t Copy Locale on Compact=False;' +
  'Jet OLEDB:Compact Without Replica Repair=False;' +
  'Jet OLEDB:SFP=False' ;

notice that there is a '%s' for the "Data Source". You would replace that with the path + name of the database at runtime i.e.

ADOConnection1.ConnectionString :=
  Format(ConnectStr,[Edit1.Text]) ;

{ Interesting note on the following code, if you cancel
  operation in WillConnect event ADO still rears it's
  ugly error message. So to suppress it we mus wrap it
  in the Try/Except. In Visual Basic 6 ADO does not
  impose on you. I am to lazy to read the Delphi source
  to see why this is so, just assume there is a
  "Raise" error if we set the [EventStatus] }
try
  ADOConnection1.Connected := True ;
except
  { See OnWillConnect event }
  exit;
end ;

procedure TForm1.ADOConnection1WillConnect(....
begin
  if not FileExists(Edit1.Text) then
  begin
    ShowMessage('Failed to locate the file' + #13 +
      Edit1.Text + #13 + 'Try later') ;
    EventStatus := esCancel ;
  end ;
end;

Yep this could also be done w/o the WillConnect;

if FileExists(Edit1.Text) then
  { Do the connection }
else
  { Error message and recovery goes here }
...


To me it makes perfect sense to (if there are more then one database choices) programmically code methods to display only valid databases to select from. For instance as someone else mentioned an open dialog component or populating a ComboBox or ListBox with valid selections.

I would steer clear of using <PromptDataSource> for novice users, only use for seasoned PC users. Remember it is our job to make life easy for the users.


Hope this is of some use to you.
Kevin S. Gallagher
Project JEDI http://www.delphi-jedi.org/
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

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

17 Experts available now in Live!

Get 1:1 Help Now