Solved

URGENT!!! Problem with ADO and Access

Posted on 2006-11-26
5
357 Views
Last Modified: 2013-11-23
    Hi!

I have an Access DB with several tables connected to my Delphi App through ADO.
I'm getting strange errors when my application is inserting data into the connected database.
One table has file information (filename, extension, path and dates) and other table
has extensions info ( extension and description).
The filetable has the extension field from the extensiontable as primary-foreign-key (PFK).
When inserting the file-info into the filetable example a .DOC or .XLS file I get the error
" You cannot add or change a record because a related record is required in table 'myext' ".
NOTE! I get this error after successfully insert around 400-700 OTHER .DOC or .XLS files.
Why ? Is it an ADO releated error/bug ?
This error is wery strange because as I said I can successfully insert 400-700 other .DOC or .XLS files
into the file-info table before getting this error. THE extension table has 2 records with both DOC and XLS
in the extension field. I remove the . of the extension before inserting the record.

Regards,
  Tomas Helgi
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
calinutz earned 500 total points
ID: 18015725
It is not an ADO issue... it is probably something you missed in the construction of your SQL's
perhaps you are trying to insert duplicate values in the detail table, anyway it is a matter of master-detail tables. I cannot be sure what your problem is unless I see the code you are trying to execute.

You could trace your error by adding a memo to your form where the inserts are called... and add inside it each SQL -BEFORE- execution... this way you'll see the exact SQL statement where it crashes.
Something like:

dm.adoquery1.Active:=false;
dm.adoquery1.SQL.Clear;
dm.adoquery1.SQL.Add('insert into...');
memo1.Lines.Add(dm.ADOQuery1.SQL.Text);
dm.ADOQuery1.ExecSQL;

After you get the exact SQL you may analyze it and try to execute it from Access's own Query editor or something like that (WinSQL... or something)

regards
0
 
LVL 11

Expert Comment

by:calinutz
ID: 18015752
So as I see it the myext table is the master with primary key = extension
and the detail table holds the filenames and other info about the file, and is connected to the master table by extensionfield.
The error seems to be explaied by the fact that you are trying to insert a record about a specific file (in the detail table) that has an unknown extension... an extension that does not appear in the master table.
The solution for you is prety sure the one in my previous comment... the Memo idea.

If you still have difficulties paste here the code and we'll check it.
But I am quite sure there is some file that has no extension (or a weird extension) on your hard drive and you did not treat the case
0
 
LVL 25

Author Comment

by:Tomas Helgi Johannsson
ID: 18017766
That's the strange thing.
As I said before I can successfully insert 400-700 other DOC or XLS files  
into the file-info table before getting this error. THE extension table has 2 records with both DOC and XLS
in the extension field.
I log each record of unsuccessful insert into a logtable and there is nothing wrong  
with the extensions in which the tables are joined. I use TADOTable to insert the records.

Regards,
  Tomas Helgi
0
 
LVL 11

Expert Comment

by:calinutz
ID: 18017999
Try to use TADOQuery...
Or try to paste here your code, perhaps we can make something out of it.
And try to log the sql statements in the memo ... not the table stuff.
Just try it... and maybe you'll find the error yourself.
Maybe the file has a dot after the .doc extension, o0r maybe a space...
you just have to use the Memo

0
 
LVL 25

Author Comment

by:Tomas Helgi Johannsson
ID: 18022659
  Hi
Thanks calinutz.
Well it has something to do with ADO or problem with Quoted strings.
my record looks like

  TMyFileRec = record
    id: Integer;
    drive: string[50];
    name: string[100];
    path: string[255];
    created: TDateTime;
    modified: TDateTime;
    accessed: TDateTime;
    extension: string[50];
  end;

In stead of using


myfiles.Append;
....
myfilesfilepath.Value := QuotedStr(rec.path); <<-- here the use of QuotedStr solved some part of the problem but not all.
....
myfiles.post;

so I used

myfiles.AppendRecord([....]) and also
used a temporary string variable to store the string from ExtractFilePath and the other Extract functions
when writing the file-info data to the record and passing that record to a DataModule function.
That did the trick and the logtable is empty after inserting the records.
The records in the logtable showed that if the filename or the filepath had space in the string
then the insert failed.  

Regards,
   Tomas Helgi
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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