Topic
6 replies Latest Post - ‏2013-01-30T06:43:44Z by SystemAdmin
SystemAdmin
SystemAdmin
2826 Posts
ACCEPTED ANSWER

Pinned topic C# & DB2 Express-C Exception SQLSTATE=58005 Year, Month, and Day parameters

‏2013-01-19T14:33:09Z |
Observation: Encountering error - Year, Month, and Day parameters describe an un-representable DateTime. Check InnerException property for more detail. SQLSTATE=58005

Condition: Happens when I execute a Stored-procedure which queries & returns records from a single table based on the parameter provided.

OS: Windows Vista
IDE: Miscrosoft Visual Studio 2005 (C# Windows Application)
Other Information: Kindly have other tables also with these kinds of field layout and I don't find this happening. On checking I came across this link http://support.microsoft.com/kb/926357/en-us?sd=rss&spid=1967. However, it cannot be installed on my system.

Would it be possible for someone to please help me with this.

SAMPLE STRUCTURES

Table Structure Pattern

CREATE TABLE TRANSACTION_MASTER
(
RECORD_ID INTEGER NOT NULL,
DATE_OF_CREATION TIMESTAMP NOT NULL,
DATE_OF_MODIFICATION TIMESTAMP,
DATE_OF_DELETION TIMESTAMP,
LAST_UPDATED_BY VARCHAR(200) NOT NULL,
LAST_UPDATED_FROM VARCHAR(200) NOT NULL,
IS_ACTIVE INTEGER,

INVOICE_DATE DATE,
INVOICE_NO VARCHAR(50),
INVOICE_AMOUNT DECIMAL(30 , 2)
)
DATA CAPTURE NONE
COMPRESS NO;

Stored Procedure Pattern

CREATE PROCEDURE USER.GET_TRANSACTION_MASTERS
(
INOUT VAL_UNIQUE_RECORD_ID INTEGER,
INOUT VAL_DATE_OF_CREATION TIMESTAMP,
INOUT VAL_DATE_OF_MODIFICATION TIMESTAMP,
INOUT VAL_DATE_OF_DELETION TIMESTAMP,
INOUT VAL_LAST_UPDATED_BY VARCHAR(200),
INOUT VAL_LAST_UPDATED_FROM VARCHAR(200),
INOUT VAL_IS_ACTIVE INTEGER,

INOUT VAL_INVOICE_DATE DATE,
INOUT VAL_INVOICE_NO VARCHAR(50),
INOUT VAL_INVOICE_AMOUNT DECIMAL(30 , 2)

)
RESULT SETS 1
LANGUAGE SQL
BEGIN

DECLARE CR_TRANSACTIONMASTERS CURSOR WITH RETURN FOR
SELECT
* FROM
USER.TRANSACTION_MASTER
WHERE
(
(
(
INVOICE_NO = VAL_INVOICE_NO
OR
VAL_INVOICE_NO LIKE VAL_INVOICE_NO
)
AND
VAL_INVOICE_NO IS NOT NULL
)
OR
VAL_INVOICE_NO IS NULL
)
AND
(
(DATE(INVOICE_DATE) = DATE(VAL_INVOICE_DATE) AND VAL_INVOICE_DATE IS NOT NULL)
OR
VAL_INVOICE_DATE IS NULL
)
AND
(
(
INVOICE_AMOUNT = VAL_INVOICE_AMOUNT
AND
VAL_INVOICE_AMOUNT IS NOT NULL
)
OR
VAL_INVOICE_AMOUNT IS NULL
) ;
OPEN CR_TRANSACTIONMASTERS;
END
Updated on 2013-01-30T06:43:44Z at 2013-01-30T06:43:44Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: C# & DB2 Express-C Exception SQLSTATE=58005 Year, Month, and Day parameters

    ‏2013-01-19T20:25:48Z  in response to SystemAdmin
    Content moved to.

    https://www.ibm.com/developerworks/forums/thread.jspa?threadID=468507&tstart=0

    Sorry, it was a mistake from my side. I ended up posting the same thing 4 times. I am sorry about the same.
  • ArchanaSoni
    ArchanaSoni
    8 Posts
    ACCEPTED ANSWER

    Re: C# & DB2 Express-C Exception SQLSTATE=58005 Year, Month, and Day parameters

    ‏2013-01-23T12:54:22Z  in response to SystemAdmin
    Hi Sougandh,

    We have tried using the re-pro you had provided, and unable to re-pro the issue. Hence we would require some inputs from your end:
    1. Which IBM Data Server are you using ?
    2. Would you please provide exact error and call stack ?
    3. Why can't MS fix be installed in your system ?
    4. I could see in your C# re-pro you are binding 12 parameters and in SP definition only 10 parameters are exist. Just wanted to recheck, is it the correct C# re-pro steps ?

    Thanks
    Archana
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: C# & DB2 Express-C Exception SQLSTATE=58005 Year, Month, and Day parameters

    ‏2013-01-24T21:46:10Z  in response to SystemAdmin
    Hi Archana,

    Thanks for your response. Here is all the information that you asked me on the particular topic. Please find the same below.

    ERROR Occurs in the fetch section when it calls the SearchByParameter routine when I am passing in all null values as search parameter


    Details of Database, Development Environment & OS

    OS: Windows Vista(TM) Business SP 2
    Database Version: DB2 UDB V10.1
    Database IDE Version: IBM® Data Studio Version 3.1.1.0, administration client
    Development IDE: Visual Studio 2005
    DB2 Run-Time Client Info
    File Name: IBM.Data.DB2.dll
    Type: Application Extension
    File Version: 10.1.0.2
    Product Version: 10.1.0.2
    Development Code
    Table Structure definition
    
    CREATE TABLE TEST_TABLE ( RECORD_ID INTEGER NOT NULL, DATE_OF_CREATION TIMESTAMP NOT NULL, DATE_OF_MODIFICATION TIMESTAMP, DATE_OF_DELETION TIMESTAMP, LAST_UPDATED_BY VARCHAR(200) NOT NULL, LAST_UPDATED_FROM VARCHAR(200) NOT NULL, IS_ACTIVE INTEGER, INVOICE_DATE DATE, INVOICE_NO VARCHAR(50), INVOICE_AMOUNT DECIMAL(30 , 2) ) DATA CAPTURE NONE COMPRESS NO;   ALTER TABLE TEST_TABLE ADD CONSTRAINT TEST_TABLE_PK PRIMARY KEY (RECORD_ID);
    


    Sequence Structure definition
    
    CREATE SEQUENCE SEQ_TT_PK_1 AS INTEGER  START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20 NO ORDER;
    


    Stored Procedures definition

    Definition 1
    
    CREATE PROCEDURE USER.SAVE_TEST_TABLE ( INOUT VAL_RECORD_ID INTEGER, INOUT VAL_DATE_OF_CREATION TIMESTAMP, INOUT VAL_DATE_OF_MODIFICATION TIMESTAMP, INOUT VAL_DATE_OF_DELETION TIMESTAMP, INOUT VAL_LAST_UPDATED_BY VARCHAR(200), INOUT VAL_LAST_UPDATED_FROM VARCHAR(200), INOUT VAL_IS_ACTIVE INTEGER, INOUT VAL_INVOICE_DATE DATE, INOUT VAL_INVOICE_NO VARCHAR(50), INOUT VAL_INVOICE_AMOUNT DECIMAL(30 , 2) ) DYNAMIC RESULT SETS 0 P1: BEGIN   IF NOT EXISTS (SELECT * FROM USER.TEST_TABLE WHERE RECORD_ID = VAL_RECORD_ID ) THEN SET VAL_RECORD_ID = NEXT VALUE FOR USER.SEQ_TT_PK_1 ; INSERT INTO USER.TEST_TABLE ( RECORD_ID, DATE_OF_CREATION, DATE_OF_MODIFICATION, DATE_OF_DELETION, LAST_UPDATED_BY, LAST_UPDATED_FROM, IS_ACTIVE, INVOICE_DATE, INVOICE_NO, INVOICE_AMOUNT ) VALUES ( VAL_RECORD_ID, VAL_DATE_OF_CREATION, VAL_DATE_OF_MODIFICATION, VAL_DATE_OF_DELETION, VAL_LAST_UPDATED_BY, VAL_LAST_UPDATED_FROM, VAL_IS_ACTIVE ,                    VAL_INVOICE_DATE, VAL_INVOICE_NO, VAL_INVOICE_AMOUNT ); ELSE UPDATE USER.TEST_TABLE SET RECORD_ID = VAL_RECORD_ID, DATE_OF_CREATION = VAL_DATE_OF_CREATION, DATE_OF_MODIFICATION = VAL_DATE_OF_MODIFICATION, DATE_OF_DELETION = VAL_DATE_OF_DELETION, LAST_UPDATED_BY = VAL_LAST_UPDATED_BY, LAST_UPDATED_FROM = VAL_LAST_UPDATED_FROM, IS_ACTIVE = VAL_IS_ACTIVE, INVOICE_DATE = VAL_INVOICE_DATE, INVOICE_NO = VAL_INVOICE_NO, INVOICE_AMOUNT = VAL_INVOICE_AMOUNT WHERE RECORD_ID = VAL_RECORD_ID;   END IF; END P1
    

    Definition 2
    
    CREATE PROCEDURE USER.GET_TEST_TABLE ( INOUT VAL_RECORD_ID INTEGER, INOUT VAL_DATE_OF_CREATION TIMESTAMP, INOUT VAL_DATE_OF_MODIFICATION TIMESTAMP, INOUT VAL_DATE_OF_DELETION TIMESTAMP, INOUT VAL_LAST_UPDATED_BY VARCHAR(200), INOUT VAL_LAST_UPDATED_FROM VARCHAR(200), INOUT VAL_IS_ACTIVE INTEGER, INOUT VAL_INVOICE_DATE DATE, INOUT VAL_INVOICE_NO VARCHAR(50), INOUT VAL_INVOICE_AMOUNT DECIMAL(30 , 2) ) RESULT SETS 1 LANGUAGE SQL BEGIN   DECLARE CR_TESTTABLE CURSOR WITH RETURN FOR SELECT * FROM USER.TEST_TABLE WHERE ( ( ( INVOICE_NO = VAL_RECORD_ID ) AND VAL_INVOICE_NO IS NOT NULL ) OR VAL_INVOICE_NO IS NULL ) AND ( (DATE(INVOICE_DATE) = DATE(VAL_INVOICE_DATE) AND VAL_INVOICE_DATE IS NOT NULL) OR VAL_INVOICE_DATE IS NULL ) AND ( ( INVOICE_AMOUNT = VAL_INVOICE_AMOUNT AND VAL_INVOICE_AMOUNT IS NOT NULL ) OR VAL_INVOICE_AMOUNT IS NULL ); OPEN CR_TESTTABLE; END
    

    Business Entities Layer
    
    using System; using System.Collections.Generic; using System.Text;   namespace BusinessEntities 
    { 
    
    public 
    
    class clsTest : clsAbstractFactoryBE 
    { 
    
    private 
    
    int m_RecordId; 
    
    private DateTime m_CreatedOn; 
    
    private DateTime m_LastModifiedOn; 
    
    private DateTime m_DeletedOn; 
    
    private string m_LastModifiedBy; 
    
    private string m_LastUpdatedFrom; 
    
    private 
    
    int m_IsActive;   
    
    private DateTime m_Date; 
    
    private string m_InvoiceNo; 
    
    private Decimal m_Amount;   
    /*  Unique field indentifying the record/entity number.     */ 
    
    public 
    
    int RecordId 
    { get 
    { 
    
    return m_RecordId; 
    } set 
    { m_RecordId = value; 
    } 
    } 
    /*  Date when Entity was created    */ 
    
    public DateTime CreatedOn 
    { get 
    { 
    
    return m_CreatedOn; 
    } set 
    { m_CreatedOn = value; 
    } 
    } 
    /*     Date when Entity was last modified      */ 
    
    public DateTime LastModifiedOn 
    { get 
    { 
    
    return m_LastModifiedOn; 
    } set 
    { m_LastModifiedOn = value; 
    } 
    } 
    /*      Date when Entity was marked for deletion.       */ 
    
    public DateTime DeletedOn 
    { get 
    { 
    
    return m_DeletedOn; 
    } set 
    { m_DeletedOn = value; 
    } 
    } 
    /*     User who modified the Entity last.      */ 
    
    public string LastModifiedBy 
    { get 
    { 
    
    return m_LastModifiedBy; 
    } set 
    { m_LastModifiedBy = value; 
    } 
    } 
    /*        The Machines from where the entity was last modified.   */ 
    
    public string LastUpdatedFrom 
    { get 
    { 
    
    return m_LastUpdatedFrom; 
    } set 
    { m_LastUpdatedFrom = value; 
    } 
    } 
    /*     Is record active.       */ 
    
    public 
    
    int IsActive 
    { get 
    { 
    
    return m_IsActive; 
    } set 
    { m_IsActive = value; 
    } 
    }   
    /*  Date    */ 
    
    public DateTime Date 
    { get 
    { 
    
    return m_Date; 
    } set 
    { m_Date = value; 
    } 
    } 
    /*  Invoice     */ 
    
    public string InvoiceNo 
    { get 
    { 
    
    return m_InvoiceNo; 
    } set 
    { m_InvoiceNo = value; 
    } 
    } 
    /*  Amount      */ 
    
    public Decimal Amount 
    { get 
    { 
    
    return m_Amount; 
    } set 
    { m_Amount = value; 
    } 
    }   
    } 
    }
    

    DB Access Layer Code
    
    using System; using IBM.Data.DB2; using IBM.Data.DB2Types; using BusinessEntities; namespace DBAccessLayer 
    { 
    /// <summary> 
    /// Summary description for DB2Class1. 
    /// This class and method can be used as the basis for a DB2 CLR procedure. 
    /// </summary> 
    
    public 
    
    class clsTest : clsDBAccessLayer 
    { 
    
    public 
    
    static clsAbstractFactoryBE AddEdit(BusinessEntities.clsAbstractFactoryBE objNewObject) 
    { BusinessEntities.clsTest objClsTest = (BusinessEntities.clsTest)objNewObject; 
    
    if (objDB2Connection.State == System.Data.ConnectionState.Closed) objDB2Connection.Open();   DB2Command cmd = objDB2Connection.CreateCommand(); DB2Transaction trans = objDB2Connection.BeginTransaction(); String procName = 
    "USER.SAVE_TEST_TABLE"; cmd.Transaction = trans; cmd.CommandType =  System.Data.CommandType.StoredProcedure; cmd.CommandText = procName;   
    // Register input-output and output parameters for the DB2Command #region Command Parameter Settings.   
    //INOUT VAL_TDD_UNIQUE_RECORD_ID                        INTEGER,, cmd.Parameters.Add(
    "VAL_TDD_UNIQUE_RECORD_ID", DB2Type.Integer); cmd.Parameters[
    "VAL_TDD_UNIQUE_RECORD_ID"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_UNIQUE_RECORD_ID"].Value = objClsTest.RecordId;   
    //INOUT VAL_TDD_DATE_OF_CREATION                        TIMESTAMP, cmd.Parameters.Add(
    "VAL_TDD_DATE_OF_CREATION", DB2Type.Timestamp); cmd.Parameters[
    "VAL_TDD_DATE_OF_CREATION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_DATE_OF_CREATION"].Value = objClsTest.CreatedOn;   
    //INOUT VAL_TDD_DATE_OF_MODIFICATION                    TIMESTAMP, cmd.Parameters.Add(
    "VAL_TDD_DATE_OF_MODIFICATION", DB2Type.Timestamp); cmd.Parameters[
    "VAL_TDD_DATE_OF_MODIFICATION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_DATE_OF_MODIFICATION"].Value = objClsTest.LastModifiedOn;   
    //INOUT VAL_TDD_DATE_OF_DELETION                        TIMESTAMP, cmd.Parameters.Add(
    "VAL_TDD_DATE_OF_DELETION", DB2Type.Timestamp); cmd.Parameters[
    "VAL_TDD_DATE_OF_DELETION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_DATE_OF_DELETION"].Value = objClsTest.DeletedOn;   
    //INOUT VAL_TDD_LAST_UPDATED_BY                         VARCHAR(200), cmd.Parameters.Add(
    "VAL_TDD_LAST_UPDATED_BY", DB2Type.VarChar, 200); cmd.Parameters[
    "VAL_TDD_LAST_UPDATED_BY"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_LAST_UPDATED_BY"].Value = objClsTest.LastModifiedBy;   
    //INOUT VAL_TDD_LAST_UPDATED_FROM                       VARCHAR(200), cmd.Parameters.Add(
    "VAL_TDD_LAST_UPDATED_FROM", DB2Type.VarChar, 200); cmd.Parameters[
    "VAL_TDD_LAST_UPDATED_FROM"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_LAST_UPDATED_FROM"].Value = objClsTest.LastUpdatedFrom;   
    //INOUT VAL_TDD_IS_ACTIVE                               INTEGER, cmd.Parameters.Add(
    "VAL_TDD_IS_ACTIVE", DB2Type.Integer); cmd.Parameters[
    "VAL_TDD_IS_ACTIVE"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_IS_ACTIVE"].Value = objClsTest.IsActive;   
    //INOUT VAL_INVOICE_DATE                                DATE, cmd.Parameters.Add(
    "VAL_INVOICE_DATE", DB2Type.Date); cmd.Parameters[
    "VAL_INVOICE_DATE"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_INVOICE_DATE"].Value = objClsTest.Date;   
    //INOUT VAL_INVOICE_NO                                  VARCHAR(50), cmd.Parameters.Add(
    "VAL_INVOICE_NO", DB2Type.VarChar, 50); cmd.Parameters[
    "VAL_INVOICE_NO"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_INVOICE_NO"].Value = objClsTest.InvoiceNo;   
    //INOUT VAL_INVOICE_AMOUNT                              DECIMAL(30 , 2), cmd.Parameters.Add(
    "VAL_INVOICE_AMOUNT", DB2Type.Decimal); cmd.Parameters[
    "VAL_INVOICE_AMOUNT"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_INVOICE_AMOUNT"].Value = objClsTest.Amount;   #endregion   
    
    try 
    { 
    
    int intReturnval = cmd.ExecuteNonQuery(); System.Diagnostics.Debug.Write(
    "Return Value: " + Convert.ToString(cmd.Parameters[0].Value)); trans.Commit(); 
    } 
    
    catch (Exception ex) 
    { trans.Rollback(); Console.WriteLine(ex.Message); 
    
    throw; 
    } 
    
    return objClsTest; 
    }     
    
    public 
    
    static System.Data.DataTable SearchByParameter(object[] objParameterValue) 
    { System.Data.DataTable dt = 
    
    new System.Data.DataTable();   
    
    if (objDB2Connection.State == System.Data.ConnectionState.Closed) objDB2Connection.Open();   DB2Command cmd = objDB2Connection.CreateCommand(); DB2Transaction trans = objDB2Connection.BeginTransaction(); String procName = 
    "USER.GET_TEST_TABLE"; cmd.Transaction = trans; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = procName;   
    // Register input-output and output parameters for the DB2Command #region Command Parameter Settings.   
    //INOUT VAL_TDD_UNIQUE_RECORD_ID                        INTEGER,, cmd.Parameters.Add(
    "VAL_TDD_UNIQUE_RECORD_ID", DB2Type.Integer); cmd.Parameters[
    "VAL_TDD_UNIQUE_RECORD_ID"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_UNIQUE_RECORD_ID"].Value = objParameterValue[0];   
    //INOUT VAL_TDD_DATE_OF_CREATION                        TIMESTAMP, cmd.Parameters.Add(
    "VAL_TDD_DATE_OF_CREATION", DB2Type.Timestamp); cmd.Parameters[
    "VAL_TDD_DATE_OF_CREATION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_DATE_OF_CREATION"].Value = objParameterValue[1];   
    //INOUT VAL_TDD_DATE_OF_MODIFICATION                    TIMESTAMP, cmd.Parameters.Add(
    "VAL_TDD_DATE_OF_MODIFICATION", DB2Type.Timestamp); cmd.Parameters[
    "VAL_TDD_DATE_OF_MODIFICATION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_DATE_OF_MODIFICATION"].Value = objParameterValue[2];   
    //INOUT VAL_TDD_DATE_OF_DELETION                        TIMESTAMP, cmd.Parameters.Add(
    "VAL_TDD_DATE_OF_DELETION", DB2Type.Timestamp); cmd.Parameters[
    "VAL_TDD_DATE_OF_DELETION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_DATE_OF_DELETION"].Value = objParameterValue[3];   
    //INOUT VAL_TDD_LAST_UPDATED_BY                         VARCHAR(200), cmd.Parameters.Add(
    "VAL_TDD_LAST_UPDATED_BY", DB2Type.VarChar, 200); cmd.Parameters[
    "VAL_TDD_LAST_UPDATED_BY"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_LAST_UPDATED_BY"].Value = objParameterValue[4];   
    //INOUT VAL_TDD_LAST_UPDATED_FROM                       VARCHAR(200), cmd.Parameters.Add(
    "VAL_TDD_LAST_UPDATED_FROM", DB2Type.VarChar, 200); cmd.Parameters[
    "VAL_TDD_LAST_UPDATED_FROM"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_LAST_UPDATED_FROM"].Value = objParameterValue[5];   
    //INOUT VAL_TDD_IS_ACTIVE                               INTEGER, cmd.Parameters.Add(
    "VAL_TDD_IS_ACTIVE", DB2Type.Integer); cmd.Parameters[
    "VAL_TDD_IS_ACTIVE"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_TDD_IS_ACTIVE"].Value = objParameterValue[6];   
    //INOUT VAL_INVOICE_DATE                                DATE, cmd.Parameters.Add(
    "VAL_INVOICE_DATE", DB2Type.Date); cmd.Parameters[
    "VAL_INVOICE_DATE"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_INVOICE_DATE"].Value = objParameterValue[7];   
    //INOUT VAL_INVOICE_NO                                  VARCHAR(50), cmd.Parameters.Add(
    "VAL_INVOICE_NO", DB2Type.VarChar, 50); cmd.Parameters[
    "VAL_INVOICE_NO"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_INVOICE_NO"].Value = objParameterValue[8];   
    //INOUT VAL_INVOICE_AMOUNT                              DECIMAL(30 , 2), cmd.Parameters.Add(
    "VAL_INVOICE_AMOUNT", DB2Type.Decimal); cmd.Parameters[
    "VAL_INVOICE_AMOUNT"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
    "VAL_INVOICE_AMOUNT"].Value = objParameterValue[9];   #endregion   
    
    try 
    { DB2DataAdapter objDB2DataAdapter = 
    
    new DB2DataAdapter(cmd); objDB2DataAdapter.Fill(dt); System.Diagnostics.Debug.Write(
    "Total Records in Table fetched : " + Convert.ToString(dt.Rows.Count)); trans.Commit(); 
    } 
    
    catch (Exception ex) 
    { trans.Rollback(); Console.WriteLine(ex.Message); 
    
    throw; 
    } 
    
    return dt; 
    } 
    } 
    }
    

    Data Insertion Code
    
    BusinessEntities.clsTest objClsTest = 
    
    new clsTest(); objClsTest.RecordId = -1; objClsTest.CreatedOn = DateTime.Now; objClsTest.LastModifiedOn = DateTime.Now; objClsTest.DeletedOn = DateTime.Now; objClsTest.LastModifiedBy = 
    "TESTFORMENTRY"; objClsTest.LastUpdatedFrom = System.Environment.MachineName; objClsTest.IsActive = -1; objClsTest.Amount = 1001.00M; objClsTest.InvoiceNo = 
    "1001"; objClsTest.Date = DateTime.Now.Date; DBAccessLayer.clsTest.AddEdit(objClsTest);
    

    Data Fetch Code
    ERROR Occurs in the fetch section when it calls the SearchByParameter routine
    
    Object[] objParametersValues = 
    { null, null, null, null, null, null, null, null, null, 
    
    null
    }; DataTable dt = 
    
    new DataTable(); dt = DBAccessLayer.clsTest.SearchByParameter(objParametersValues);
    

    Details of Call Stack Trace

    ERROR 58005 IBMhttp://DB2.NET SQL0902 An unexpected exception has occurred in Process: 5716 Thread 10 AppDomain: Name:Inventory Manager.vshost.exe
    There are no context policies.
    Function: DB2DataReader.Read()
    CallStack: at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
    at System.Environment.get_StackTrace()
    at IBM.Data.DB2.DB2ConnPool.HandleUnknownErrors(String strFncMsg, Exception exception, Boolean bThrow)
    at IBM.Data.DB2.DB2DataReader.Read()
    at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
    at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
    at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
    at DBAccessLayer.clsTest.SearchByParameter(Object[] objParameterValue) in E:\Development\Source Codes\Inventory Manager\Inventory Manager\DB2ClassLibrary\clsTest.cs:line 169
    at Inventory_Manager.frmTestBase.button20_Click(Object sender, EventArgs e) in E:\Development\Source Codes\Inventory Manager\Inventory Manager\Inventory Manager\frmTestBase.cs:line 396
    at System.Windows.Forms.Control.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnClick(EventArgs e)
    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at System.Windows.Forms.ButtonBase.WndProc(Message& m)
    at System.Windows.Forms.Button.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
    at System.Windows.Forms.Application.Run(Form mainForm)
    at Inventory_Manager.Program.Main() in E:\Development\Source Codes\Inventory Manager\Inventory Manager\Inventory Manager\Program.cs:line 17
    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart() InnerException Message: Year, Month, and Day parameters describe an un-representable DateTime. Check InnerException property for more detail. SQLSTATE=58005
    Others Errors

    I could not use MS fix in my system because I got the following Errors.

    Microsoft Host Integration Server 2004 Hotfix HIS2004-KB926357-CLI-ENU.exe
    Microsoft Host Integration Server 2004 Client not found or this package does not contain compatible patch.

    Microsoft Host Integration Server 2004 HIS2004-KB926357-SRV-ENU.exe
    Microsoft Host Integration Server 2004 Client not found or this package does not contain compatible patch.



    From the above I believe I have furnished all the information which you have asked me to furnish. Kindly suggest something which can be of some help.

    Thanks & Regards,

    Sougandh
  • ArchanaSoni
    ArchanaSoni
    8 Posts
    ACCEPTED ANSWER

    Re: C# &#38; DB2 Express-C Exception SQLSTATE=58005 Year, Month, and Day parameters

    ‏2013-01-29T13:44:38Z  in response to SystemAdmin
    Hello Sougandh,

    1) Would you be able to confirm if this issue is reproducible on any other client machine. We are unable to reproduce the issue and wondering if it is a specific machine problem since Microsoft has a similar error documented. Would you be able to check on a Windows 7 client and confirm?

    2) Are you changing the default 'DateTime' format either thru the application or the Windows system settings. Can you let us know what is the format you have set.

    3) Additionally we would have liked to see the DB2 trace, but considering it is not possible to attach files in the forum, we encourage you to reach out to IBM Support which will help in expediting the investigation.

    Thanks
    Archana
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: C# &#38; DB2 Express-C Exception SQLSTATE=58005 Year, Month, and Day parameters

    ‏2013-01-30T06:37:53Z  in response to SystemAdmin
    Hi Archana Madam,

    I really don't know why this happens. But you see there are 2 stored procedure routines which I sent you and its pertaining to DATE datatype only and in certain cases only. The 2 stored procedures are.

    1. http://USER.GET_TEST_TABLE - A procedure to fetch records.
    2. http://USER.SAVE_TEST_TABLE - A procedure to save records.

    What I find funny here is, the fact that for Save records procedure(Procedure #2) things work properly through the same .net code. When I try passing a null to the DATE parameter it gets saved also, properly without any errors, I have no problem at all. But with the fetch operation(Procedure #1), its the same story I get the exception.

    Method call through the .net application is the only point where I get these errors. Execution of the procedure from the Data Studio works fine it returns the right set of records too.

    Now as for my purpose, its only in particular table I am using this DATE datatype too. And it is not working. Rest at all the places I have used TIMESTAMP and I have no issues with it. As a fix I feel, probably I might have to change the datatype from DATE to TIMESTAMP in order to expedite the same at-least at my end, I think mainly because DB2-Express C is a free DB.

    No other changes I am making with regard to date format etc. I am attaching the .jpg of the regional settings I have on my computer.

    DB2Trace, Let me check if I can provide. It will take time though.

    Regards,
    Sougandh
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: C# &#38; DB2 Express-C Exception SQLSTATE=58005 Year, Month, and Day parameters

    ‏2013-01-30T06:43:44Z  in response to SystemAdmin
    Yeah one more thing, I regret the fact that I won't be able to reproduce it on another machine. Please forgive me for that.