create connectionstring at runtime
Thomas Willms
Hello,
I know quite well connect to a databank but only at designtime...
Unfortunately, now, I need to create the connection settings and strings at runtime:
first, a simple connection to a mdb file using a given path X
and alternatively a connectionstring to a server Y with known password, username, server name and port number.
The letter seems to be much more complicated..
I want to use the same adoconnection and change the parameters in case that Internet is available or not.
I already tried it but it doesn't work.
Also, in the internet-connectionstring there is an option something like "Don't copy ....". As <'> is a separator for strings: how to make the string in this case?
Do I have to set all attributes separatedly of the connection or is just the connectionstring sufficient.
Can someone post an example for each code : mdb databank and server databank ?
Thanks very much
Thomas Willms
Niklas Larsson
"Thomas Willms" <th.w...@wanadoo.fr> wrote in message
news:477feb05$1...@newsgroups.borland.com...
>
> Hello,
>
> I know quite well connect to a databank but only at designtime...
> Unfortunately, now, I need to create the connection settings and strings
> at runtime:
> first, a simple connection to a mdb file using a given path X
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=' + X + ';Persist Security Info=False';
> and alternatively a connectionstring to a server Y with known password,
> username, server name and port number.
> The letter seems to be much more complicated..
>
Yes! And again it depends on the database. Simple access the database you
want to access at designtime and copy the connectionsting and replace
information as servernamne, username and so on with variables from your
application, as in example above.
As an alternative you can let the enduser log in to the database by creating
the connectionstring at runtime:
var
CS : WideString;
begin
CS := PromptDataSource(Handle, CS);
ADOConnection.ConnectionString := CS;
ADOConnection.Open;
Niklas Larsson
Virtual Carisma
Thomas Willms
Hello,
Thanks very much. So as simple as I thought. But now I know why it didn't work before. I didn't understood that I have to put adoconnection as property in adotable , adoquery etc. and only to define the connectionstring for the adoconnection....
How can I prevent the program from showing the boxes to put in username or password for adoconnections if anyway there is none ?
Thomas Willms
Niklas Larsson
>
>I didn't understood that I have to put adoconnection as property in
>adotable , adoquery etc. and only to define the connectionstring for the
>adoconnection....
You don't really need an TADOConnection. You can set the connectionstring
directly to the TADOTable or TADOQuery component in the same way. But if you
have several components it's better to let them share the same connection
using an TADOConnection component. This component also have same nice
feature as filling the DB tabales and fields in different kind of Lists.
> How can I prevent the program from showing the boxes to put in username or
> password for adoconnections if anyway there is none ?
>
Set the LoginPrompt-property to "False" or do it by code:
ADOConnection1.LoginPrompt := False;
Regards!
Niklas Larsson
Virtual Carisma
Thomas Willms
Thanks very very much !
It is incredible everything worked all the time but today
I get an error I never had before:
It is impossible to connect to the databank although I didn't change anything but the delphi code. The error:
'databank format not recogniced :' +..it follows the path of the mdb file.
Did something happen what I should know ?
Thomas Willms
Thomas Willms
Hello,
In fact the only real difference is the fact that I used
shfolder in USES.(but even if I make a comment of it the error is still present)
I get an error 'error initializing the provider'
besides error 'database format ...mdb not recogniced' !
Even manually connection is not possible.
Any idea what I can do ?
Thomas Willms
Thomas Willms
I found the problem.
If I write the local path it works:
So if true, then "C:\files\file123.mdb" works
but this is nonesense for a universal use
If I put ".\file123.mdb"
it works sometimes but sometimes not and today it didn't work .
"initialisiation error" for the mdb file although the path is correct ...
I don't understand this problem ...
Thomas Willms
Del Murray
where the database resides.
Put the complete connection string into a .txt file and put the txt file
into the same subdirectory as your program.
You can get the path of the program from system.params(0) and use that to
read in the txt file which contains the complete connection string.
Niklas Larsson
"Del Murray" <Del.M...@CreditHawk.Net> wrote in message
news:4782...@newsgroups.borland.com...
Or maybe consider store the connectionsstring in the windows register. It
should at least be a bit more secure then store this kind of information in
a flat file. There is a lot of examples on the internet how to use the
windows register.
Then use my previous example: CS := PromptDataSource(Handle, CS);
to let the enduser write the CS to the windows register.
Niklas Larsson
Virtual Carisma
Thomas Willms
Hello,
I think the datasourceprompt might be really a good Idea but I would like to understand why attributing the connectionstring at runtime doesn't work.
Everytime I do it with
(adoconnection1.connectionstring := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; Data Source='+
writepath + '\Theredaprivate.mdb;Mode=ReadWrite;Extended Properties="";Persist Security Info=False';)
(writepath being the path string of the file theredaprivate.mdb)
, Delphi tells me that 'XXXXXXX\file.mdb' with XXXXX being the path on the disk, is not a valid database formate, also some initialisation error of the provider is indicated. After this, it is often not possible to connect to the database, even manually and deleting everything before a new trial....
Last time I was forced to take my old version and begin at zero..
Do you know this error ? Is it reasonable to put the path in quotation marks "" ?
Thomas Willms
Thomas Willms
Having explained why I have problems to attribut the connectionstring argument, I tried the databaseprompt as given
cs := Promptdatabase(Handle, cs);
after introducing an error because I never know where my databases will be searched after installation (it is searced somewhere in Document settings but depending on system/ profile etc.) and how to put them there. Like this there will be the error 'database not found at XXXX' and I will let the user write down the path (anyway he shall know where they are) and so he can enter it by the dataprompt but it doesn't work...
Still better would be to get the path myself...
How can I let appear the search path (the error or the handled error ?) and have the databaseprompt. Is it the error which prevents the databaseprompt from appearing ?
Do I need "try except" with an "on raise error event" ?
which error is this ?
I don't know anything about this ....
If I get the path given by the error I would put it in a string and copy the database files to this place....if I were able to construct at runtime the connectionstring and attribute it (see message posted before) I would't need any user entry ..which is my ultimate target...
to much "if's" ....
Thanks very much
Thomas Willms
Niklas Larsson
>
> Hello,
> I think the datasourceprompt might be really a good Idea but I would like
> to understand why attributing the connectionstring at runtime doesn't
> work.
> Everytime I do it with
>
> (adoconnection1.connectionstring := 'Provider=Microsoft.Jet.OLEDB.4.0;User
> ID=Admin; Data Source='+
> writepath + '\Theredaprivate.mdb;Mode=ReadWrite;Extended
> Properties="";Persist Security Info=False';)
>
> (writepath being the path string of the file theredaprivate.mdb)
>
> , Delphi tells me that 'XXXXXXX\file.mdb' with XXXXX being the path on the
> disk, is not a valid database formate, also some initialisation error of
> the provider is indicated. After this, it is often not possible to connect
> to the database, even manually and deleting everything before a new
> trial....
> Last time I was forced to take my old version and begin at zero..
>
> Do you know this error ? Is it reasonable to put the path in quotation
> marks "" ?
>
> Thomas Willms
>
Try the following CS:
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Data Source=' + ExtractFileDir(Application.ExeName) +
'\Theredaprivate.mdb;Mode=Share Deny None;Extended Properties=""';
It takes the path from the exe file.
Works fine for me!
Niklas Larsson
Virtual Carisma
MikeR
> Hello,
> I think the datasourceprompt might be really a good Idea but I would like to understand why attributing the connectionstring at runtime doesn't work.
> Everytime I do it with
>
> (adoconnection1.connectionstring := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; Data Source='+
> writepath + '\Theredaprivate.mdb;Mode=ReadWrite;Extended Properties="";Persist Security Info=False';)
>
> (writepath being the path string of the file theredaprivate.mdb)
>
> , Delphi tells me that 'XXXXXXX\file.mdb' with XXXXX being the path on the disk, is
not a valid database formate, also some initialisation error of the provider is
indicated.
After this, it is often not possible to connect to the database, even manually and
deleting everything before a new trial....
> Last time I was forced to take my old version and begin at zero..
>
> Do you know this error ? Is it reasonable to put the path in quotation marks "" ?
>
> Thomas Willms
Thomas -
Instead of re-typing some of the code and error messages, please always copy and
paste them. Please paste the error message and your code into your reply instead of
typing xxxxxxxx.
Are you using the code I gave you in your other thread? What is the value of writepath?
You are giving two different names for your mdb, 'Theredaprivate.mdb' and 'file.mdb'.
We have no way to determine from your post which is correct.
Mike
Del Murray
object, when the object is created it may be trying to connect to the
database when the form is created if "connected" is set to true and saved
that way.
be sure to set "connected" to "false" before you compile your program.
Thomas Willms
Hello,
I used the code below which didn't work the day I wrote you this post. But to give all details I tried to reproduce it and today it worked !!!!!!
Any idea what causes this provider initialisation error ????
which gives the error :
'D:\Gemeinsame Documente\TED\Thereda.mdb'
not recogniced databank format ?????
even manually the database was not accessable with the normal connectionstring construction....
Thanks so much....
Thomas
procedure mdbfilelocation(verzE,WRITEpATH1 : STRING);
var filesource,filedest : string;
begin
fileSource := verze + '\Thereda.mdb';fileDest := WritePath1 +'\'+ 'Thereda.mdb';
CopyFile(PChar(fileSource), PChar(fileDest), False);
//try DeleteFile(fileSource); finally end;
fileSource := verze + '\Theredaprivate.mdb';fileDest := WritePath1 +'\'+ 'Theredaprivate.mdb';
CopyFile(PChar(fileSource), PChar(fileDest), False);
//try DeleteFile(fileSource); finally end;
fileSource := verze + '\KopieTheredapriv.mdb';fileDest := WritePath1 + '\'+ 'KopieTheredapriv.mdb';
CopyFile(PChar(fileSource), PChar(fileDest), False);
//try DeleteFile(fileSource); finally end;
end;
procedure ADOConnectTest(cst1,cst2,cst3 : widestring;resultC : boolean);
begin
resultC :=true;
try Form1.adoConnection1.connected := false;Form1.adoConnection1.connectionstring := cst1;
Form1.adoConnection1.connected :=true;
Form1.ADOConnection2.GetTableNames(Form1.ListBox3.Items, False);
Form1.adoConnection2.connected := false;Form1.adoConnection2.connectionstring := cst2;
Form1.adoConnection2.connected :=true; Form1.adoConnection3.connected := false;
Form1.adoConnection3.connectionstring := cst3;
Form1.adoConnection3.connected :=true;
except resultC :=false; end;
end;
procedure TForm1.FormCreate(Sender: TObject);
var Dateiname,error1,m ,w,writepath,filesource,filedest : string; cs1,cs2,cs3: widestring;
option1,i,Feldnamenzahl : integer;ok : boolean;
begin
Sendergruppe := 'Nichts'; IDZahl:=0; IDAnfang:=0;
TabbedNB1.PageIndex := 0; Listbox2.Items.Clear;Listbox3.Items.Clear;
verz := extractFilepath(ParamStr(0));Form1.ListBox3.Clear;
WritePath := GetFolder( CSIDL_COMMON_DOCUMENTS, True);
mdbfilelocation(verz,WRITEpATH);
cs1 := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+
writepath + '\Thereda.mdb;Mode=ReadWrite;Extended Properties="";Persist Security Info=False';
cs2 := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+
writepath + '\Theredaprivate.mdb;Mode=ReadWrite;Extended Properties="";Persist Security Info=False';
cs3 := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+
writepath + '\KopieTheredapriv.mdb;Mode=ReadWrite;Extended Properties="";Persist Security Info=False';
//Showmessage('Bitte notieren Sie sich das folgende Verzeichnis in dem die Thereda-Datenbankdateien gesucht werden !');
ADOConnectTest(cs1,cs2,cs3,ok);
if OK = true then
begin
Showmessage('Die Thereda-Datenbankdateien befinden sich in: ' + writepath);
end //WritePath := '.\Theredaprivate.mdb'; //Showmessage('Die Thereda-Datenbankdateien befinden sich in: ' + writepath);
else begin
//writepath := inputbox('Geben Sie den notierten Pfad ein: ','Datenbank','');
mdbfilelocation(verz,WRITEpATH);
cs1 := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+
writepath + '\Thereda.mdb;Mode=ReadWrite;Extended Properties="";Persist Security Info=False';
cs2 := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+
writepath + '\Theredaprivate.mdb;Mode=ReadWrite;Extended Properties="";Persist Security Info=False';
cs3 := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source='+
writepath + '\KopieTheredapriv.mdb;Mode=ReadWrite;Extended Properties="";Persist Security Info=False';
ADOConnectTest(cs1,cs2,cs3,ok);
end;
end;
MikeR
> Hello,
>
> Any idea what causes this provider initialisation error ????
> which gives the error :
> 'D:\Gemeinsame Documente\TED\Thereda.mdb'
> not recogniced databank format ?????
>
> even manually the database was not accessable with the normal connectionstring construction....
Can you open the .mdb with Access after it is installed, and copied?
3 possibilities:
1 - The database isn't where the program is looking for it.
or
2- There is a permission issue on the folder or on the database itself.
or
3 - The database is corrupt. Can you open it in Access both before you make the
install and after it's installed?
Is the install being run by the same user as the program?
With the same permissions?
Put a 'If fileexists()' here and see if it passes.
Thomas Willms
Hello,
>> Any idea what causes this provider initialisation error ????
>> which gives the error :
>> 'D:\Gemeinsame Documente\TED\Thereda.mdb'
>> not recogniced databank format ?????
>> even manually the database was not accessable with the normal connectionstring construction
>I don't understand what you mean by this.
....of course in Delphi just building the connectionstring manually and testing the connection
>Can you open the .mdb with Access after it is installed, and copied?> Yes, no problem at all. Only with Delphi by the adoconnection I cannot connect to it : More precisely: when I try to built the connectionstring for ADoTable1 I choose the databank file and all options. If I test the connection I got the error "database format not recogniced" with the path of the file.
>1 - The database isn't where the program is looking for it.
Considering the error this is not possible because Delphi tells that the database format is not recogniced and not that there is no database ! There might be a problem with the understanding of the path string but I just chose the data source by the connection stringbuilder so I don't understand why.
> 2- There is a permission issue on the folder or on the database itself.
I have all the rights on this Computer. If you mean something as administrator rights this is not applicable, also because obviuosly Delphi does not tell us this error. It tells me "provider initialisation error". And just building the connection string this could not be the error.
3 - The database is corrupt. Can you open it in Access both before you make the install and after it's installed? Is the install being run by the same user as the program? With the same permissions?
As I said before no problem at all to open it with ACCESS.
The problem is only ADO related nothing to do with ACCESS. It occurs when compiling the problem or even just building the connection string after a problem like this has occurred..
Obviouesly, it would occur if I install the program.
Anyway, afterwards it worked, so this must be another type of problem. Perhaps there was just a problem which is resolved by closing delphi or/ and the computer ....
Thanks for the trial....
MikeR
> Anyway, afterwards it worked, so this must be another type of problem. Perhaps there was just a problem which is resolved by closing delphi or/ and the computer ....
>
> Thanks for the trial....