Update SQL from Datatable

Posted on 2006-06-14
Last Modified: 2008-02-01
Hello Experts,

I'd like to write the values from a datatable to SQL Server... my code throws the following error:  "Insert Error: Column name or number of supplied values does not match table definition".  

In short, my code 1) loads an excel sheet at runtime from a fileupload control, 2)creates a datatable from the excel values, and 3) iterates thru the datatable and writes the values back to SQL.  The ExecuteNonQuery() method is the culprit.  Please help!

    protected void Button1_Click(object sender, EventArgs e)
        //set up the connection to excel
        string OLEDBConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("") +
            "\\" + "uploads" + "\\" + FileUpload1.FileName + ";Extended Properties=Excel 8.0;";

        OleDbConnection OLEDBConn = new OleDbConnection(OLEDBConnectionString);
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        string selcommand = "select * from [Sheet1$]";
        OleDbCommand OLEDBCmd = new OleDbCommand();
        OLEDBCmd.Connection = OLEDBConn;
        OLEDBCmd.CommandText = selcommand;

        //set up the sql connection
        string SQLConnectionString = "Data Source=Clancy;database=northwind;integrated security=true";
        SqlConnection SQLConn = new SqlConnection();
        SQLConn.ConnectionString = SQLConnectionString;

        if (FileUpload1.HasFile)
            FileUpload1.SaveAs(Server.MapPath("uploads") + "\\" + FileUpload1.FileName);
        //create dataset to hold excel values
        adapter.SelectCommand = OLEDBCmd;
        DataSet dataset = new DataSet();
        adapter.Fill(dataset, "Sheet1");

        //create sqlcommand
        SqlCommand SqlCmd = new SqlCommand();
        SqlCmd.Connection = SQLConn;
        SqlCmd.CommandText = "Insert into [Order Details] values (@OrderID, @ProductID, @Quantity)";
        //put dataset values into the sql database... this is where the problem is...
        foreach (DataRow dr in dataset.Tables[0].Rows)
            Response.Write("</br>" + dr.ItemArray[0].ToString() + " " + dr.ItemArray[1].ToString());
            SqlCmd.Parameters.AddWithValue("@OrderID", 10248);
            SqlCmd.Parameters.AddWithValue("@ProductID", dr[0]);
            SqlCmd.Parameters.AddWithValue("@Quantity", dr[1]);

Question by:BoggyBayouBoy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 16

Expert Comment

ID: 16908557
It is better to specify the column name in the INSERT statement because it save your trouble when altering the table structure at a later time.


SqlCmd.CommandText = "Insert into [Order Details] (OrderID, ProductID, Quantity) values (@OrderID, @ProductID, @Quantity)";


Expert Comment

ID: 16908891
Try this
you r trying to insert the value directly without defining the columns of the table , so this error occurs
make the values as
for eg:
@OrderID = int or string values,
@ProductID = int or string values,
@quantity = int
SqlCommand SqlCmd = new SqlCommand();
        SqlCmd.Connection = SQLConn;
        SqlCmd.CommandText = "Insert into [Order Details] (OrderID, ProductID, Quantity) values (@OrderID, @ProductID, @Quantity)";


LVL 39

Expert Comment

ID: 16908948
check if your [order details] table has only three columns or more columns.
if it has more then the above suggetions should solve the problem. even after changing if the problem persists then
try changing your code as follows


 foreach (DataRow dr in dataset.Tables[0].Rows)
            Response.Write("</br>" + dr.ItemArray[0].ToString() + " " + dr.ItemArray[1].ToString());
            SqlCmd.Parameters["@OrderID"]= 10248;
            SqlCmd.Parameters["@ProductID"]= dr[0];
            SqlCmd.Parameters["@Quantity"]= dr[1];

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Expert Comment

ID: 16909202
do following

1) check ur sql
 string sql  = "Insert into [Order Details] (OrderID, ProductID, Quantity) values (@OrderID, @ProductID, @Quantity)";

response.write (sql);
copy and paste sql in query anaylzer then check it

2) If you are getting problem in any mid row of datatable then and a counter in ur foreach loop
and use try catch block.Plz check ur data it may be possible ur data have any special charcter.
LVL 16

Accepted Solution

Edwin_C earned 500 total points
ID: 16912027
Besides what other experts suggest, the table [Orde Details] probably has more than 3 columns that you have not supplied values to the new record.  If that is the case, you should supply values to these columns or assign default values to these columns in the table definition.  Even if you just have three columns, as I said earlier, it is a good practice to specify the column in the INSERT statement so that it will not run into error if you alter the table structure later.

Author Comment

ID: 16912078
Will do... trying it now.

Author Comment

ID: 16923644
Thanks again to all... I'm having trouble with type mismatches in my parameters... but in much better shape...

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL ( and MongoDB (…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question