Database text field. Allow Zero Length Yes/No

[IanS]

In jet databases. Text columns have an option "Allow Zero Length" which can be set Yes or No.

Has the behaviour changed? In my vb6 application If "Allow Zero Length=No" then an empty string is allowed but null isnt. So, using an sql INSERT naming all fields and inserting '' empty strings into each field is no problem.

But in .Net Oledb/jet4 an empty string is not allowed. The string has to contain 1 or more characters.?

Is that right or is something else going on?

[techgnome]

"If "Allow Zero Length=No" then an empty string is allowed but null isnt." --- that sounds backwards...

a zero-length string would be an empty string... which is not the same as a null. you can set a field to not allow nulls, AND not allow zero-length strings... in which case, yes, any value inserted into that field has to be a length of one or more...
it's also possible to allow nulls and NOT allow zero length strings... in which case you could insert a value with a length of one or more... OR a NULL value...

Sounds like some behavior got corrected somewhere along the line.

-tg

[IanS]

Thanks.

Maybe a bit of background. Sorry if its long winded.

i have an application written in vb6 Its been running without problems for around 15 years. The database has all text columns set to "allow zero length=yes" "allow nulls=yes". The application confirms that data is correct before inserting it and i at least write an empty string into all columns even though null is allowed. The vb6 program has no problems.

2 years ago i wrote a new .net version of the application. The new version has no problems either.

Today a user contacted me. He sent me a copy of his database and i see that he's changed some of the design. Some text fields in his datafile Have been set to "Allow Zero Length=No". He's not supposed to fiddle about in the database but now that he has made the change I looked into his complaint.

The old vb6 application has no problems writing to the database. The .net application fails to write saying that it cant write a zero length string.

Its not mission critical. The user shouldnt have fiddled with his database design and he will just have to change it back.

But now I'm just curious.

[dunfiddlin]

Well, it's VB6 that's wrong (where have I written that before?) It shouldn't allow "" if Allow Zero Length is No though it clearly does/did. Just another example of VB6 letting you get away with syntax murder!

[techgnome]

More likely it was the drivers doing it than VB6 itself... but yes, the users shouldn't be fiddling about with the DB like that... and if they are, that's like breaking the seal and voiding the warranty.

-tg

[IanS]

More likely it was the drivers doing it than VB6 itself... but yes, the users shouldn't be fiddling about with the DB like that... and if they are, that's like breaking the seal and voiding the warranty.

-tg

Certainly. Its just such a long time ago that i couldnt remember what versions of dao/jet were used so just mentioned vb6 knowing you guys would know what i was talking about.

Thank you all for your feedback.
Ian