Problems with inserting data in database

[thl1000]

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

[Alexey.mdr]

Hi!

Could you please post a table definition script?
We will investigate the problem.