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,812 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

746 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

12 Experts available now in Live!

Get 1:1 Help Now