Very Computer

by **Imtiazur Sye** » Thu, 06 Jun 2002 00:04:03

I am using a textbox control (let's call it Text1) data
bound to an ADO Data Control (let's call it MyAdoDC).
MyAdoDC is connected to a SQL Server through the provider
SQLOLEDB. The recordsource for MyAdoDC is a table called
Table1. Table1 contains 4 rows. The field Text1 is bound
is of type Integer. When I run the project and open the
form containing MyAdoDC and Text1, I see the bound field
value in Text1 without any problem. But when I remove the
contents of Text1 and attempt to navigate forward (to
update the field value to null) by clicking on the right
arrow on MyAdoDC I get the message 'Operation is Canceled'
and the value doesn't change. However if I change the
value in Text1 to a different number (and not blank) the
operation goes fine. I think this is a bug in ADO. Does
anyone think otherwise?

by **Dave War** » Thu, 06 Jun 2002 17:04:49

It could be a bug, but I don't think so. Text boxes hold text. If you
remove all the text, you are not changing the value to null, but to a
zero length string. If you are trying to update a field of integer data
type, you've got conflict with a string data type. What would be better
to do is use the Text1_Change event to catch the problem
eg
Private Sub Text1_Change()
If Text1.Text = "" Then Text1.Text = 0
End Sub
That'll sort it out for you.
Cheers
Dave

> I am using a textbox control (let's call it Text1) data
> bound to an ADO Data Control (let's call it MyAdoDC).
> MyAdoDC is connected to a SQL Server through the provider
> SQLOLEDB. The recordsource for MyAdoDC is a table called
> Table1. Table1 contains 4 rows. The field Text1 is bound
> is of type Integer. When I run the project and open the
> form containing MyAdoDC and Text1, I see the bound field
> value in Text1 without any problem. But when I remove the
> contents of Text1 and attempt to navigate forward (to
> update the field value to null) by clicking on the right
> arrow on MyAdoDC I get the message 'Operation is Canceled'
> and the value doesn't change. However if I change the
> value in Text1 to a different number (and not blank) the
> operation goes fine. I think this is a bug in ADO. Does
> anyone think otherwise?

by **Imtiazur Sye** » Thu, 06 Jun 2002 23:24:57

Hi Dave,
Thanks for the response. I have quite a few questions about this
behavior of ADO. In your response you said that when I clear the text
box and try to update an integer field, I am trying to send an empty
string which ADO doesn't accept. Then how do I update an integer field
with null using a textbox? Look at this from the background that a
character field is updated to a null when the contents of the textbox
bound to it are cleared and update method is executed. Setting
Text1.Text=0 when Text1 is empty is not a solution in most cases since a
developer might want a null in the field and not 0 since a zero will
interfere with computed statistics on this field.

Imtiaz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

by **Imtiazur Sye** » Sat, 08 Jun 2002 01:36:03

This is definitely a bug. What you are suggesting is a
workaround. How does one update an integer field
containing a non-null value to null using
adodc.recordset.update method?

Imtiaz

>-----Original Message-----
>It could be a bug, but I don't think so. Text boxes hold
text. If you
>remove all the text, you are not changing the value to
null, but to a
>zero length string. If you are trying to update a field
of integer data
>type, you've got conflict with a string data type. What
would be better
>to do is use the Text1_Change event to catch the problem
>eg
>Private Sub Text1_Change()
>If Text1.Text = "" Then Text1.Text = 0
>End Sub
>That'll sort it out for you.
>Cheers
>Dave

>> I am using a textbox control (let's call it Text1) data
>> bound to an ADO Data Control (let's call it MyAdoDC).
>> MyAdoDC is connected to a SQL Server through the
provider
>> SQLOLEDB. The recordsource for MyAdoDC is a table
called
>> Table1. Table1 contains 4 rows. The field Text1 is
bound
>> is of type Integer. When I run the project and open the
>> form containing MyAdoDC and Text1, I see the bound field
>> value in Text1 without any problem. But when I remove
the
>> contents of Text1 and attempt to navigate forward (to
>> update the field value to null) by clicking on the right
>> arrow on MyAdoDC I get the message 'Operation is
Canceled'
>> and the value doesn't change. However if I change the
>> value in Text1 to a different number (and not blank) the
>> operation goes fine. I think this is a bug in ADO.
Does
>> anyone think otherwise?

>.

by **Dave War** » Sat, 08 Jun 2002 15:47:24

> Hi Dave,
> Thanks for the response. I have quite a few questions about this
> behavior of ADO. In your response you said that when I clear the text
> box and try to update an integer field, I am trying to send an empty
> string which ADO doesn't accept. Then how do I update an integer field
> with null using a textbox? Look at this from the background that a
> character field is updated to a null when the contents of the textbox
> bound to it are cleared and update method is executed. Setting

A character field is not updated to null as suggested above. It started off
as null before data was entered into it. After text is entered it contains a
string. If the string is 'removed' it does not revert back to null, but to a
zero length string which is different to null.

Quote:
> Text1.Text=0 when Text1 is empty is not a solution in most cases since a
> developer might want a null in the field and not 0 since a zero will
> interfere with computed statistics on this field.

If 0 interferes with computed statistics on the field null will be a bigger
problem, cause if null is present in the field and you try to compute stats
on it it will always return null.
I never update a field to null, but rather do a data validation through a
sub checking the data type for each field. If no value is required, insert a
zero length string or 0 value, depending on the data type.
Cheers
Dave

Quote:
> Imtiaz

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

by FZ » Sat, 08 Jun 2002 22:45:11

Imtiazur,
Wrong question (you wrote):
This is definitely a bug. What you are suggesting is a workaround.
How does one update an integer field containing a non-null value to null
using adodc.recordset.update method?

I think you are confusing two design levels here.
1) The text box control's behavior
a) how empty strings are represented intranally (this is the issue of a
zero length string)
b) this object represents numbers as string of text (which must be
programmatically inturpreted)
c) how the text box field is bound to the ADO record set
AND
2) ADO record sets and their relation to the underlying DBMS.

Your text box control has NO IDEA IN THE WORLD what a null string is. It
was probably developed long before ADO was invented.
Is a zero length string representation of a number a database NULL, an
invalid entry, or what. How would ADO know your intensions.
Your ADO record set does know what a NULL is and can represent one
internally.

Your problem, as a developer, is to bridge the gap between the two objects
(this is what programmers do, right?). You need to determine if a blank
string (zero length string) represents a NULL,
(and if so)
capture that state-change
programmatically change the ADO record set value from zero length string to
a NULL (there is way to do this, of course)
and then allow the ADO update method to do it's thing.

This is not a work around, it is programming.

> This is definitely a bug. What you are suggesting is a
> workaround. How does one update an integer field
> containing a non-null value to null using
> adodc.recordset.update method?

> Imtiaz
> >-----Original Message-----
> >It could be a bug, but I don't think so. Text boxes hold
> text. If you
> >remove all the text, you are not changing the value to
> null, but to a
> >zero length string. If you are trying to update a field
> of integer data
> >type, you've got conflict with a string data type. What
> would be better
> >to do is use the Text1_Change event to catch the problem
> >eg
> >Private Sub Text1_Change()
> >If Text1.Text = "" Then Text1.Text = 0
> >End Sub
> >That'll sort it out for you.
> >Cheers
> >Dave

> >> I am using a textbox control (let's call it Text1) data
> >> bound to an ADO Data Control (let's call it MyAdoDC).
> >> MyAdoDC is connected to a SQL Server through the
> provider
> >> SQLOLEDB. The recordsource for MyAdoDC is a table
> called
> >> Table1. Table1 contains 4 rows. The field Text1 is
> bound
> >> is of type Integer. When I run the project and open the
> >> form containing MyAdoDC and Text1, I see the bound field
> >> value in Text1 without any problem. But when I remove
> the
> >> contents of Text1 and attempt to navigate forward (to
> >> update the field value to null) by clicking on the right
> >> arrow on MyAdoDC I get the message 'Operation is
> Canceled'
> >> and the value doesn't change. However if I change the
> >> value in Text1 to a different number (and not blank) the
> >> operation goes fine. I think this is a bug in ADO.
> Does
> >> anyone think otherwise?

> >.

by **Imtiazur Sye** » Sat, 08 Jun 2002 23:36:11

Thanks for your reply. I understand the empty strings and nulls. But I
think that ADO shows dual behavior in this respect. When you are
entering a new record and leave the integer field blank, the value for
that field goes in as null when Update method is executed on the
recordset. But when updating it (apparently) thinks that a blank field
is an empty string and does not update. Added to this fact that a
string field when updated from a non-zero length string to a zero length
string is updated as null.

Imtiaz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Very Computer

Is this a bug in ADO?

Is this a bug in ADO?

Is this a bug in ADO?

Is this a bug in ADO?

Is this a bug in ADO?

Is this a bug in ADO?

Is this a bug in ADO?

Is this a bug in ADO?