?
Solved

URGENT!!! Problem with ADO and Access

Posted on 2006-11-26
5
Medium Priority
?
358 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 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month14 days, 16 hours left to enroll

771 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