Solved

URGENT!!! Problem with ADO and Access

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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