Detecting if a field is required

[Lorenzo Bolognini]

Hi all,

i need to detect whether a field is required or not. I'm using this code for
building a string to convert later to an array (by Split) of which each
element matches the field index (ex. Field(myArray(0)):

For Each Field in rst.Fields
If Field.Attributes And adFldIsNullable Then
Required = 0 'field is NOT required
Else
Required = 1 'field is required
End If

strFieldRequired = strFieldRequired & Required & " "
Next

I'm running the above code against a table which has 4 fields in this exact
order:
- a PK
- 2 required text fields
- 1 NOT required text field

And i'm getting this result when printing strFieldRequired:
1 0 0 0

So what's the problem with the code above? I've got some inspiration from
the script at this URL:
http://www.asptechniques.com/content.asp?a=co&cID=910

Thank You,
Lorenzo

[Aaron Bertrand [MVP]]

Make sure you are using OLEDB to connect to your database; adFldIsNullable
is not exposed to the standard ODBC/JET drivers. To see an OLEDB connection
string, see http://www.aspfaq.com/2126 and make sure you have a recent
installation of MDAC.

"Lorenzo Bolognini" <lo*****@mysurname.net> wrote in message
news:5L**********************@news1.tin.it...

Hi all,

i need to detect whether a field is required or not. I'm using this code for building a string to convert later to an array (by Split) of which each
element matches the field index (ex. Field(myArray(0)):

For Each Field in rst.Fields
If Field.Attributes And adFldIsNullable Then
Required = 0 'field is NOT required
Else
Required = 1 'field is required
End If

strFieldRequired = strFieldRequired & Required & " "
Next

I'm running the above code against a table which has 4 fields in this exact order:
- a PK
- 2 required text fields
- 1 NOT required text field

And i'm getting this result when printing strFieldRequired:
1 0 0 0

So what's the problem with the code above? I've got some inspiration from
the script at this URL:
http://www.asptechniques.com/content.asp?a=co&cID=910

Thank You,
Lorenzo

[Lorenzo Bolognini]

"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> ha scritto nel messaggio
news:uJ**************@TK2MSFTNGP09.phx.gbl...

Make sure you are using OLEDB to connect to your database; adFldIsNullable
is not exposed to the standard ODBC/JET drivers. To see an OLEDB connection string, see http://www.aspfaq.com/2126 and make sure you have a recent
installation of MDAC.

The following is my connection string and i'm using ADO 2.8 on a Win2k with
ALL the latest patches:

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.Mappath("\xxxy\datasource\xyz.mdb") & _
";Persist Security Info=False"

I'm noticing that someone else has had the same problem always with Access
as you may see if you look for this MsgID: uGwIzjTjBHA.2024@tkmsftngp04 on
microsoft.public.data.ado

Thank You,
Lorenzo

P.S I live by aspfaq.com and i couldn't stress enough how much i'm thankfull
to you!

[Bob Barrows]

When I think back to the dozens of Access applications I've created, and the
single ASP application I've created that used Access, I can safely say that
I've never onec had to worry about whether a field was "Required" or not.
The reason: I never created a nullable field. And even when i was using a
database created by someone else, I did not care: I always put a default
value into all fields. Instead of wasting devlopment and processing time
with code code that tests the nullability of the fields in your table, start
with the assumption that they are all required.

Having said that, I'm wondering if you realize that an Access Text field can
be created with the Required checkbox unchecked, but the "Allow zero-length
string" checkbox unchecked, a contidtion which will not be covered in the
Attributes property since it's a Jet-specific property.

Bob Barrows

Lorenzo Bolognini wrote:

"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> ha scritto nel
messaggio news:uJ**************@TK2MSFTNGP09.phx.gbl...

Make sure you are using OLEDB to connect to your database;
adFldIsNullable is not exposed to the standard ODBC/JET drivers. To
see an OLEDB connection string, see http://www.aspfaq.com/2126 and
make sure you have a recent installation of MDAC.

The following is my connection string and i'm using ADO 2.8 on a
Win2k with ALL the latest patches:

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.Mappath("\xxxy\datasource\xyz.mdb") &
_ ";Persist Security Info=False"

I'm noticing that someone else has had the same problem always with
Access as you may see if you look for this MsgID:
uGwIzjTjBHA.2024@tkmsftngp04 on microsoft.public.data.ado

[Lorenzo Bolognini]

"Bob Barrows" <re******@NOyahoo.SPAMcom> ha scritto nel messaggio
news:Ot**************@TK2MSFTNGP10.phx.gbl...

Having said that, I'm wondering if you realize that an Access Text field can be created with the Required checkbox unchecked, but the "Allow zero-length string" checkbox unchecked, a contidtion which will not be covered in the
Attributes property since it's a Jet-specific property.

Thank You very much but that would be a workaround and i think i cannot rely
on that since i'm building a function for:
- displaying all rows in a table and allowing inserts, edit and delete of
records in that table
- building a for with ready-made javascript (on the client side) and server
controls based on the field attributes

Making it the way u say may work for MY db but not on someone elses which i
may not be allowed to modify if i want to reuse my function.

Anyway thank you very much for pointing that out.

Lorenzo

[Lorenzo Bolognini]

"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> ha scritto nel messaggio
news:uJ**************@TK2MSFTNGP09.phx.gbl...

Make sure you are using OLEDB to connect to your database; adFldIsNullable
is not exposed to the standard ODBC/JET drivers. To see an OLEDB connection string, see http://www.aspfaq.com/2126 and make sure you have a recent
installation of MDAC.

I've finally made it this way:

rst.Open("tblAuthor"), cnn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect

For Each Field in rst.Fields
If Field.Attributes And adFldIsNullable Then
Required = 0 'nullable
Else
Required = 1 'not nullable
End If

strFieldRequired = strFieldRequired & Required & " "
Next

I've played only with the ADO CommandTypeEnum i don't know if chainging the
cursors may affect in any way the results but THIS way it works!! ;-)
adCmdTableDirect is is the only CommandTypeEnum which returns the right
results

Think this could be placed on the aspfaq.com

Lorenzo