Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1846
  • Last Modified:

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.

0
jfpeter
Asked:
jfpeter
  • 6
  • 4
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now