Blank Cell Values in DBGrid

[daneb]

Hi.

I have a DBgrid bounded to an Access database table through a Data control. My table has some numeric and string fields that are empty/no value. When the DBGrid is displayed, all cells that do not contain values are left blank. My problem is this: if I attempt to leave a numeric cell (which has originally a numeric value) blank, a message box with a "DBgrid" title appears saying "Invalid Field Data type". This will prevent me from going to other cells, especially on the next row, unless I input a value. But for cells (string/numeric)that originally are blank, there's no problem. How can I remove or delete numeric values from cells without receiving this error message and just leave these cells empty?

Thanks for the info in advance!

[Iouri]

Check in your database if the value is required and if you want to leave them blank allow null in these fields.

Iouri Boutchkine
iouri@hotsheet.com

[Cubbie]

In your Access database try setting the Allow Zero Length to Yes.

[daneb]

Thanks for the reply. But the Allow Zero Length option is for text fields only. I have already set this option to "Yes" for my text fields. For numeric fields, the Required option is set to "No" value (default). If I try to delete an existing numeric value in the Access DataSheet view, there's no error, but in the DBGrid display, an Invalid data type error appears. Do you have any more suggestions?

[daneb]

Thank you for your reply. I don't think that there is an option like Allow Null in Access, especially if the field is numeric. I have already set my text fields' Allow Zero Length to "Yes", but it is my numeric fields that I have a problem on. There is no option like this for numeric fields. Any more ideas?

[Cimperiali]

try setting default value = null
(general panel of field)
be sure the fields are not required, not indexed, not part of a key.



Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[Cimperiali]

..wait... when you change from a filed of a grid to another, are not you automatically trying to insert a space or null string (="") in the field?...
May be you have to write some code in your "leave the cell" event (=validate event) like:
'pseudocode
if cell.text= "" then cell.text ="0"


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[daneb]

Thanks for the reply. Sorry for the late response. I have no access over the weekend. I tried setting my numeric fields' Default Value to Null, but the error persisted. I even tried putting the value vbNull to the cell if the original value is removed, but I got the same error. Any more ideas?

[Cimperiali]

Last try is: put there a zero (which is the no-value of numeric fields).

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[daneb]

Thanks again. There's already a zero in the Default Value of each numeric field. This is placed for every numeric field that you create or add to an existing table. Why is it that for records that don't have a value (not even zero) in their numeric fields in the Access table, these display as blank in the DBGrid, whereas for records that have non-blank values, when you remove these values in the DBGrid, the "Invalid field data type" shows up? How can I safely remove these values without getting the error message?

[Cimperiali]

I know it is strange, but it seems DB show you empty (=null) values in numeric fields as those fields are not really inserted in validate (constraint) of DB itself untill a first value is entered. Once a value is entered, DB will look for valid data, and from that time on, if you cannot set a kind of "accept null value" for your field, you will never be enabled to set the value to null again. Null is a non-value, while zero is equivalent to no-wheight-value which is an accepted value by Db vaidate rules.

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[Cimperiali]

(adding to my previous answer)
Moreover, if one of the fields is a key field (primary) you will never be able to set it to null, and Db will never let you set attributes to accept null value. For a primary key, values are always required (tthis apply to the entire key, which may be composed of more than one field). But as you look at first record *before* inserting anithing, you may see it has all "blanks" value. This is only because it still not exists, untill you write it for the first time.
Hope this helped
Cesare Imperiali


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.