Click here to Skip to main content
15,891,976 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi ,
What im trying to do is upload data from an XML File to an sql Database.
My XML File looks like this.
XML
<?xml version="1.0" encoding="utf-8"?>
<Table>
	<Product>
		<Product_id>1</Product_id>
		<Product_name>Product 1</Product_name>
		<Product_price>1000</Product_price>
	</Product>
....


My Database Column Names are Id[int], ProductName[nvarchar50] , ProductDescription[nvarchar4000] and a number of other columns.

The ERROR im getting
Column name or number of supplied values does not match table definition.


What I have tried:

C#
private void button3_Click(object sender, EventArgs e)
     {
         string connetionString = null;
         SqlConnection connection;
         SqlCommand command;
         SqlDataAdapter adpter = new SqlDataAdapter();
         DataSet ds = new DataSet();
         XmlReader xmlFile;
         string sql = null;

         int product_ID = 0;
         string Product_Name = null;
        string product_Price = null;

         connetionString = "Data Source=server name;Initial Catalog=database;User ID=user;Password=password";

         connection = new SqlConnection(connetionString);

         xmlFile = XmlReader.Create("Product.xml", new XmlReaderSettings());
         ds.ReadXml(xmlFile);
         int i = 0;
         connection.Open();
         for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
         {
             product_ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
             Product_Name = ds.Tables[0].Rows[i].ItemArray[1].ToString();
             product_Price = ds.Tables[0].Rows[i].ItemArray[2].ToString();
                 //Convert.ToDouble(ds.Tables[0].Rows[i].ItemArray[2]);
             sql = "insert into Product values(" + product_ID + ",'" + Product_Name + "'," + product_Price + ")";
             command = new SqlCommand(sql, connection);
             adpter.InsertCommand = command;
             adpter.InsertCommand.ExecuteNonQuery();
         }
         connection.Close();
         MessageBox.Show("Done .. ");
     }
 }
Posted
Updated 29-Jul-16 18:23pm

1 solution

Error is bit self-explanatory, your provided values for insertion does not matched with the number of columns in database.
Please checkout number of columns in database OR the good way to deal with such problem is, Use column name in insert query
see below query
SQL
sql = "insert into Product (Id, ProductName, ProductDescription)  values(" + product_ID + ",'" + Product_Name + "'," + product_Price + ")";

before you proceed, please check your ID should not be auto incremented.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900