Handling {*word*193} MS Access zero length error
We recently converted a Delphi 4 program that used dBase files to an MS
Access database. In (what appears to have been inadequate) testing
everything worked OK. After installing it at several client sites I began to
get calls about the users receiving a General SQL Error. The message was
"Field xxxx cannot be a zero length string". It appears that when you create
a database using Access the text fields default to not allowing a field to
be empty (null?). In trying to understand this it appears that if you leave
a field blank there is no error, but if you add data to a field and then try
to remove it (set the field to blank) the error is generated. All of the
fields were set to the defaults: Required = No, Allow Zero Length = 0. If I
start Access, Open the table and edit in the grid I don't receive any error
when I blank out the contents of the field.
This appears to be the same sort of error that occurs with edit fields, e.g.
if you set an edit mask to the default phone number (!\(999\)000-0000;1;_)
it is happy to be blank. But If you put data into it and then try to blank
it out it will FORCE you to change it to ()000-000. It will not allow you to
leave it blank. Because of this I have been forced to use
(!\(999\)999-9999;1;_) as the mask.
The code in question is the standard
If DmContra.TblContr1.Modified then
DmContra.TblContr1.Post
else
DmContra.TblContr1.Cancel;
ModalResult := mrOK
The real problem here is that after the post fails the code attempts to
continue and closes the form that the user is editing. It then activates a
procedure in the calling form that attempts to run a query. The query tries
to close and change a parameter but it cannot because the table is still
locked from the unposted changes. This locks up the program and causes the
users to have to reboot.
The only real way to fix this seems to be to go to each of the client sites,
start Access, open each of the tables in design mode and change each of the
fields to allow zero length fields.
I did find that could keep the program from crashing by using
procedure TFrmDetailInfo.BbContInfoOKClick(Sender: TObject);
Var ClientStr : String;
begin
try
If DmContra.TblContr1.Modified then
DmContra.TblContr1.Post
else
DmContra.TblContr1.Cancel;
ModalResult := mrOK
except
file://on TDBError do
fDbigetErrorEntry(1, ClientStr);
ShowMessage(ClientStr);
ModalResult := mrNone;
end;
end;
The only way to get around the error seems to be to use the try..except
block. The problem is that to do this I have to know what class the error is
so that I can handle it. Since I don't know that I have to include the
fDbigetErrorEntry code (from the BDE help file) in every module that I want
to use Post in. This still requires the users to enter something into the
field.
So I end up with 3 questions:
1) Is there is any way to tell the BDE to shut up and post the record
anyway?
2) If not is there a better way to handle the error, hopefully one that does
not require that I include the fDbigetErrorEntry code?
3) If Access lets me use zero length strings without giving this error why
does the BDE enforce it?
