Solved

VB is unable to handle DB2 date = 0001-01-01

Posted on 2003-10-31
19
1,247 Views
Last Modified: 2011-09-20
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.

Mark

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
0
Comment
Question by:onthemark
  • 8
  • 7
  • 3
19 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9662377
Hi onthemark,

please show us the actual sql stm.

do a
debug.print  sSql
and post the results..


Cheers!
0
 

Author Comment

by:onthemark
ID: 9663257
Hi Lowfatspread,

I'll have to wait till Monday to post the actual sql statement as I can't run the code offsite.  The dbfield_ft, dbfield_ta, dbfield_cn, dbfield_tye are declared constants that are assigned to the db2 table fields as follows:

private const DBFIELD_BN = "BATCH_NUM"
private const DBFIELD_FT = "FORM_TP"
private const DBFIELD_TA = "TAX_ACT"
private const DBFIELD_CN = "CLIENT_NUM"
private const DBFIELD_TYE = "TAXN_END_DT"

TABLE_BATCH is another constant that contains the name of the DB2 table.  It is declared as follows:
Private const TABLE_BATCH  = "ritbpdb.enit"

The Batch.BatchPages(1).BatchPageIndexes(1).Value returns the batch number of the current batch.  This number has been keyed by the operator and validated against the TABLE_BATCH table.  

The "AND CHQ_AMT=0" excludes entries for items (cheques) that are not in the batch being processed.

So.... the "ssql = " statement in my original post results in the following:
SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,TAXN_END_DT FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;

Using the CHAR() function the sql statement is as follows:
SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,CHAR('TAXN_END_DT') FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;

This particular query returns 21 records.  9 of which have a TAXN_END_DT of 0001-01-01.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9665110
ok ,
I wanted to set the statement, really just to confirm you hadn't got any syntax type problems...

both your examples look ok....
although I'm not sure why you have quotes around the TAXN_END_DT in the char example...

have you considered

using
 case when taxn_endt_dt = '0001-01-01' then null else taxn_endt_dt end as TAXN_END_DT  
in the select...

0
 

Author Comment

by:onthemark
ID: 9666226
Hi Lowfatspread,
Good suggestion.  I'll give it a try on Monday.  I'm not sure why I put the quotes in the char statement either.  I was just typing from memory.

Mark
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9671475

Can you try having the default date later than 1756 or null as lowfatspread has told.  Most probably the problem is DB2 allows default date as low as 0001-01-01 which no other databases accept.

Just try and let us know..
0
 

Author Comment

by:onthemark
ID: 9673832
The problem is definitely that 0001-01-01 is a valid BD2 date but not a VB date.  I can't change the default.  

