Problems with inserting data in database

Problems with inserting data in database

Postby thl1000 » Tue 29 Apr 2008 12:40

Hi all,

i want to read a xml file into a sql database table. There 's no problem reading the xml into a dataset using the following code:
Code: Select all
DataSet xmlDS = new DataSet();

xmlDS.ReadXml("hb_test1.xml");

Now a can display the dta in a dataGridview:
Code: Select all
this.dataGridView1.DataSource = xmlDS.Tables["hb"];

This works fine...

Now i want to insert the data into a mysql database using this code (Connection to database works):
Code: Select all
MySqlDataAdapter myAdapter = new MySqlDataAdapter();
         
         try
         {
            myAdapter.InsertCommand = new MySqlCommand( "" +
               "INSERT INTO hb (hb_id, hb_number, hb_year, mawb, customer_id, shipment_number, shipment_date, service_level, service_type, " +
               "origin_code, origin_country, dest_code, dest_country, description, shipper_code, shipper_name, consignee_code, consignee_name, " +
               "active, reason, addwho, adddate, editwho, editdate) " +
               "VALUES (:hb_id, :hb_number, :hb_year, :mawb, :customer_id, :shipment_number, :shipment_date, :service_level, :service_type, " +
               ":origin_code, :origin_country, :dest_code, :dest_country, :description, :shipper_code, :shipper_name, :consignee_code, :consignee_name, " +
               ":active, :reason, :addwho, :adddate, :editwho, :editdate)", myConnection);

            myConnection.Open();

            foreach (DataRow myRow in xmlDS.Tables["hb"].Rows)
            {
               myAdapter.InsertCommand.Parameters.Add("hb_id", MySqlType.Char, 20, myRow["hb_id"].ToString());
               myAdapter.InsertCommand.Parameters.Add("hb_number", MySqlType.VarChar, 20, myRow["hb_number"].ToString());
               myAdapter.InsertCommand.Parameters.Add("hb_year", MySqlType.Char, 2, myRow["hb_year"].ToString());
               myAdapter.InsertCommand.Parameters.Add("mawb", MySqlType.VarChar, 30, myRow["mawb"].ToString());
               myAdapter.InsertCommand.Parameters.Add("customer_id", MySqlType.VarChar, 10, myRow["customer_id"].ToString());
               myAdapter.InsertCommand.Parameters.Add("shipment_number", MySqlType.VarChar, 50, myRow["shipment_number"].ToString());
               myAdapter.InsertCommand.Parameters.Add("shipment_date", MySqlType.Int, 11, myRow["shipment_date"].ToString());
               myAdapter.InsertCommand.Parameters.Add("service_level", MySqlType.VarChar, 10, myRow["service_level"].ToString());
               myAdapter.InsertCommand.Parameters.Add("service_type", MySqlType.VarChar, 10, myRow["service_type"].ToString());
               myAdapter.InsertCommand.Parameters.Add("origin_code", MySqlType.Char, 5, myRow["origin_code"].ToString());
               myAdapter.InsertCommand.Parameters.Add("origin_country", MySqlType.Char, 3, myRow["origin_country"].ToString());
               myAdapter.InsertCommand.Parameters.Add("dest_code", MySqlType.Char, 5, myRow["dest_code"].ToString());
               myAdapter.InsertCommand.Parameters.Add("dest_country", MySqlType.Char, 3, myRow["dest_country"].ToString());
               myAdapter.InsertCommand.Parameters.Add("description", MySqlType.VarChar, 255, myRow["description"].ToString());
               myAdapter.InsertCommand.Parameters.Add("shipper_code", MySqlType.VarChar, 20, myRow["shipper_code"].ToString());
               myAdapter.InsertCommand.Parameters.Add("shipper_name", MySqlType.Text, 0, myRow["shipper_name"].ToString());
               myAdapter.InsertCommand.Parameters.Add("consignee_code", MySqlType.VarChar, 20, myRow["consignee_code"].ToString());
               myAdapter.InsertCommand.Parameters.Add("consignee_name", MySqlType.Text, 0, myRow["consignee_name"].ToString());
               myAdapter.InsertCommand.Parameters.Add("active", MySqlType.Int, 1, myRow["active"].ToString());
               myAdapter.InsertCommand.Parameters.Add("reason", MySqlType.Text, 0, myRow["reason"].ToString());
               myAdapter.InsertCommand.Parameters.Add("addwho", MySqlType.VarChar, 20, myRow["addwho"].ToString());
               myAdapter.InsertCommand.Parameters.Add("adddate", MySqlType.Int, 11, myRow["adddate"].ToString());
               myAdapter.InsertCommand.Parameters.Add("editwho", MySqlType.VarChar, 20, myRow["editwho"].ToString());
               myAdapter.InsertCommand.Parameters.Add("editdate", MySqlType.Int, 11, myRow["editdate"].ToString());

               myAdapter.Update(xmlDS, "hb");
            }

            myConnection.Close();

When executing this code, MySQL throws the following error:
Column "hb_id" cannot be null

Please help...

Regards

Thomas
thl1000
 
Posts: 1
Joined: Tue 09 Oct 2007 10:07

Postby Alexey.mdr » Mon 05 May 2008 10:17

Hi!

Could you please post a table definition script?
We will investigate the problem.
Alexey.mdr
 
Posts: 729
Joined: Thu 13 Dec 2007 10:24


Return to dotConnect for MySQL