0

I'm trying to update a column and delete the row that follows it in a DataTable retrieved from sql table, whenever the column equals a value and the same column in the row which follows starts with another value.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT * from tbl_Copy_Journal", con);
        DataSet Journals = new DataSet();
        adapter.Fill(Journals, "tbl_Copy_Journal");
        foreach (DataTable table in Journals.Tables)
        {
            for (int i = 0; i < Journals.Tables[0].Rows.Count; i++)
            {
                String action = Journals.Tables[0].Rows[i]["Action"].ToString();
                if (action == "reading dispensed")
                {

                    String action1= Journals.Tables[0].Rows[i+1]["Action"].ToString();
                    if (action1.StartsWith("Referred"))
                    {

                        String _Action = Journals.Tables[0].Rows[i]["Action"].ToString() + ", " + Journals.Tables[0].Rows[i + 1]["Action"].ToString();
                        Journals.Tables[0].Rows[i + 1].Delete();
                        Journals.Tables[0].Rows[i]["Action"] = _Action;
                    }
                    adapter.Update(Journals, "tbl_Copy_Journal");
                    Journals.AcceptChanges();
                }


            }
        }

The code returns Update requires a valid UpdateCommand when passed DataRow collection with modified rows. Is there a way to delete and update rows in datatable and to fix this code. Any help is appreciated.

Ayoub Salhi
  • 312
  • 1
  • 4
  • 19
  • 1
    Just an insight... You declare `int index` and `int index1` and never use them. – Ivan García Topete May 11 '18 at 17:42
  • thanks, I updated my code, they were there just for tracking. How do you think I can solve this ? – Ayoub Salhi May 11 '18 at 17:49
  • 1
    The error is spot on. Adapters require a command for each operation you might do, SELECT, INSERT, UPDATE and DELETE. You have only provided a SELECT command. [Check this](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/updating-data-sources-with-dataadapters) – Crowcoder May 11 '18 at 17:51
  • Also check [this answer](https://stackoverflow.com/a/6009157/9453080), `adapter.UpdateCommand = new SqlCommandBuilder(adapter).GetUpdateCommand();` after `adapter.Fill(Journals, "tbl_Copy_Journal")` – Ivan García Topete May 11 '18 at 17:55

1 Answers1

0

You fix it by doing what they ask. Supply the UpdateCommand.

paparazzo
  • 44,497
  • 23
  • 105
  • 176