ConstraintException but i don't know why!

Question

I'm getting an unhandled Constraint 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.

please show us the structure of database and Query itself.. do you have any constraints on the database. as this error is raised only when some constraint is violated. — Shekhar_Pro, Jan 25 '11 at 12:31

score 0 · Answer 1 · edited May 23 '17 at 11:57

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

score 0 · Answer 2 · answered Jan 25 '11 at 12:21

0

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?

answered Jan 25 '11 at 12:21

Kernow Steve

196
4

As far as I know. However, this is making the table up on the fly, isn't it? There are no predefined columns, so it should just take the result of my query and create a dataTable based on that. (Or so I thought...) – Ben Jan 25 '11 at 12:25

score 0 · Answer 3 · answered Jan 25 '11 at 12:50

0

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

answered Jan 25 '11 at 12:50

Joe

122,218
32
205
338

ConstraintException but i don't know why!

3 Answers3