ADO, "0001-01-01" Date value causes error 80040e21 when reading 
Author Message
 ADO, "0001-01-01" Date value causes error 80040e21 when reading

I have an ADO application accessing a Teradata Database. I am
connecting using ADO2.6 using the Microsoft OLE DB Provider for ODBC
Drivers. It is quite common and valid to have date values in our
database with the value "0001-01-01". Whenever I try to read a field
with this date value in it I get the run time error:

80040e21 Multiple-step OLE DB operation generated errors. Check each
OLE DB status value, if available. No work was done.

I done extensive searching of newsgroups, the msdn site and the rest
of the web trying to find a solution to this problem. I have found
many sites offering help for this particular error message but none of
them are helpful for my particular problem. I am guessing that this
error is caused because Microsoft didn't cater for dates this low.
What I am asking is, Is there a work around for this problem? eg. can
I tell ado to treat all dates as Chars or something similar.



Sun, 18 Apr 2004 08:08:52 GMT
 ADO, "0001-01-01" Date value causes error 80040e21 when reading

Hi,

Can you open recordset or you even cannot do that? Could
you please post your code which reads value from that
field?

Thanks,

Val

Quote:
>-----Original Message-----
>I have an ADO application accessing a Teradata Database.
I am
>connecting using ADO2.6 using the Microsoft OLE DB
Provider for ODBC
>Drivers. It is quite common and valid to have date values
in our
>database with the value "0001-01-01". Whenever I try to
read a field
>with this date value in it I get the run time error:

>80040e21 Multiple-step OLE DB operation generated errors.
Check each
>OLE DB status value, if available. No work was done.

>I done extensive searching of newsgroups, the msdn site
and the rest
>of the web trying to find a solution to this problem. I
have found
>many sites offering help for this particular error
message but none of
>them are helpful for my particular problem. I am guessing
that this
>error is caused because Microsoft didn't cater for dates
this low.
>What I am asking is, Is there a work around for this
problem? eg. can
>I tell ado to treat all dates as Chars or something
similar.
>.



Mon, 19 Apr 2004 02:07:10 GMT
 ADO, "0001-01-01" Date value causes error 80040e21 when reading
I can open the recordset, I can read everything else including this
column for other records where the date is a modern date. Any date
field below a certain value (I haven't bothered to find out what this
value is) seems to have this problem. I have checked the type of Field
and it comes back as type "adDBDate".

Here is an extract of the code. I am trying to copy the recordset to a
temporary recordset. I have checked and it is not a problem with
adoTempRec because I still get the error when I do a "debug.print
adoRec(i)"

Here is the connection string:
Public Const adoConnStr As String = "Provider=MSDASQL.1;Extended
Properties=""DRIVER=Teradata;DBCNAME=pcop1;UID=;PWD=;DATABASE="""

And the part of the code that has the error:
            Set adoRec = New ADODB.Recordset
            blnExecuteComplete = False
            adoRec.Open sqlExec, adoConn, , , adAsyncExecute

            While Not blnExecuteComplete
                DoEvents
            Wend

            If blnSQLError Then
                Exit For
            End If

            Set adoTempRec = CopyRecordSet(adoRec)

            While Not adoRec.EOF And Not frmStatus.blnCancel
                adoTempRec.AddNew
                For i = 0 To adoRec.Fields.Count - 1
                    adoTempRec(i) = adoRec(i)
                Next i
                adoTempRec.Update
                adoRec.MoveNext
                DoEvents
            Wend
            adoRec.Close
            Set adoRec = Nothing
            Set adoRec = adoTempRec

Thanks to anyone who can help

Jason

Quote:

> Hi,

> Can you open recordset or you even cannot do that? Could
> you please post your code which reads value from that
> field?

> Thanks,

> Val

> >-----Original Message-----
> >I have an ADO application accessing a Teradata Database.
>  I am
> >connecting using ADO2.6 using the Microsoft OLE DB
>  Provider for ODBC
> >Drivers. It is quite common and valid to have date values
>  in our
> >database with the value "0001-01-01". Whenever I try to
>  read a field
> >with this date value in it I get the run time error:

> >80040e21 Multiple-step OLE DB operation generated errors.
>  Check each
> >OLE DB status value, if available. No work was done.

> >I done extensive searching of newsgroups, the msdn site
>  and the rest
> >of the web trying to find a solution to this problem. I
>  have found
> >many sites offering help for this particular error
>  message but none of
> >them are helpful for my particular problem. I am guessing
>  that this
> >error is caused because Microsoft didn't cater for dates
>  this low.
> >What I am asking is, Is there a work around for this
>  problem? eg. can
> >I tell ado to treat all dates as Chars or something
>  similar.
> >.



Tue, 20 Apr 2004 06:58:54 GMT
 ADO, "0001-01-01" Date value causes error 80040e21 when reading

January 1st of the year 100 is the earliest "date" in most of the
COM/Automation world of Windows, in which ADO is included.  This explains
the reason for the error messages.

If it's not possible to convert the source data -- assuming the 1/1/1 value
means "no date value entered", one meaningless date ought to be as good as
another -- then your only alternative seems to be checking for the invalid
value and converting it in your SQL SELECT statement itself, before ADO
attempts to fill the recordset.  A CASE clause would be used for this
purpose with SQL Server; you would need to check whether your database
supports this syntax.

This posting is provided AS IS with no warranties, and confers no rights.



Wed, 21 Apr 2004 06:32:33 GMT
 ADO, "0001-01-01" Date value causes error 80040e21 when reading
Thanks for the response. I thought this might be the case. The problem
I have however is that any piece of SQL could be run against the app
by users so it is not practicle to tell people that they have to put
case statements in all of their SQL for date fields. I guess I will
trap the error and if the error occurs against a date field, I will
output the date "0001-01-01". It's a bit of a cludge but what else can
I do.


Quote:
> January 1st of the year 100 is the earliest "date" in most of the
> COM/Automation world of Windows, in which ADO is included.  This explains
> the reason for the error messages.

> If it's not possible to convert the source data -- assuming the 1/1/1 value
> means "no date value entered", one meaningless date ought to be as good as
> another -- then your only alternative seems to be checking for the invalid
> value and converting it in your SQL SELECT statement itself, before ADO
> attempts to fill the recordset.  A CASE clause would be used for this
> purpose with SQL Server; you would need to check whether your database
> supports this syntax.

> This posting is provided "AS IS" with no warranties, and confers no rights.



Thu, 22 Apr 2004 16:47:59 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Help, How Insert "0001-01-01" in Date Field ???

2. Error reading date '0001-01-01' on DB2

3. Select by job date stopped working on 13/01/01

4. Input date of 01-JAN-99 Defaults to 01-JAN-2099

5. Command mdate={01/01/1950} errors with "ambigous" error

6. Empty string in date field gives me "01-01-1900"

7. datepart(wk, "01-01-2000") returns week 53

8. insert NULL into SQL database and get "01/01/1900" in the field

9. Heeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeelp - vb.txt [01/01]

10. Putting Text into Grid - dbase.nx [01/01]

11. **MAKING MONEY FOR YOU OWN DATABASE - a.txt [01/01]

12. Importing a text file - Smallctx.txt [01/01]


 
Powered by phpBB® Forum Software