Topic
  • No replies
SystemAdmin
SystemAdmin
2826 Posts

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

‏2013-01-19T14:55:08Z |
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

C# Sample Code



// Declaring & Setting DB Connection DB2Connection objDB2Connection = 

new DB2Connection(
"Database=YourDbNameGoesHere;User ID=YourUserIdGoesHere;Password=YourUserIdPasswordGoesHere;Persist Security Info=True");   
// Setting DB Connection 

if (objDB2Connection.State == System.Data.ConnectionState.Closed) objDB2Connection.Open();   
// Setting DB Command Object DB2Command cmd = objDB2Connection.CreateCommand(); DB2Transaction trans = objDB2Connection.BeginTransaction(); String procName = 
"USER.GET_TRANSACTION_MASTERS"; 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_AGD_UNIQUE_RECORD_ID                    INTEGER, cmd.Parameters.Add(
"VAL_UNIQUE_RECORD_ID", DB2Type.Integer); cmd.Parameters[
"VAL_UNIQUE_RECORD_ID"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_UNIQUE_RECORD_ID"].Value = 

null;   
//INOUT VAL_AGD_DATE_OF_CREATION                    TIMESTAMP, cmd.Parameters.Add(
"VAL_DATE_OF_CREATION", DB2Type.Timestamp); cmd.Parameters[
"VAL_DATE_OF_CREATION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_DATE_OF_CREATION"].Value = 

null;   
//INOUT VAL_AGD_DATE_OF_MODIFICATION                TIMESTAMP, cmd.Parameters.Add(
"VAL_DATE_OF_MODIFICATION", DB2Type.Timestamp); cmd.Parameters[
"VAL_DATE_OF_MODIFICATION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_DATE_OF_MODIFICATION"].Value = 

null;   
//INOUT VAL_AGD_DATE_OF_DELETION                    TIMESTAMP, cmd.Parameters.Add(
"VAL_DATE_OF_DELETION", DB2Type.Timestamp); cmd.Parameters[
"VAL_DATE_OF_DELETION"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_DATE_OF_DELETION"].Value = 

null;   
//INOUT VAL_AGD_LAST_UPDATED_BY                     VARCHAR(200), cmd.Parameters.Add(
"VAL_LAST_UPDATED_BY", DB2Type.VarChar, 200); cmd.Parameters[
"VAL_LAST_UPDATED_BY"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_LAST_UPDATED_BY"].Value = 

null;   
//INOUT VAL_AGD_LAST_UPDATED_FROM                   VARCHAR(200), cmd.Parameters.Add(
"VAL_LAST_UPDATED_FROM", DB2Type.VarChar, 200); cmd.Parameters[
"VAL_LAST_UPDATED_FROM"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_LAST_UPDATED_FROM"].Value = 

null;   
//INOUT VAL_AGD_IS_ACTIVE                           INTEGER, cmd.Parameters.Add(
"VAL_IS_ACTIVE", DB2Type.Integer); cmd.Parameters[
"VAL_IS_ACTIVE"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_IS_ACTIVE"].Value = 

null;   
//INOUT VAL_INVOICE_NO_REF                             VARCHAR(50), cmd.Parameters.Add(
"VAL_INVOICE_NO_REF", DB2Type.VarChar, 50); cmd.Parameters[
"VAL_INVOICE_NO_REF"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_INVOICE_NO_REF"].Value = 

null;   
//INOUT VAL_INVOICE_DATE                                 DATE, cmd.Parameters.Add(
"VAL_INVOICE_DATE", DB2Type.Timestamp); cmd.Parameters[
"VAL_INVOICE_DATE"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_INVOICE_DATE"].Value = 

null;   
//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 = 

null;   
//INOUT VAL_INVOICE_DATE                                 DATE, cmd.Parameters.Add(
"VAL_INVOICE_DATE", DB2Type.Timestamp); cmd.Parameters[
"VAL_INVOICE_DATE"].Direction = System.Data.ParameterDirection.InputOutput; cmd.Parameters[
"VAL_INVOICE_DATE"].Value = 

null;   
//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 = 

null;   #endregion   

try 
{ DB2DataAdapter objDB2DataAdapter = 

new DB2DataAdapter(cmd); objDB2DataAdapter.Fill(dt); 
// POINT WHERE ERROR IS THROWN 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;