DateTime fields shows as "#Error"

Question

In trying to convert some reports from VS2008 to VS2010 I have run into a major show stopper for me.  If I pull in a datetime field into the report it will just show "#Error" in the field.

I am using the MySqlConnector:

Main Form:

    private void button1_Click(object sender, EventArgs e)
    {
      string sQuery =
        @"SELECT
	        ID,
	        Email,
	        Subject,
	        Creator,
	        CreateDate
        FROM test_table
        WHERE
	        Email = '[e-mail]';";

      MySqlConnection sqlConn = new MySqlConnection(connStr);
      MySqlDataAdapter adReport = new MySqlDataAdapter(sQuery, sqlConn);
      adReport.MissingSchemaAction = MissingSchemaAction.AddWithKey;
      DataTable table = new DataTable();
      adReport.Fill(table);
      frmViewer fmViewer = new frmViewer("C:\Projects\Test\Test.rdlc", "Test", table, null);
      fmViewer.ShowDialog();
    }

Viewer Form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Reporting.WinForms;

namespace WinReports
{
  public partial class frmViewer : Form
  {
    public frmViewer(string ReportPath, string DSName, object DataObject, List<ReportParameter> Params)
    {
      InitializeComponent();
      rptViewer.LocalReport.ReportPath = ReportPath;
      rptViewer.LocalReport.DataSources.Add(new ReportDataSource(DSName, DataObject));
      if (Params != null)
        rptViewer.LocalReport.SetParameters(Params);
    }

    private void frmViewer_Load(object sender, EventArgs e)
    {
      this.rptViewer.RefreshReport();
    }
  }
}

If I put a DataGridView component on my form and bind it to my table, it shows dates just fine.  If I convert the datetime field to a string in my select statement it works, but I can't do proper sorting and such in the report.

I am using the MySql connector 6.3.5.0

Any ideas on this one?

Answer 1

I was able to fix this problem.  I guess if you don't explicitly define the table columns, the report will fail on a DateTime field.  Here is how I resolved it in the code above:

   MySqlConnection sqlConn = new MySqlConnection(connStr);
   MySqlDataAdapter adReport = new MySqlDataAdapter(sQuery, sqlConn);
   adReport.MissingSchemaAction = MissingSchemaAction.AddWithKey;

   // DataTable table = new DataTable();
   DataSet1.TestDataTable table = new DataSet1.TestDataTable();

   adReport.Fill(table);
   frmViewer fmViewer = new frmViewer("C:\Projects\Test\Test.rdlc", "Test", table, null);
   fmViewer.ShowDialog();

In VS2005/2008 you could pass in a generic table and the report could read datetime columns, but I guess this doesn't work in VS2010.

 

Answer 2

This didn't work for me.  Followed CTBrewski solution but it made no difference, I was still getting #ERROR on MySQL date columns.  Maybe my backend setup is different so here's the summary of how I was doing it (Visual Studio 2010, MySql 5.5, MySql Connector 6.4.4):

1.  Created a Dataset (DriversDataSet) using visual tools pointing to a database in my local MySql server.

2. Added a tableadapter (driverprofiles1DataTable) using simple select statement only using Fill option.  The selected rows contain a datetime field.

3. Created a report (Report2.rdlc)) using the tableadapter as the datasource - report dataset name is Report2DataSet .  Dragged the fields onto a report table control.

4. Added a button to the form and coded the reportviewer setup same as CTBrewski - see below.

5. run the report and see the date field gets #ERROR instead of showing the data.

Fix

6. Go back into dataset (DriverDataSet) using visual editor - click on the date field listed in the tableadapter (driversTableAdapter).  In the properties window you will see the DataType listed as MySql.Data.Types.MySqlDateTime, click the drop down and select System.Datetime.  Re-Ran the report and the dates appeared (no errors).

I'm guessing the the table adapter converts the Mysql.Datetime to System datetime when it fills the table so you have to match the date type in your report dataset.

// THIS IS THE BUTTON CODE TO REFRESH REPORTVIEWER
string sql =
            @"SELECT * from driverprofiles";

MySqlConnection Conn = new MySqlConnection();
Conn.ConnectionString = "server=localhost;...";

MySqlDataAdapter adapter = new MySqlDataAdapter(sql, Conn);

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

DriverDataSet.driverprofiles1DataTable table = new DriverDataSet.driverprofiles1DataTable();

adapter.Fill(table);

reportViewer1.LocalReport.ReportPath = @"C:\Report2.rdlc";

reportViewer1.LocalReport.DataSources.Clear();

reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("Report2DataSet",(object) table));
            reportViewer1.RefreshReport();

Hope this helps, cause I couldn't find this anywhere.

Answer 3

I can see why my original solution didn't work for you.  I didn't use a table adapter at all.  I just created a DataTable in the datasource and manually added the fields, so my date field was a DateTime field to begin with (I don't use table adapters much).

Good Catch.

CTBrewsi.