System.Data.ConstraintException Question

Question

I am working on a MySQL data dump project. Got two tables in my test database.

1) Orders
2) Product

---------------------------------------------------------------------------------

--

-- Table structure for table `orders`

--

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (

  `order_id` int(10) unsigned NOT NULL auto_increment,

  `prd_id` int(10) unsigned NOT NULL default '0',

  `qty` int(10) unsigned NOT NULL default '0',

  `cust_name` varchar(25) NOT NULL default '',

  PRIMARY KEY  (`order_id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `orders` VALUES (1,1,10,'Cust No.1');

INSERT INTO `orders` VALUES (2,1,14,'Cust No.2');

INSERT INTO `orders` VALUES (3,3,33,'Cust No.1');

INSERT INTO `orders` VALUES (4,2,50,'Cust No.2');

DROP TABLE IF EXISTS `product`;

CREATE TABLE `product` (

  `id` int(10) unsigned NOT NULL auto_increment,

  `code` varchar(5) NOT NULL default '',

  `name` varchar(20) NOT NULL default '',

  PRIMARY KEY  (`id`),

  UNIQUE KEY `UNIQUE_KEY` (`code`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `product` VALUES (1,'IT101','Item 101');

INSERT INTO `product` VALUES (2,'IT102','Item 102');

INSERT INTO `product` VALUES (3,'IT103','Item 103');

INSERT INTO `product` VALUES (4,'IT104','Item 104');

---------------------------------------------------------------------------------

my C# code is suppose to pull the info from these two table and show it in a datagridview.

I am using the Mysql .Net Connector (Latest version) to do the job.

if I run the query "select * from orders as o inner join product as p on o.prd_id = p.id" on a mysql console I get the following output.

mysql> select * from orders as o inner join product as p on o.prd_id=p.id;

+----------+--------+-----+-----------+----+-------+----------+

| order_id | prd_id | qty | cust_name | id | code  | name     |

+----------+--------+-----+-----------+----+-------+----------+

|        1 |      1 |  10 | Cust No.1 |  1 | IT101 | Item 101 | 

|        2 |      1 |  14 | Cust No.2 |  1 | IT101 | Item 101 | 

|        4 |      2 |  50 | Cust No.2 |  2 | IT102 | Item 102 | 

|        3 |      3 |  33 | Cust No.1 |  3 | IT103 | Item 103 | 

+----------+--------+-----+-----------+----+-------+----------+

BUT........ if I run the same query with C#, I get an exception

---------------------------------------------------------------------------------

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

   at System.Data.DataTable.EnableConstraints()

   at System.Data.DataTable.set_EnforceConstraints(Boolean value)

   at System.Data.DataTable.EndLoadData()

   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)

   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)

   at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)

   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)

   at System.Data.DataTable.Load(IDataReader reader)

   at MySQLQuery.DAL.returnDataInDataTable(String sqlQuery) in C:\Documents and Settings\User1\Desktop\CSharp\MySQLQuery\DAL.cs:line 42

---------------------------------------------------------------------------------

but If I run just "select * from product" on my C# side this thing works!

C# Code snip:

---------------------------------------------------------------------------------

        private void btnRunQuery_Click(object sender, EventArgs e)

        {

            try

            {

                string query = tbxQuery.Text.ToString();

                BOL bol = new BOL();

                DataTable dt = new DataTable();

                //bol.GetDataFromMysqlTable runs the query and returns a datatable

                dt = bol.GetDataFromMysqlTable(query);

                dgvMysqlData.DataSource = dt;

            }

            catch (Exception ex)

            {

                tbxExceptions.Text = ex.Message.ToString();

            }

        }

---------------------------------------------------------------------------------

So the million dollar question is... what is wrong with my query. As far as I am concerned the query works & what I am doing wrong here!

Please let me know as my brain is running out of brain cells..

Thanks all for keeping this forum so useful.

MT