I tried using the case statement as suggested by lowfatspread but as with the char statement, the taxn_end_dt field is still not accessible.  
Any attempt to reference the field in the recordset (e.g. if rssearch("TAXN_END_DT") = NULL results in an "Item cannot be found in the collection corresponding to the requested name or ordinal" error.

It appears that the taxn_end_dt has already been typed as a "date" field (at connection time? and any field value that is not seen as a date throws an error.

Is there another way of making the db2 records accessible from VBA?

For now I've asked for clarification of the business rules resulting in 0001-01-01 dates.... hopefully I can work around the issue based on form_tp.  It'll be ugly but may be an out.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9673914
can you create a view on the table
which includes the case statement to either nullify or move the date when its a default?


Just noticed we haven't both missed a T have we TAXN_END_DT  
                                                                               TAXN_ENDT_DT  
?
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9673944
In the recordset, check for the value of the taxn_end_dt and if found equal to 0001-01-01 then do some thing else do something else..
if rssearch("TAXN_END_DT") = '0001-01-01' should be the condition. or try if rssearch("TAXN_END_DT").value = '0001-01-01'

You need to explore the functions in recordset.. Once you get the correct function then you are done. Will let you know once it works for me.

Thanks

0
 
LVL 2

Expert Comment

by:askanivg
ID: 9674162
I could create the situation you are facing and here is the solution for your problem


SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,TAXN_END_DT FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;
 has to be changed

SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,cast (TAXN_END_DT as char(10)) FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;


Thanks
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Expert Comment

by:askanivg
ID: 9674187
Once the above statement works, the record set will have the taxn_end_dt with string data type. Here you can perform any operation. As already discussed, the problem was the mismatch between the minimum dates allowed in DB2 and Microsoft driver.

hth
0
 

Author Comment

by:onthemark
ID: 9674660
TAXN_END_DT is the correct field name.

If I use the Cast function the same error occurs as with using either Char(taxn_end_dt) or a case statement (as innoted in previous posts).

The error is :
   "Item cannot be found in the collection corresponding to the requested name or ordinal"

This error occurs if the rssearch("TAXN_END_DT") field is referenced in a any way.  I can't process based on the value of this field as the error occurs whenever the field is referenced.

I'm pushing to see about the likelihood of getting the default changed.



0
 
LVL 2

Expert Comment

by:askanivg
ID: 9674732
Mark,

Your sql in the VB code is

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

Here you have to modify either the above query or the below definition you have.
private const DBFIELD_TYE = "TAXN_END_DT"

Basically you are not casting the date type to char(10). Please do that.. Have a simple hard-coded sSQL variable as i have mentioned above and test the result. This should work and then you can have this code of passing the pre-defined variables. I have tried the same and got the same error. Then did the casting and i could compare with "0001-01-01" and then i could do what ever. The comparison has to be done in recordset level. But the sql statement has to be modified to return character and not date. Refer to my above query.. or as below..

SELECT BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM,cast (TAXN_END_DT as char(10)) FROM RITBPDB.ENIT
  WHERE BATCH_NUM = '19100' AND CHQ_AMT=0
  ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;

 and then in recordset do the comparison. It worked for me. Please check..

Thanks
0
 

Author Comment

by:onthemark
ID: 9680155
Askanivg,

I think I have done what you suggested but still get errors.

I created a simple VB test app with one form consisting of a listbox and 2 buttons.  
Button1 opens the connection to the DB2 table, constructs the sql for batch_num='19100', executes the sql then loops through the record set adding the field values of each to the listbox.  

Button2 closes the db2 connection and frees resources.

To simplify the sql string construction and eliminate potential for any oddities the sql statement has the table and field names hard-coded.

If the item being added to the listbox has a TAXN_END_DT of "0001-01-01"  then the following error occurs:
"Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal."
If TAXN_END_DT = an actual date everything works.

The code is below.

Is there something I'm missing?

****** Start of VB Test **********************************
Option Explicit

'Data access constants
Private Const DSN = "db2p"
Private Const UID = "userid"
Private Const PWD = "password"
Private Const TABLE_BATCH = "ritbpdb.enit"

'Fields used for DB2 reconciliation queries
Private Const DBFIELD_BN = "BATCH_NUM"
Private Const DBFIELD_CN = "CLIENT_NUM"
Private Const DBFIELD_FT = "FORM_TP"
Private Const DBFIELD_TA = "TAX_ACT"
Private Const DBFIELD_TYE = "TAXN_END_DT"

Private mConDB As Connection
Private sSql As String
Private rsSearch As Recordset
Private Temp As String

Private Sub Command1_Click()

 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 BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM," & _
        "CAST(TAXN_END_DT AS CHAR(10)) " & _
        "FROM RITBPDB.ENIT WHERE BATCH_NUM = '19100' AND CHQ_AMT=0 " & _
        "ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;"

 Set rsSearch = New Recordset
 rsSearch.Open sSql, mConDB ' get the record set

 While (rsSearch.BOF = False And rsSearch.EOF = False)
   List1.AddItem (rsSearch("FORM_TP") & rsSearch("TAX_ACT") & rsSearch("CLIENT_NUM") & "    " & rsSearch("TAXN_END_DT"))
 Wend

 rsSearch.Close
 Set rsSearch = Nothing

End Sub

Private Sub Command2_Click()
 mConDB.Close
 Set mConDB = Nothing
 ' free resources
 Unload Form1
End Sub
0
 
LVL 2

Accepted Solution

by:
askanivg earned 250 total points
ID: 9680381
'   List1.AddItem (rsSearch("FORM_TP") & rsSearch("TAX_ACT") & rsSearch("CLIENT_NUM") & "    " & rsSearch("TAXN_END_DT"))
Above sentence should change to ..
'list1.AddItem (rsSearch.Fields(0).Value)  

Also you need to have rssearch.movefirst before doing the while loop.. and also have
rsSearch.MoveNext in the while loop.

I have copied yours and changed some lines to link to my table and database and by making the above changes it works for me.. Please check below..


'****** Start of VB Test **********************************
Option Explicit

'Data access constants
Private Const DSN = "dsn_name"
Private Const UID = "userid"
Private Const PWD = "password"
Private Const TABLE_BATCH = "ritbpdb.enit"

'Fields used for DB2 reconciliation queries
'Private Const DBFIELD_BN = "BATCH_NUM"
'Private Const DBFIELD_CN = "CLIENT_NUM"
'Private Const DBFIELD_FT = "FORM_TP"
'Private Const DBFIELD_TA = "TAX_ACT"
'Private Const DBFIELD_TYE = "TAXN_END_DT"
Private Const DBFIELD_TYE = "LST_DUPDT_TS"

Private mConDB As Connection
Private sSql As String
Private rsSearch As Recordset
Private Temp As String

Private Sub Command1_Click()
 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 BATCH_NUM,FORM_TP,TAX_ACT,CLIENT_NUM," & _
        "CAST(TAXN_END_DT AS CHAR(10)) " & _
        "FROM RITBPDB.ENIT WHERE BATCH_NUM = '19100' AND CHQ_AMT=0 " & _
        "ORDER BY FORM_TP,TAX_ACT,CLIENT_NUM;"
sSql = "select cast(date('01/01/0001') as char(10)) from btadprd.btt_office"

 Set rsSearch = New Recordset
 rsSearch.Open sSql, mConDB ' get the record set
rsSearch.MoveFirst
 While (rsSearch.BOF = False And rsSearch.EOF = False)
'   List1.AddItem (rsSearch("FORM_TP") & rsSearch("TAX_ACT") & rsSearch("CLIENT_NUM") & "    " & rsSearch("TAXN_END_DT"))
list1.AddItem (rsSearch.Fields(0).Value)  ' this displays 01-01-0001 for me 12 times as i am selecting this value only.
rsSearch.MoveNext
 Wend

 rsSearch.Close
 Set rsSearch = Nothing

End Sub

Private Sub Command2_Click()
 mConDB.Close
 Set mConDB = Nothing
 ' free resources
 Unload Form1
End Sub

0
 

Author Comment

by:onthemark
ID: 9682146
Askanivg,

It works. Excellent hlp my fired, greatly appreciated.
I'm new to experts exchange.
What is the process for giving you full points?
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9682179
Onthemark,

I too and new and i donno how you give full points to me.

Well its works and thats great.. Congratulations.............

Thanks...

0
 

Author Comment

by:onthemark
ID: 9682282
So i can't type...
I meant excellent help my friend.
0
 
LVL 2

Expert Comment

by:askanivg
ID: 9682318
Thanks and here you go .. i got the details from help..

There are two kinds of points at Experts Exchange: Question points and Expert points, and they have little relationship to each other. As a member, you receive five Question points every day with which to ask questions. When you ask a question, the points are removed from your Available points total into Escrow. Once you've accepted a comment as an answer, the points are removed from Escrow; if your question is deleted, they are returned to your Available total.

If you are a Premium Services member, then your available points total never decreases (it just increases by 5 points per day), no matter how many questions you ask and how many points the questions are worth.

When you accept a comment as an answer, the Question points are multiplied by a factor depending on the grade you give and added to the Expert point total of the person who answered your question. Giving a higher grade does not affect your Question points. For more information regarding grades, please look at What's the right grade to give.

The points you offer for a question are to be a reward for getting assistance from Experts; they are not necessarily an indication of the "value" of the answer. For instance, you may want a very simple question answered very quickly; one way to get your question attended to is to offer more points for it -- but it's still a simple question. For more information, see the For more tips on how to grade, please look at The Member page.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

16 Experts available now in Live!

Get 1:1 Help Now