Multi-step error when setting NULL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Multi-step error when setting NULL

  1. #1
    Pat Guest

    Multi-step error when setting NULL


    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


  2. #2
    Michael Culley Guest

    Re: Multi-step error when setting NULL

    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
    >




  3. #3
    Pat Guest

    Re: Multi-step error when setting NULL


    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
    >>

    >
    >



  4. #4
    Pat Guest

    Re: Multi-step error when setting NULL


    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
    >>

    >
    >



  5. #5
    Steve Guest

    Re: Multi-step error when setting NULL


    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
    >



  6. #6
    Joe \Nuke Me Xemu\ Foster Guest

    Re: Multi-step error when setting NULL

    "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!



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center