Multi-step error when setting NULL

[Pat]

Hi:

I am using ADO2.6 service pack 2,
I developed a customized TEXTBOX control wrapper over
Microsoft textbox. I connect an ADO datasource to this
textbox.recordset.

It works if I changed the value to a no-null value field,
but if the new Value is null, system give me an multi-step
error. I am confused because it works if the new value is
not null. If I set it to empty, it leave the value as '12/30/1899'

Here is my statement.
if text1.text <> originalValue
If Trim(Text1.Text) <> "" Then
recs.Fields(Field_ID) = Text1.Text 'works
Else
Dim x As Variant
x = Null
recs.Fields(Field_ID) = x 'not work if I run
recs.Update later
End If

[Michael Culley]

I'd say you need a function like this

Function EmptyStringToNull(Value as String) as Variant
If Len(Value)=0 then
EmptyStringToNull=Null
Else
EmptyStringToNull = Value
End If
End function

then use it when setting the value to the recordset

rs!SomeField = EmptyStringToNull(MyTextBox.Text)

--
Michael Culley
www.vbdotcom.com


"Pat" <pwang@uclink.berkeley.edu> wrote in message
news:3d3f446e$1@10.1.10.29...
>
> Hi:
>
> I am using ADO2.6 service pack 2,
> I developed a customized TEXTBOX control wrapper over
> Microsoft textbox. I connect an ADO datasource to this
> textbox.recordset.
>
> It works if I changed the value to a no-null value field,
> but if the new Value is null, system give me an multi-step
> error. I am confused because it works if the new value is
> not null. If I set it to empty, it leave the value as '12/30/1899'
>
> Here is my statement.
> if text1.text <> originalValue
> If Trim(Text1.Text) <> "" Then
> recs.Fields(Field_ID) = Text1.Text 'works
> Else
> Dim x As Variant
> x = Null
> recs.Fields(Field_ID) = x 'not work if I run
> recs.Update later
> End If
>

[Pat]

Thanks for the reply, Michael. But it still didnot work.
I can set the NULL value correctly, but the update method will cause
an EmptyStringNullValue error.

"Michael Culley" <mculley@optushome.com.au> wrote:
>I'd say you need a function like this
>
>Function EmptyStringToNull(Value as String) as Variant
> If Len(Value)=0 then
> EmptyStringToNull=Null
> Else
> EmptyStringToNull = Value
> End If
>End function
>
>then use it when setting the value to the recordset
>
>rs!SomeField = EmptyStringToNull(MyTextBox.Text)
>
>--
>Michael Culley
>www.vbdotcom.com
>
>
>"Pat" <pwang@uclink.berkeley.edu> wrote in message
>news:3d3f446e$1@10.1.10.29...
>>
>> Hi:
>>
>> I am using ADO2.6 service pack 2,
>> I developed a customized TEXTBOX control wrapper over
>> Microsoft textbox. I connect an ADO datasource to this
>> textbox.recordset.
>>
>> It works if I changed the value to a no-null value field,
>> but if the new Value is null, system give me an multi-step
>> error. I am confused because it works if the new value is
>> not null. If I set it to empty, it leave the value as '12/30/1899'
>>
>> Here is my statement.
>> if text1.text <> originalValue
>> If Trim(Text1.Text) <> "" Then
>> recs.Fields(Field_ID) = Text1.Text 'works
>> Else
>> Dim x As Variant
>> x = Null
>> recs.Fields(Field_ID) = x 'not work if I run
>> recs.Update later
>> End If
>>
>
>

[Pat]

Thanks for the reply, Michael. But it still didnot work.
I can set the NULL value correctly, but the update method will cause
an Multi-step error.

"Michael Culley" <mculley@optushome.com.au> wrote:
>I'd say you need a function like this
>
>Function EmptyStringToNull(Value as String) as Variant
> If Len(Value)=0 then
> EmptyStringToNull=Null
> Else
> EmptyStringToNull = Value
> End If
>End function
>
>then use it when setting the value to the recordset
>
>rs!SomeField = EmptyStringToNull(MyTextBox.Text)
>
>--
>Michael Culley
>www.vbdotcom.com
>
>
>"Pat" <pwang@uclink.berkeley.edu> wrote in message
>news:3d3f446e$1@10.1.10.29...
>>
>> Hi:
>>
>> I am using ADO2.6 service pack 2,
>> I developed a customized TEXTBOX control wrapper over
>> Microsoft textbox. I connect an ADO datasource to this
>> textbox.recordset.
>>
>> It works if I changed the value to a no-null value field,
>> but if the new Value is null, system give me an multi-step
>> error. I am confused because it works if the new value is
>> not null. If I set it to empty, it leave the value as '12/30/1899'
>>
>> Here is my statement.
>> if text1.text <> originalValue
>> If Trim(Text1.Text) <> "" Then
>> recs.Fields(Field_ID) = Text1.Text 'works
>> Else
>> Dim x As Variant
>> x = Null
>> recs.Fields(Field_ID) = x 'not work if I run
>> recs.Update later
>> End If
>>
>
>

[Steve]

It sounds like the provider is indicating that the database doesn't accept
null values.

If you are using Access, open the table and make sure "Allow Zero Length
String" is set to true. If another DB, ensure it can accept null values.

Steve.

"Pat" <pwang@uclink.berkeley.edu> wrote:
>
>Hi:
>
>I am using ADO2.6 service pack 2,
>I developed a customized TEXTBOX control wrapper over
>Microsoft textbox. I connect an ADO datasource to this
>textbox.recordset.
>
>It works if I changed the value to a no-null value field,
>but if the new Value is null, system give me an multi-step
>error. I am confused because it works if the new value is
>not null. If I set it to empty, it leave the value as '12/30/1899'
>
>Here is my statement.
> if text1.text <> originalValue
> If Trim(Text1.Text) <> "" Then
> recs.Fields(Field_ID) = Text1.Text 'works
> Else
> Dim x As Variant
> x = Null
> recs.Fields(Field_ID) = x 'not work if I run
>recs.Update later
> End If
>

[Joe \Nuke Me Xemu\ Foster]

"Pat" <pwang@uclink.berkeley.edu> wrote in message <news:3d3f446e$1@10.1.10.29>...

> I am using ADO2.6 service pack 2,
> I developed a customized TEXTBOX control wrapper over
> Microsoft textbox. I connect an ADO datasource to this
> textbox.recordset.
>
> It works if I changed the value to a no-null value field,
> but if the new Value is null, system give me an multi-step
> error. I am confused because it works if the new value is
> not null. If I set it to empty, it leave the value as '12/30/1899'
>
> Here is my statement.
> if text1.text <> originalValue
> If Trim(Text1.Text) <> "" Then
> recs.Fields(Field_ID) = Text1.Text 'works
> Else
> Dim x As Variant
> x = Null
> recs.Fields(Field_ID) = x 'not work if I run recs.Update later
> End If

This might be the same bug from VB3's Data Control that was never fixed,
and the workaround is likely to be similar. Do you have to use a Data
Control? It's easier to roll your own than to work around Microslop's
many "quirks", and you can use .Tag to "bind" just about *any* control.

--
Joe Foster <mailto:jlfoster%40znet.com> "Regged" again? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!