Multiple-Step Operation Generated Errors when setting a date value to empty or null in bound control

I have a form with a bunch of bound controls and a few are date fields.  When I try to clear out a date that has been previously set in the record I get the Multiple-Step Operation Error -2147217887(8004e21)  The database allows nulls for these date values, so it is OK at the DB level - looks like the ado is causing the problem.

I found the following on Microsoft's site that is directly related to my problem - looks like there is no workaround according to them.

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q247/2/02.asp&NoWebContent=1

Anyone else have any ideas?  There is to much invested in using the bound controls at this point - I cannot abandon this method.

jfpeterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wsteegmansCommented:
EMPTY is not the same as NULL.

NULL is still a value, EMPTY is not ...
Just try to use NULL instead of EMPTY, because your DB allows NULL-values ...

What type of DataBase are you using? Because MS Access has also some limitations when doing 'complicated' UPDATES/INSERTS.

Can you also poste the update-statement?
0
jfpeterAuthor Commented:
The database is SQL Server 2000

Can't post the update statement itself - this is a form with bound controls.  Once I open up the form, navigate to a record and then try clear the textbox of the date value - that's when I get the error.

If I try to programatically set the value to Null, Empty, "''" etc. I still get the same error.
0
wsteegmansCommented:
> If I try to programatically set the value to Null, Empty, "''" etc. I still get the same error.
Because these are BOUND-Controls, where did put this code?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jfpeterAuthor Commented:
in the change event for the textbox - this was only after receiving the error without doing anything (i.e. letting the default behind the scenes databound behavior do its work)

So - with no code written by me, the error happens, and if I try to put code in the change event like:

MyDateField_Change()
    AdoPrimaryRS("mydatefield")=Null
   'Or this: AdoPrimaryRS("mydatefield")=Empty
   'Or this: AdoPrimaryRS("mydatefield")="''"
End Sub

I still get the same error - I guess the difference is that the error happens on the above line of code, as opposed to on the AdoPrimaryRS.Update line of code in my save button routine.
0
wsteegmansCommented:
Try this:

in the AdoPrimaryRS_WillChangeRecord event, you normally have a Select-Statement, like:
Select Case adReason
  Case ...
  Case adRsnUpdate

In the Case adRsnUpdate, put this code,
  If Me.MyField.Text = "" then
    AdoPrimaryRS.RecordSet.Fields("mydatefield").Value = CDate(Empty)
  End If

The normal Empty should also work.
This works with me, trying with Access, but got first the same error.
0
jfpeterAuthor Commented:
unfortunately, that code produces a value of 12:00:00 AM in the database - not a null, but it was a good try.  I think I could write a trigger in SQL Server 2000 to look for that value, and then set the field to null instead, but that is kind of messy, and would necessitate writing a trigger for any table that has a nullable date field, where the VB program can update records in that table.

I am going to try to install MDAC 2.8 and see if that fixes it
0
wsteegmansCommented:
12:00:00 AM is indeed the EMPTY value for a Date/Time field :-(

But you don't get the error-value any more ...
That's already good news!
0
wsteegmansCommented:
Tried it with MDAC 2.7 -> same result ...
What you also could do ... because our update works now, change your SQL-Statement in AdoPrimaryRS. Replace there all 0:00:00 with nothing (if using SQL 2000, use the CASE statement or something like that)
0
wsteegmansCommented:
To be more exact, what is stored isn't 0:00:00 but 12/30/1899 00:00:00 (change the format of your date field to mm/dd/yyyy hh:nn:ss and you will see!) .

That's the ZERO date of SQL. You can't avoid this, so just change your SELECT statement and everything works fine (after the other changes in the WillChangeRecord event)

Regards.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfpeterAuthor Commented:
Thanks for all the help.  The only problem remaining is that there is code in my ASP pages reading from the same database, that is expecting to see a date (and use that date to do something), or find a null.  I want to use a null value in the DB to denote that no data has been entered (or data has been purposefully set to null)

If I run an update statement and set the date field to null then execute that from VB outright, keeping the bound controls out of the picture, the null will be stored in the DB - not the zero date.

looks like the problem is really with the MS implementation of the update method on a bound control where the field is a datetime type, and there may be no good way to get around it other than to not use the bound controls, or perhaps fudge it by using a trigger or something in the select statement like you suggest above.

Thanks again for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.