• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

Update SQL from Datatable

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();
        OLEDBConn.Open();
        adapter.Fill(dataset, "Sheet1");

        //create sqlcommand
        SqlCommand SqlCmd = new SqlCommand();
        SqlCmd.Connection = SQLConn;
        SqlCmd.CommandText = "Insert into [Order Details] values (@OrderID, @ProductID, @Quantity)";
        SQLConn.Open();
       
        //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]);
            SqlCmd.ExecuteNonQuery();
        }

        OLEDBConn.Close();
        SQLConn.Close();
    }
}
0
BoggyBayouBoy
Asked:
BoggyBayouBoy
1 Solution
 
Edwin_CCommented:
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.

Try

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

0
 
SKumar_1981Commented:
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)";
        SQLConn.Open();

Regards,
skumar

0
 
appariCommented:
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


      SqlCmd.Parameters.Add("@OrderID");
      SqlCmd.Parameters.Add("@ProductID");
      SqlCmd.Parameters.Add("@Quantity");

 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];
            SqlCmd.ExecuteNonQuery();
        }


0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rraghvendraCommented:
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.
0
 
Edwin_CCommented:
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.
0
 
BoggyBayouBoyAuthor Commented:
Will do... trying it now.
0
 
BoggyBayouBoyAuthor Commented:
Thanks again to all... I'm having trouble with type mismatches in my parameters... but in much better shape...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now