Date/Time in Datagrid

[Administrator]

I have followed the codes in the Windows Forms FAQ but still can't get it to work. I'm binding my datagrid to a table with datatype as Date/Time in access. But when I run it, the datagrid only shows the date as 12/30/1899 and the time is not appearing!! The values in my db table are TIME and not dates!! Pls. help! I tried formatting the datagrid using the datagridcolumnstyles but still not showing the time!! WHY?

[Clay Burch]

I suspect that your implementation of the datagridcolumnstyles is not being used by your datagrid for some reason. This is simple to check. In your columnstyle for the time, chnage the width to 200 or something. If the width does not change in your datagrid, then the custom columnstyle is not being applied, and that would explain why the format is not working.

If you can post a small project and mdb file showing the problem, maybe someone can suggest a change that would work for you.

The time formatting does seem to work. If you use VS and

1) create a new Windows Application.
2) Drop a DataGrid onto the form
3) Double click in the form itself to add a Load handler & copy the code below into the handler
4) add a using System.Data.OleDb;
5) change the connection string to whatever works on your system

With these 5 steps, I see time only (00:00 AM) in column 3.

private void Form1_Load(object sender, System.EventArgs e)
{
	// Set the connection and sql strings
	// assumes your mdb file is in your root
	string connString = @"Provider=Microsoft.JET.OLEDB.4.0;data source=C:orthwind.mdb";
	string sqlString = "SELECT * FROM orders";

	OleDbDataAdapter dataAdapter = null;
	DataSet _dataSet = null;

	try
	{
		// Connection object
		OleDbConnection connection = new OleDbConnection(connString);

		// Create data adapter object
		dataAdapter = new OleDbDataAdapter(sqlString, connection);
			
		// Create a dataset object and fill with data using data adapter's Fill method
		_dataSet = new DataSet();
		dataAdapter.Fill(_dataSet, "orders");
		connection.Close();
	}
	catch(Exception ex)
	{	
		MessageBox.Show("Problem with DB access-   connection: "
			+ connString + "\r\r            query: " + sqlString
			+ "\r\r\r" + ex.ToString());
		this.Close();
		return;
	}

	// Create a table style that will hold the new column style 
	// that we set and also tie it to our customer's table from our DB
	DataGridTableStyle tableStyle = new DataGridTableStyle();
	tableStyle.MappingName = "orders";

	// since the dataset has things like field name and number of columns,
	// we will use those to create new columnstyles for the columns in our DB table
	int numCols = _dataSet.Tables["orders"].Columns.Count;
	DataGridTextBoxColumn aColumnTextColumn ;
	for(int i = 0; i < numCols; ++i)
	{
		aColumnTextColumn = new DataGridTextBoxColumn();
		aColumnTextColumn.HeaderText = _dataSet.Tables["orders"].Columns[i].ColumnName;

		//set col 3 format to a time only
		if(i == 3)
			aColumnTextColumn.Format = "mm:ss t";//"MM/dd/yyyy";

		aColumnTextColumn.MappingName = _dataSet.Tables["orders"].Columns[i].ColumnName;
		tableStyle.GridColumnStyles.Add(aColumnTextColumn);
	}
			
	// make the dataGrid use our new tablestyle and bind it to our table
	dataGrid1.TableStyles.Clear();
	tableStyle.AlternatingBackColor = Color.Firebrick;
	dataGrid1.TableStyles.Add(tableStyle);
	dataGrid1.DataSource = _dataSet.Tables["orders"].DefaultView;

}