VB is unable to handle DB2 date = 0001-01-01
Posted on 2003-10-31
From VBA I'm accessign a MVS DB2 table using the IBM DB2 ODBC Driver. I construct a SQL statement then execute the statement and store the returned recordset.
The connection and sql results are correct.
The problem I am experiencing is that one field, DBFIELD_TYE, is a date. If the value of dbfield_tye is an actual date then everything is ok. However, the default value for this field is "0001-01-01". This default value is used whenever a date is not supplied.
The following runtime error occurs whenever rssearch(dbfield_tye) = 0001-01-01
"Multiple-step OLE DB operation generated errors. Check each OL# DB status value, if available. No work was done."
The connect steps and sql are included below.
I've tried using CHAR(dbfield_tye) within the SQL to see if I could force VB to treat the field as a string and have tried casting the date to a string before referencing it but nothing works.
Has anyone seen this behaviour. Any suggestions welcome.
Set mConDB = New Connection
mConDB.Open "DSN=" & DSN & "; UID=" & UID & "; PWD=" & PWD
'Construct sql to return one record for each document expected in the batch
sSql = "SELECT " & DBFIELD_BN & "," & DBFIELD_FT & "," & DBFIELD_TA & "," & DBFIELD_CN & "," & DBFIELD_TYE & " FROM " & TABLE_BATCH & " WHERE " & DBFIELD_BN & " = '" & Batch.BatchPages(1).BatchPageIndexes(1).Value & "' AND CHQ_AMT=0 ORDER BY " & DBFIELD_FT & "," & DBFIELD_TA & "," & DBFIELD_CN
Set rsSearch = New Recordset
rsSearch.Open sSql,mConDB ' get the record set