Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: SQLCODE: -180 The DATE, TIME, OR TIMESTAMP value is invalid

    Friends,

    This is my first post and I'm new to DB2. Hopefully somebody would be able to point me in the right direction. Before I post my problem here is a snapshot of the environment:
    IBM DB2 Universal Database Version 8
    Level: DB2 v8.1.9.700
    Build level: s050422
    Product identifier: SQL08022

    Problem:
    This is an old application which needs some repair work. Language used is ASP(VB6) and database is DB2. I'm trying to display date fields and update them with a submit button. The Date columns in DB2 are of type DATE. There is also a TIMESTAMP column to determine the exact time of the update which is also updated with the submit button. To display the dates presently i'm using a function called GetRs. The query goes like this:

    strSql = "SELECT a.PNUM, CASE CHAR(a.REF_DT) WHEN '0001-01-01' THEN '' ELSE CHAR(a.REF_DT) END R_DT , "
    and so forth

    The default values in the DATE columns are 1-01-01 and notnull.

    The display(GetRs) works ok. But when I try to update the dates, the Recordset.UpdateBatch throws error - SQLCODE 180. And I think this is due to the incorrect DATE/CHAR conversion that I've implemented.

    The client side script is designed to pass the date values in the format of 'Year-Month-Day' and this format is passed to the sql string.
    I searched this forum and found few similar threads but couldn't implement them in my code.
    I've tried using CAST, DATE(), DT() etc from the page below -
    http://www.dbforums.com/db2/1617017-...query-db2.html

    I would appreciate it if somebody could look into the issue and point me in the right direction. If you need more information please let me know.

    Thanks,
    Optimus

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why did you choose to post the query that works, but not the statement that fails? Is this some sort of a game where we get to guess what the update statement looks like?

  3. #3
    Join Date
    Sep 2011
    Posts
    5
    No. There was no "wild guess game" intended. The query works fine independently. But my question was related to conversion. Since the Date columns in DB2 are of type DATE my interpretation from the error was that I'm not converting to the DATE format properly. I'm using CHAR in the query.
    Code:
    Public Function UpdateABCDRs(adoABCDrs As Variant, ByVal lngPNUM As Long, _
                                    Optional ByVal strUserID As String) As Boolean
    ---
    ---
    adocnConn.BeginTrans
    objABCD.GetRs rsABCD, adocnConn, eABCDAllFields, lngPNUM
    If Not (rsABCD.BOF And rsABCD.EOF) Then
           rsABCD!PNUM = lngPNUM
           rsABCD!REF_DT = adoABCDrs!REF_DT
           ----
           ----
          rsABCD.Update
          objABCD.UpdateRs rsABCD, adocnConn
    Else
          ---
          ---
    End If
       adocnConn.CommitTrans
    GetRs function:-
    Code:
    Public Function GetRs(adoRs As Variant, _
            Optional objConn As Variant, _
            Optional ByVal eABCDvt As EnumABCDViewTypes , _
            Optional ByVal lngPNUM As Long = 0) As Boolean
    
            ----
            ----
            Select Case eABCDvt 
              Case eABCDAllFields
              strSql = "SELECT a.PNUM, CASE CHAR(a.REF_DT) WHEN '0001-01-01' THEN '' ELSE CHAR(a.REF_DT) END R_DT , " 
    ---
    ---


    UpdateRs is a function in ABCD class (I can't modify this function - existing structure)
    the update statement:
    Code:
    Public Function UpdateRs(adoRs As Variant, objConn As Variant) As Boolean
     Dim rData     As DataProcessor
        
        On Error GoTo UpdateRS_ErrorHandler
                
        UpdateRs = False
        
        Set rData = New DataProcessor
        rData.UpdateRecordset adoRs, objConn
        Set rData = Nothing
               
        UpdateRs = True
         
        Exit Function
    Comes here

    Code:
    Public Sub UpdateRecordset(adorsRecs As Variant, objConn As Variant)
    
    Dim objEventLog As Object
    Dim strErrDesc As String, strErrSource As String, lngErrNbr As Long
        On Error GoTo UpdateRecordset_ErrorHandler
            
        With adorsRecs
            Set .ActiveConnection = objConn
            .UpdateBatch adAffectAll      - Fails here. Value of adAffectAll = 3 
        End With
           
        Set adorsRecs.ActiveConnection = Nothing
            
        Exit Sub
    UpdateRecordset_ErrorHandler:
       ----
       ----
    End Sub
    I've tried to put forth all the relevant codes for my problem. If it's still insufficient, please mention it.
    All of the above functions (GetRs & UpdateRs) work perfectly fine when the Date column is of type DECIMAL.

    PS:If the code is insufficient please let me know. Or simply ask what do you need and I'll post it. I'm still finding my through VB6-DB2 environment. It would help me immensely if anyone could point out where I went wrong or what information is actually relevant rather than indulging in wild guessing games .

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by 1optimus View Post
    Code:
    CASE CHAR(a.REF_DT) WHEN '0001-01-01' THEN '' ELSE CHAR(a.REF_DT) END
    Empty string is not a valid representation of a date value. '1-01-01' isn't one either. Check what value you are assigning in "rsABCD!REF_DT = adoABCDrs!REF_DT"

  5. #5
    Join Date
    Sep 2011
    Posts
    5
    Thanks for the quick response. Checked the value assigned to
    rsABCD!REF_DT = adoABCDrs!REF_DT
    Assigned value:
    2002-03-05


    Edit: The purpose of the empty string was to display blank if the default value of the Date column is 0001-01-01
    Code:
    CASE CHAR(a.REF_DT) WHEN '0001-01-01' THEN '' ELSE CHAR(a.REF_DT) END
    The client side script takes the values from a textbox which is split into txtYear, txtMonth & txtDay. There is a specific function which formats/converts all these values to the form 2002-03-05 and this value is passed to the vb6 code. This function can be modified if needed.

    The query below executes ok in the DB2 window.
    UPDATE ABCD_TEST
    SET REF_DT = '2003-03-05', WHERE PNUM = 12345678;

    My idea was to convert the user inputs to the format '2003-02-03'. Is this an invalid way? If so how could I modify the query.

    Thanks
    Last edited by 1optimus; 09-26-11 at 15:39.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by 1optimus View Post
    Assigned value:
    2002-03-05
    Any trailing blanks, by chance?

  7. #7
    Join Date
    Sep 2011
    Posts
    5
    I'm using Trim.

  8. #8
    Join Date
    Sep 2011
    Posts
    5
    Looks like this works.

    Instead of passing Year-Month-Day now it's month/day/year

    If no user input then 01/01/0001

    I'll test it further.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •