Solved

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

Posted on 2003-11-21
10
1,817 Views
Last Modified: 2007-12-19
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
Comment
Question by:jfpeter
  • 6
  • 4
10 Comments
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9797685
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
 

Author Comment

by:jfpeter
ID: 9797755
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
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9797879
> 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
 

Author Comment

by:jfpeter
ID: 9798029
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
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9798190
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:jfpeter
ID: 9798296
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
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9798339
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
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9798434
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
 
LVL 7

Accepted Solution

by:
wsteegmans earned 250 total points
ID: 9798482
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
 

Author Comment

by:jfpeter
ID: 9799015
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now