Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update SQL from Datatable

Posted on 2006-06-14
7
Medium Priority
?
401 Views
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();
        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
Comment
Question by:BoggyBayouBoy
7 Comments
 
LVL 16

Expert Comment

by:Edwin_C
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.

Try

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

0
 
LVL 2

Expert Comment

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

Regards,
skumar

0
 
LVL 39

Expert Comment

by:appari
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


      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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Expert Comment

by:rraghvendra
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.
0
 
LVL 16

Accepted Solution

by:
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.
0
 
LVL 1

Author Comment

by:BoggyBayouBoy
ID: 16912078
Will do... trying it now.
0
 
LVL 1

Author Comment

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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses

581 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