ConstraintException but i don't know why!

I'm getting an unhandled Constra开发者_开发技巧int Exception when I run the following code with a particular set of paramaters:

using (MySqlConnection connMySql = new MySqlConnection(global.g_connString))
            {

                MySqlCommand cmd = connMySql.CreateCommand();

                cmd.CommandText = this.Query;

                connMySql.Open();

                using (MySqlDataReader dr = cmd.ExecuteReader())
                {

                    DataTable dt = new DataTable();
                    dt.Load(dr);
                    return dt;
                }

However, if I run the query direct (i.e. not on my application but using Query Browser) I can't see any null values or anything that would generate the error.

It must be data specific, as if I change the date range of my query it works fine.

Anyone got any ideas?!

Thanks,

Ben

PS The query is as follows:

SELECT COALESCE(ti.FIRST_NAME, 'Not Assigned') AS 'Technician',wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester', wo.TITLE 'Subject', rrs.resolution As Resolution, (wo.COMPLETEDTIME/1000) 'TimeStamp'
                                            FROM WorkOrder_Threaded wot
                                            INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID
                                            LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
                                            LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
                                            LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
                                            LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
                                            LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
                                            LEFT JOIN RequestResolution rrs ON wo.WORKORDERID=rrs.REQUESTID
                                        WHERE (wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME != -1) AND (wo.COMPLETEDTIME IS NOT NULL)
                                            AND wo.COMPLETEDTIME >= (UNIX_TIMESTAMP(TIMESTAMP('" + sdChartRange.From + @"')) * 1000)
                                            AND wo.COMPLETEDTIME <= (UNIX_TIMESTAMP(TIMESTAMP('" + sdChartRange.To + @"')) * 1000)
                                            AND wot.THD_WOID=wot.WORKORDERID
                                        ORDER BY Technician ASC

Where sdChartRange.From and .To are datetime values.

It's hard to say without knowing the schema of your table and the query you are calling it with. Are you certain that they are a perfect match?

I would suggest you call dataTable.GetErrors(); to get the error rows, then inspect / log the RowError property for each error row.

Try a different way of loading the DataTable, to avoid unintended constraints coming from the DB. Try this in the function.

MySqlCommand cmd = connMySql.CreateCommand();
cmd.CommandText = this.Query;

connMySql.Open();

DataTable dataTable;

// Create data adapter
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);

// Populate dataTable based on DB query
da.Fill(dataTable);
da.Dispose();

return dataTable;

Also see: C# Sqlite throwing ConstraintException with DataTable