Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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];

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!


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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

598 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