Update SQL from Datatable

Posted on 2006-06-14
Medium Priority
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];

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Suggested Courses

764 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