Topic
  • 1 reply
  • Latest Post - ‏2012-06-05T14:29:45Z by PTM
PTM
PTM
10 Posts

Pinned topic Displaying date/time column (returned by an Informix sp) in a DataGridView

‏2012-05-30T22:45:18Z |
Hello.

I would appreciate whether some assistance could be provided to solve the following problem.

In a Windows Forms application, a DataGridView is populated with the result set of a stored procedure:



// declaration 

private DB2Connection m_connection;                       
// Informix database connection 

private DB2Command m_cmdISMassAuthenticationRunSelect1;   
// retrieves mass authentication runs   
// initialization m_connection = 

new DB2Connection(); m_cmdISMassAuthenticationRunSelect1 = 

new DB2Command(
"procISMassAuthenticationRunSelect1"); m_cmdISMassAuthenticationRunSelect1.CommandType = CommandType.StoredProcedure; m_cmdISMassAuthenticationRunSelect1.Connection = m_connection; m_cmdISMassAuthenticationRunSelect1.Parameters.Add(

new DB2Parameter(
"parDate", DB2Type.Date));   ...   
// connecting to the database m_connection.Open();   
// setting the parameter of the stored procedure m_cmdISMassAuthenticationRunSelect1.Parameters[
"parDate"].Value = dpDate.Value;   
// retrieving runs that match the date parameter DB2DataAdapter dataAdapter = 

new DB2DataAdapter(m_cmdISMassAuthenticationRunSelect1); DataTable dataTable = 

new DataTable(); dataAdapter.Fill(dataTable);   
// populating the data grid view dgRun.DataSource = dataTable;   
// closing the connection m_connection.Close();


The stored procedure returns rows with 3 columns:

CREATE PROCEDURE procISMassAuthenticationRunSelect1 (parDate DATE) RETURNING INT8 AS id, DATETIME YEAR TO SECOND AS start_date_time, DATETIME YEAR TO SECOND AS end_date_time;


For date 05/17/2012, a result set with one row is returned.
I have debugged the DataTable object and the values are correct:
id: 29
start_date_time: 05/17/2012 4:25:07 PM
end_date_time: null

However, the DataGridView does not display the value associated with column "start_date_time"; only the value associated with column "id" is displayed.

The DataSource property of the DataGridView is set to runItem1BindingSource, which is associated with the following class:



public 

class RunItem1 
{ 

private Int64 m_id; 

private DateTime m_start; 

private DateTime m_end;   

public Int64 Id 
{ get 
{ 

return m_id; 
} set 
{ m_id = value; 
} 
}   

public DateTime StartDateTime 
{ get 
{ 

return m_start; 
} set 
{ m_start = value; 
} 
}   

public DateTime EndDateTime 
{ get 
{ 

return m_end; 
} set 
{ m_end = value; 
} 
} 
}


Is there something missing in the definition of the DataGridView that is causing this problem?
Thank you in advance.

PTM
Environment:
. IBM Informix 11 running on a Linux server
. Visual Studio 2010 Professional Version 10.0.30319.1 RTMRel running on Windows XP Professional Version 2002 Service Pack 3
Updated on 2012-06-05T14:29:45Z at 2012-06-05T14:29:45Z by PTM
  • PTM
    PTM
    10 Posts

    Re: Displaying date/time column (returned by an Informix sp) in a DataGridView

    ‏2012-06-05T14:29:45Z  
    For those who might be interested in the workaround.


    Workaround #1
    . Change of the stored procedure so that the date/time column is returned as a string
    . Corresponding change in class RunItem1
    . Outcome: problem not solved


    Workaround #2
    . Change of the stored procedure so that the id is the second column of the result set
    . Corresponding change in class RunItem1
    . Outcome: problem not solved


    Workaround #3
    . Change of the stored procedure to its original implementation
    . Corresponding change in class RunItem1
    . Removal of the value of property DataSource of DataGridView object
    . "Manual" process populates DataGridView:

    
    
    // connecting to the database m_connection.Open();   
    // executing the stored procedure using (DB2DataReader reader = m_cmdISMassAuthenticationRunSelect1.ExecuteReader()) 
    { 
    
    if (reader.HasRows) 
    { Int32 id = reader.GetOrdinal(
    "id"); Int32 start_date_time = reader.GetOrdinal(
    "start_date_time"); Int32 end_date_time = reader.GetOrdinal(
    "end_date_time");   
    
    while (reader.Read()) 
    { Int64 runId = (!reader.IsDBNull(id) ? reader.GetInt64(id) : 0); String startDateTime = (!reader.IsDBNull(start_date_time) ? String.Format(reader.GetDateTime(start_date_time).ToString(
    "g")) : 
    ""); String endDateTime = (!reader.IsDBNull(end_date_time) ? String.Format(reader.GetDateTime(end_date_time).ToString(
    "g")) : 
    "");   
    
    int rowIndex = dgRun.Rows.Add(1); dgRun.Rows[rowIndex].Cells[
    "Id"].Value = runId; dgRun.Rows[rowIndex].Cells[
    "StartDateTime"].Value = startDateTime; dgRun.Rows[rowIndex].Cells[
    "EndDateTime"].Value = endDateTime; 
    } 
    } 
    }   
    // closing the connection m_connection.Close();
    

    Outcome: problem solved

    PTM