Board index » delphi » MS-Access : Field must not be empty

MS-Access : Field must not be empty

Hi !

I have to use a MS-Access database, through BDE (5.01) and DAO 3.5 (or 3.0
it's the same)

On some tables, when I edit some records and set some char field value to an
empty string, I get a message : "the field must not be empty !".  I believe
it occurs randomly...

Of course, I'm sure that the 'required' property is set to false and do not
see any other property related to this.
I can append new records and post them with no value in those fields.
The problem occurs only when I edit, clear the field, and post a record.
I can do the job on the same table with Database Explorer shipped with D5..

Anyone have found this ?  Ideas ?

Regards.

 

Re:MS-Access : Field must not be empty


It is a setting in Access when you define the table. You need to set the
Allow Zero Length option for the field to True. It is False by default.

--
Bill

Re:MS-Access : Field must not be empty


Thanks.

The tables are created by DELPHI  (createtable), not using MS-ACCESS.

I Do not see any parameter to "Allow Zero Length", in FieldDefs or Fields.

What should I do ?

Regards.

"Bill Todd (TeamB)" <billtodd...@nospam.qwest.net> a crit dans le message
news: 3a81c810$1_1@dnews...

Quote
> It is a setting in Access when you define the table. You need to set the
> Allow Zero Length option for the field to True. It is False by default.

> --
> Bill

Re:MS-Access : Field must not be empty


I believe there is a way to change that setting by making direct DAO method
calls but I do not know how to do it.

--
Bill

Re:MS-Access : Field must not be empty


I don't know how to do it either, but in Access the default field attributes
are Required=False and AllowZeroLength=False.  The first thing to understand
is that Access, unlike BDE/Paradox tables, have two kinds of "blank" fields.
They can be NULL or a zero-length string.  These are not the same, and
Paradox tables (and I think BDE in general) do not make this distinction.
Therefore, if you insert '' (two single quotes, the empty string, or
zero-length string) to a field that is not required, but also does not allow
zero length strings, you have just violated the latter condition.  NULL is
not a zero-length string, it means there is no string at all (a zero-length
string is a string).  I don't have any solutions for you or experience with
this, but this may help you look further.  If you can get or borrow a copy
of Access (or take your tables to it) and set that AllowZeroLength to True,
you should be ready to go.  I do not know what kind of code you have, but
the other option is to avoid posting any zero-length strings.  As a last
resort, you might try an Access or VB newsgroup to get more info on DAO
calls (or search Deja).

Bill Todd (TeamB) <billtodd...@nospam.qwest.net> wrote in message
news:3a82d5bc$1_2@dnews...

Quote
> I believe there is a way to change that setting by making direct DAO
method
> calls but I do not know how to do it.

> --
> Bill

Re:MS-Access : Field must not be empty


"Bill Todd (TeamB)" <billtodd...@nospam.qwest.net> wrote in message
news:3a82d5bc$1_2@dnews...

Quote
> I believe there is a way to change that setting by making direct DAO
method
> calls but I do not know how to do it.

Here is how it is done:

  DBField := DBTable.CreateField(fName,fType,fSize);
  DBField.DefaultValue := '';
  if fType = dbText then
   DBField.AllowZeroLength := -1;  // Access defaults to NOT allow zero
length strings so we need to explicitly set it...
  DBTable.Fields.Append(DBField);

This assumes that :
    DBField is a variant;
    DBTable is a DAO object from the TableDef
    fName is the name for the field
    fType is the type of field (eg: 1-boolean; 2-byte; etc..)
    fSize is used only for string fields for length
    dbText is my constant for text fields (field type 10)

HTH
Woody

Re:MS-Access : Field must not be empty


Ok, thanks

It seems to be the solution, unless Inprise correct the bug.

But could you give a little more code ?
Which types and units are used before your piece of code.
How do you create these DAO objects ?

Regards.

"Woody" <woody....@ih2000.net> a crit dans le message news:
3a831fc8$1_1@dnews...

Quote
> "Bill Todd (TeamB)" <billtodd...@nospam.qwest.net> wrote in message
> news:3a82d5bc$1_2@dnews...
> > I believe there is a way to change that setting by making direct DAO
> method
> > calls but I do not know how to do it.

> Here is how it is done:

>   DBField := DBTable.CreateField(fName,fType,fSize);
>   DBField.DefaultValue := '';
>   if fType = dbText then
>    DBField.AllowZeroLength := -1;  // Access defaults to NOT allow zero
> length strings so we need to explicitly set it...
>   DBTable.Fields.Append(DBField);

> This assumes that :
>     DBField is a variant;
>     DBTable is a DAO object from the TableDef
>     fName is the name for the field
>     fType is the type of field (eg: 1-boolean; 2-byte; etc..)
>     fSize is used only for string fields for length
>     dbText is my constant for text fields (field type 10)

> HTH
> Woody

Re:MS-Access : Field must not be empty


Quote
"Martin RODOT" <MRo...@mrit.qom> wrote in message

news:960kib$fa08@bornews.inprise.com...

Quote
> Ok, thanks

> It seems to be the solution, unless Inprise correct the bug.

> But could you give a little more code ?
> Which types and units are used before your piece of code.
> How do you create these DAO objects ?

Here is some code that may help...
Woody

const
// constants for Access Version 95 or 97...
  cDAOEngine: string='DAO.DBEngine'; // for Access 95 databases...
  cDAOEngine35: string='DAO.DBEngine.35'; // for Acces 97 databases...
// field type constants...
  dbBoolean = 1;
  dbByte = 2;
  dbInteger = 3;
  dbLong = 4;
  dbCurrency = 5;
  dbSingle = 6;
  dbDouble = 7;
  dbDate = 8;
  dbBinary = 9;
  dbText = 10;
  dbLongBinary = 11;
  dbMemo = 12;
  dbGUID = 15;
  dbAutoInc = 16;
  dbVarBinary = 17;
  dbChar = 18;
  dbNumeric = 19;
  dbDecimal = 20;
  dbFloat = 21;
  dbTime = 22;
  dbTimeStamp = 23;
// used for index ordering...
  dbDescending = 1;
  dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0';
// used for manipulating table information...
 dbOpenTable = 1; // single table, no links, updatable
  dbOpenDynaset = 2; // updatable, only read when needed
  dbOpenSnapshot = 4; // not updatable, in memory
  dbOpenForwardOnly = 8; // snapshot, forward scrolling only
  dbOpenDynamic = 16; // ODBC only, updatable

// variables used for accessing the DAO object once established..
var
  DBEngine,
  DBWorkSp,
  DBDataB,
  DBTable,
  DBField,
  DBIndex: variant;

function StartYourEngine(WorkName, User, Password, DAOType: string):
boolean;
// WorkName = any name you want to use to open a "workspace" in DAO...
// User = user name... if security is not on, just use Admin...
// Password = user password... if security is not on, use an empty string...
// DAOType = DAO engine string... cDAOEngine or cDAOEngine35
begin
 try
    DBEngine := CreateOLEObject(DAOType);
    DBWorkSp := DBEngine.CreateWorkSpace(WorkName,User,Password);
    result := true;
  except
   on E: Exception do
    begin
     ShowMessage('Error: ' + E.Message);
    result := false;
    end;
  end;
end;

function CloseDatabase: boolean;
begin
 try
   DBDataB := unassigned;
    result := true;
  except
   on E: Exception do
    begin
     ShowMessage('Error: ' + E.Message);
    result := false;
    end;
  end;
end;

function AddNewField(tName,fName: string; fType,fSize: integer): boolean;
// tName = table name to add the new field to...
// fName = field name to add...
// fType = field type...
// fSize = field size... ignored for some fields such as numeric, etc.
label
  NoCanDo,
  ClearVariants;
var
  x: integer;
  s: string;

begin
  for x := 0 to DBDataB.TableDefs.Count-1 do
  begin
   Application.ProcessMessages;
   s := DBDataB.TableDefs[x].Name;
    if UpperCase(s) = UpperCase(tName) then
    begin
     s := 'found';
     break;
    end;
  end;
 if s <> 'found' then
   goto NoCanDo;
  DBTable := DBDataB.TableDefs[x];
  DBField := DBTable.CreateField(fName,fType,fSize);
  DBField.DefaultValue := '';
  if fType = dbText then
   DBField.AllowZeroLength := -1;  // Access defaults to NOT allow zero
length strings so we
                    // we need to explicitly set it...
  DBTable.Fields.Append(DBField);
 Result := true;
  goto ClearVariants;
NoCanDo:
 Result := false;
ClearVariants:
 DBTable := unassigned;
end;

Re:MS-Access : Field must not be empty


Thanks, Woody, it is nearly ok.
I'm just missing the way to assign "DBDataB", i.e the OpenDatabase routine.

Where did you find this doc ?

Regards.

"Woody" <woody....@ih2000.net> a crit dans le message news:
3a84224e_1@dnews...

Quote
> "Martin RODOT" <MRo...@mrit.qom> wrote in message
> news:960kib$fa08@bornews.inprise.com...
> > Ok, thanks

> > It seems to be the solution, unless Inprise correct the bug.

> > But could you give a little more code ?
> > Which types and units are used before your piece of code.
> > How do you create these DAO objects ?

> Here is some code that may help...
> Woody

> const
> // constants for Access Version 95 or 97...
>   cDAOEngine: string='DAO.DBEngine'; // for Access 95 databases...
>   cDAOEngine35: string='DAO.DBEngine.35'; // for Acces 97 databases...
> // field type constants...
>   dbBoolean = 1;
>   dbByte = 2;
>   dbInteger = 3;
>   dbLong = 4;
>   dbCurrency = 5;
>   dbSingle = 6;
>   dbDouble = 7;
>   dbDate = 8;
>   dbBinary = 9;
>   dbText = 10;
>   dbLongBinary = 11;
>   dbMemo = 12;
>   dbGUID = 15;
>   dbAutoInc = 16;
>   dbVarBinary = 17;
>   dbChar = 18;
>   dbNumeric = 19;
>   dbDecimal = 20;
>   dbFloat = 21;
>   dbTime = 22;
>   dbTimeStamp = 23;
> // used for index ordering...
>   dbDescending = 1;
>   dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0';
> // used for manipulating table information...
>  dbOpenTable = 1; // single table, no links, updatable
>   dbOpenDynaset = 2; // updatable, only read when needed
>   dbOpenSnapshot = 4; // not updatable, in memory
>   dbOpenForwardOnly = 8; // snapshot, forward scrolling only
>   dbOpenDynamic = 16; // ODBC only, updatable

> // variables used for accessing the DAO object once established..
> var
>   DBEngine,
>   DBWorkSp,
>   DBDataB,
>   DBTable,
>   DBField,
>   DBIndex: variant;

> function StartYourEngine(WorkName, User, Password, DAOType: string):
> boolean;
> // WorkName = any name you want to use to open a "workspace" in DAO...
> // User = user name... if security is not on, just use Admin...
> // Password = user password... if security is not on, use an empty
string...
> // DAOType = DAO engine string... cDAOEngine or cDAOEngine35
> begin
>  try
>     DBEngine := CreateOLEObject(DAOType);
>     DBWorkSp := DBEngine.CreateWorkSpace(WorkName,User,Password);
>     result := true;
>   except
>    on E: Exception do
>     begin
>      ShowMessage('Error: ' + E.Message);
>     result := false;
>     end;
>   end;
> end;

> function CloseDatabase: boolean;
> begin
>  try
>    DBDataB := unassigned;
>     result := true;
>   except
>    on E: Exception do
>     begin
>      ShowMessage('Error: ' + E.Message);
>     result := false;
>     end;
>   end;
> end;

> function AddNewField(tName,fName: string; fType,fSize: integer): boolean;
> // tName = table name to add the new field to...
> // fName = field name to add...
> // fType = field type...
> // fSize = field size... ignored for some fields such as numeric, etc.
> label
>   NoCanDo,
>   ClearVariants;
> var
>   x: integer;
>   s: string;

> begin
>   for x := 0 to DBDataB.TableDefs.Count-1 do
>   begin
>    Application.ProcessMessages;
>    s := DBDataB.TableDefs[x].Name;
>     if UpperCase(s) = UpperCase(tName) then
>     begin
>      s := 'found';
>      break;
>     end;
>   end;
>  if s <> 'found' then
>    goto NoCanDo;
>   DBTable := DBDataB.TableDefs[x];
>   DBField := DBTable.CreateField(fName,fType,fSize);
>   DBField.DefaultValue := '';
>   if fType = dbText then
>    DBField.AllowZeroLength := -1;  // Access defaults to NOT allow zero
> length strings so we
>                     // we need to explicitly set it...
>   DBTable.Fields.Append(DBField);
>  Result := true;
>   goto ClearVariants;
> NoCanDo:
>  Result := false;
> ClearVariants:
>  DBTable := unassigned;
> end;

Re:MS-Access : Field must not be empty


Quote
"Martin RODOT" <MRo...@mrit.qom> wrote in message

news:966shu$st32@bornews.inprise.com...

Quote
> Thanks, Woody, it is nearly ok.
> I'm just missing the way to assign "DBDataB", i.e the OpenDatabase
routine.

> Where did you find this doc ?

> Regards.

First, you didn't need to quote my entire response to reply. (Team B would
catch this so I'm just saving them the typing. :)

I get all my information from the Access Help Files. After working with the
DAO for awhile, you'll get a good feel for how to do things. If you still
can't find what you want, email me your piece of code that doesn't work and
I'll try to help.

Woody

Other Threads