prb with generated dataform and null values

[Dan]

Hi all, I have a problem with the template code for an IDE-generated
dataform. You can easily reproduce it with the following steps:

1) create a dummy Access database with a table with a couple of fields:
ID = a numerical ID (e.g. autonumber)
somefield = a textual field which can be NULLABLE but NOT zero-length
(in Access terms, say "No" to required and "No" to allow zero length)

2) create a new windows form project with VS and let it create a data form
for this table.

3) launch the application and create a new record: do not enter anything for
the textual field, then update the data source. If you now open the table
with Access, you can see that the new record contains a NULL value for the
textual field, as expected (e.g. "SELECT * FROM DummyTable WHERE
IsNull(somefield)" shows it).

4) THIS IS THE POINT: now, edit again the same record: this time enter
something in the textual field, and update the database. Again, edit the
same record and CLEAR the textual field (delete all the characters in the
bound editbox). Then update the datasource: this will raise an Exception,
because the update command is trying to store NOT a NULL value but an EMPTY
string, and by this database design an empty string is not allowed there.

Now, is there a way to tell the update command that when a bound text
control is empty it should store a NULL and not an empty string?

Thanks in advance...

 

[Kevin Yu [MSFT]]

Hi Dan,

You got such an exception, because when you create the record, the data in
this cell is set to NULL automatically. But when you modify this cell and
clear the text in it, the content of the cell changed to an empty string.
So an exception is thrown.

I think you can changed the UpdateCommand and InsertCommand of the
DataAdapter to avoid this from being happen.
To edit UpdateCommand or InsertCommand, right click OleDbDataAdapter1 below
the form, choose properties in the popup menu. Expand a command property
and write your own command in CommandText property. You can also write the
command in the .cs or .vb file.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| From: "Dan" <[email protected]>
| Subject: prb with generated dataform and null values
| Date: Sat, 11 Oct 2003 09:33:28 +0200
| Lines: 30
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: ppp-217-133-159-44.cust-adsl.tiscali.it 217.133.159.44
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63414
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Hi all, I have a problem with the template code for an IDE-generated
| dataform. You can easily reproduce it with the following steps:
|
| 1) create a dummy Access database with a table with a couple of fields:
| ID = a numerical ID (e.g. autonumber)
| somefield = a textual field which can be NULLABLE but NOT zero-length
| (in Access terms, say "No" to required and "No" to allow zero length)
|
| 2) create a new windows form project with VS and let it create a data form
| for this table.
|
| 3) launch the application and create a new record: do not enter anything
for
| the textual field, then update the data source. If you now open the table
| with Access, you can see that the new record contains a NULL value for the
| textual field, as expected (e.g. "SELECT * FROM DummyTable WHERE
| IsNull(somefield)" shows it).
|
| 4) THIS IS THE POINT: now, edit again the same record: this time enter
| something in the textual field, and update the database. Again, edit the
| same record and CLEAR the textual field (delete all the characters in the
| bound editbox). Then update the datasource: this will raise an Exception,
| because the update command is trying to store NOT a NULL value but an
EMPTY
| string, and by this database design an empty string is not allowed there.
|
| Now, is there a way to tell the update command that when a bound text
| control is empty it should store a NULL and not an empty string?
|
| Thanks in advance...
|
|
|