How can I get rid of "Update requires a valid InsertCommand when passed DataRow collection with new rows." RRS feed

  • Question

  • I get the following exception when I try to update my database It says:"Update requires a valid InsertCommand when passed DataRow collection with new rows."
    and I dont know how to add the appropriated insert command.
    Please help, thanks.
    Code Block

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    using System.Data;

    namespace WindowsFormsApplication1
        public partial class Form1 : Form
            public Form1()

            private void button1_Click(object sender, EventArgs e)
                OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=E:\\hp\\Source\\Val-Asr-LastPart\\db1.mdb");
                string sqlstr = "SELECT * FROM kham";
                OleDbCommand com=new OleDbCommand(sqlstr,con);
                OleDbDataAdapter da =new OleDbDataAdapter(com);
                DataSet ds = new DataSet();
                da.Fill(ds, "kham");
                DataRowCollection drc = ds.Tables["kham"].Rows;
                textBox1.Text = (string)drc[0][1];

                string str1 = (string)drc[0][1];
                string st="";
                string stt="";
                char[] ch1 = str1.ToCharArray();
                int k=0;
                int i=0;

                sqlstr="SELECT * FROM parsed";
                da=new OleDbDataAdapter(com);
                ds=new DataSet();
                for (; i < ch1.Length; i++)
                    if ((ch1[i] == '1' || ch1[i] == '2' || ch1[i] == '3' || ch1[i] == '4' || ch1[i] == '5' || ch1[i] == '6' || ch1[i] == '7' || ch1[i] == '8' || ch1[i] == '9' || ch1[i] == '0') && (ch1[i + 1] == 'ـ'))
                            if ((ch1[i]!='(')||(ch1[i+1]!='['))
                                st += ch1[i];
                                //ds.Tables["parsed"].Rows[k][1]= st;
                                //st = "";
                                for (i += 6; i < ch1.Length; i++)
                                    if ((ch1[i] == '1' || ch1[i] == '2' || ch1[i] == '3' || ch1[i] == '4' || ch1[i] == '5' || ch1[i] == '6' || ch1[i] == '7' || ch1[i] == '8' || ch1[i] == '9' || ch1[i] == '0') && (ch1[i + 1] == 'ـ'))
                                        //ds.Tables["parsed"].Rows[k++][2] = stt;
                                        string[] sst = { ""+(k++), st, stt };
                                        stt = "";
                                        stt += ch1[i];

    Saturday, January 5, 2008 2:29 PM

All replies

  • You should be able to supply the update statement for the dataadapter with something like this


    da.UpdateCommand.CommandText = " your update string here "



    or in the designer you can configure your adapter to generate the statements for you if you are using a dataset

    just edit the dataset with the designer and then right click and select configure - advanced options



    another way would be to use the commandbuilder

    Dim cmdbldr As New OleDb.OleDbCommandBuilder(da)



    I think between these you should be able to make it work

    sorry it's in vb, i don't use c#

    Sunday, January 6, 2008 12:52 AM
  • It's worth understanding what an Adapter (any IDBDataAdatper object like SqlDataAdatper or OleDbDataAdapter) does.  A Dataadapter is an object that can contain 0 to 4 Command objects. To be useful, it must have at least one.  If you have to get data, add data , delete data and modify data, then you'll need 4 commands. You need an UpdateCommand, a SelectCommand, a DeleteCommand and an InsertCommand.  For any update scenario, you'll need an UpdateCommand, DeleteCommand or InsertCommand - perhaps all three. If you're only getting data, (read only scenarios) then a SelectCommand will suffice. That's what you have right now, only a Select command so you can't do any form of updates. If you only have rows that will be added, then you only need an InsertCommand. If you might be deleting rows you need a DeleteCommand and modifying rows then an UpdateCommand - if you don't add these, you'll get rid of the Insert exception but you'll run into subsequent ones with each of these type.


    When you call DataAdapter.Update, the adapter loops through each row of your datatable and looks at the RowState.  If the Rowstate of a datarow is Added, then it will use the adapter's Insert command. If it's Deleted, it will use the Delete command.  If it's modified, then it will ue the UpdateCommand.  Now, if you call DataAdatper.Update and you only have Deleted rows or unchanged rows in your datatable, you'll be fine as long as you have a DeleteCommand. If you don't have a delete command, that's where you get in trouble. That's what's happening here.  You have rows that have Added as their rowstate and No corresponding InsertCommand.  Now, to be useful, you need to have Parameters for that InsertCommand and those need to be mapped to fields in your datatable. If you use the AdapterConfigurationWizard, look at the code it generates b/c it shows you how to do this.  Anyway, you'll need to figure out how to get those row values to map to the parameters in your INsertCommand - you can do it by setting the mapping in the column instantiation or you can do it manually later - that is really not a good idea if you're calling Update in most cases. 


    Now, you can can the whole thing and just get your new rows, loop through them and call an sqlcommand's ExecuteNonQuery method (which would have INSERT INTO MYTABLE VALUES (Column1Value, Column2Value etc)


    That's a different issue though but equally valid. After all, that's all the adapter does behind the scenes (other than opening and closing connections associated with the commands).


    As to where to add the INsertCommand - well, anywhere after you instantiate it and before you call Update. Run the configuration wizard and see the code it generates, this will really help you understand how it works.  Also, if you added a valid Insert command, but you have Deleted or Modified rowstates on other rows, you're just going to throw exceptions for those so unless you're positive you'll never encounter rows with those states, best to go ahead and configure them now too when you add your InsertCommand.


    Hopefully this helps, if not, let me know.


    Sunday, January 6, 2008 6:40 AM