Solved

URGENT!!! Problem with ADO and Access

Posted on 2006-11-26
5
356 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

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 my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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