DataAdapter.Update() does not Update the Database

Question

I'm sure there is an extremely simple reason that this one line isn't working, but it has evaded for the past week, so I'm hoping someone else will notice my fault.

I have been working on this project for several weeks to a month. I have been using a mix of old DataAdapter, CommandBuiler, etc. with some linq to sql coding on 1 database, with multiple windows application forms. This particular form Edits or Deletes rows from the Database using a DataAdapter, Dataset, and Command Builder. It has been working fine, until I switched computers. Now the Dataset is being updated, but the Database is not.

Here is the full code of this form:

private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("Exit Cook Book?", "Exit?", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {
        Application.Exit();
    }
}

private void goBackToolStripMenuItem_Click(object sender, EventArgs e)
{
    AddRecipe goBack = new AddRecipe();

    Close();
    goBack.Show();
}

private void helpToolStripMenuItem_Click(object sender, EventArgs e)
{
    MessageBox.Show("Scan through the Cook Book to find recipes that you wish to edit or delete.", "Help!");
}

SqlConnection con;
SqlDataAdapter dataAdapt;
DataSet dataRecipe;
SqlCommandBuilder cb;

int MaxRows = 0;
int inc = 0;


private void EditRecipe_Load(object sender, EventArgs e)
{
    con = new SqlConnection();
    dataRecipe = new DataSet();

    con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Recipes.mdf;Integrated Security=True;User Instance=True";

        con.Open();

        //MessageBox.Show("Database Open");

        string sql = "SELECT* From CookBookRecipes";
        dataAdapt = new SqlDataAdapter(sql, con);

        dataAdapt.Fill(dataRecipe, "CookBookRecipes");
        NavigateRecords();
        MaxRows = dataRecipe.Tables["CookBookRecipes"].Rows.Count;

        con.Close();
}


private void NavigateRecords()
{
    DataRow dRow = dataRecipe.Tables["CookBookRecipes"].Rows[inc];

    tbRName.Text = dRow.ItemArray.GetValue(0).ToString();
    listBox1.SelectedItem = dRow.ItemArray.GetValue(1).ToString();
    tbRCreate.Text = dRow.ItemArray.GetValue(2).ToString();
    tbRIngredient.Text = dRow.ItemArray.GetValue(3).ToString();
    tbRPrep.Text = dRow.ItemArray.GetValue(4).ToString();
    tbRCook.Text = dRow.ItemArray.GetValue(5).ToString();
    tbRDirections.Text = dRow.ItemArray.GetValue(6).ToString();
    tbRYield.Text = dRow.ItemArray.GetValue(7).ToString();
    textBox1.Text = dRow.ItemArray.GetValue(8).ToString();
}

private void btnNext_Click(object sender, EventArgs e)
{
    if (inc != MaxRows - 1)
    {
        inc++;
        NavigateRecords();
    }
    else
    {
        MessageBox.Show("That's the last recipe of your Cook Book!", "End");
    }
}

private void btnBack_Click(object sender, EventArgs e)
{
    if (inc > 0)
    {
        inc--;
        NavigateRecords();
    }
    else
    {
        MessageBox.Show("This is the first recipe of your Cook Book!", "Start");
    }
}

private void btnSave_Click(object sender, EventArgs e)
{
    cb = new SqlCommandBuilder(dataAdapt);

    DataRow daRow = dataRecipe.Tables["CookBookRecipes"].Rows[inc];

    daRow[0] = tbRName.Text;
    daRow[1] = listBox1.SelectedItem.ToString();
    daRow[2] = tbRCreate.Text;
    daRow[3] = tbRIngredient.Text;
    daRow[4] = tbRPrep.Text;
    daRow[5] = tbRCook.Text;
    daRow[6] = tbRDirections.Text;
    daRow[7] = tbRYield.Text;
    daRow[8] = textBox1.Text;

    if (MessageBox.Show("You wish to save your updates?", "Save Updates?", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {

        dataAdapt.Update(dataRecipe, "CookBookRecipes");

        MessageBox.Show("Recipe Updated", "Update");
    }
}

private void btnDelete_Click(object sender, EventArgs e)
{
    SqlCommandBuilder cb;
    cb = new SqlCommandBuilder(dataAdapt);

    if (MessageBox.Show("You wish to DELETE this recipe?", "Delete?", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {
        dataRecipe.Tables["CookBookRecipes"].Rows[inc].Delete();
        MaxRows--;
        inc = 0;
        NavigateRecords();

        dataAdapt.Update(dataRecipe, "CookBookRecipes");

        MessageBox.Show("Your Recipe has been Deleted", "Delete");
    }
}

This is supposed to update the table:

dataAdapt.Update(dataRecipe, "CookBookRecipes");

I'm not getting any errors, but the data table just won't update.

Thanks in advance for your help, and just let me know if you need more information.

Pippa

Did you ever get an answer on this? I am having the same problem with a project I built to handle my Db updates. Using it from one project, it fails just as you note. Using it from another, it works fine. So I'm really stumped. I have even compared the SqlAdapter.GetUpdateCommand().CommandText from the two and they are exactly the same as are the ItemArray data elements. I don't really understand all of the parameters in the auto-generated INSERT command, though. RowStates are also the same. Frustrating. – Socher

Answer 1

In order to update the data on the database your SqlDataAdapter need to have its InsertCommand, UpdateCommand, DeleteCommand properties set. The SqlCommandBuilder instance that you've created has these commands but you need to set them to your SqlDataAdapter.

In other worlds: Somewhere between

 SqlCommandBuilder cb;
 cb = new SqlCommandBuilder(dataAdapt);

and

 dataAdapt.Update(dataRecipe, "CookBookRecipes");

you need to

dataAdapt.DeleteCommand = cb.GetDeleteCommand(true);
dataAdapt.UpdateCommand = cb.GetUpdateCommand(true);
dataAdapt.InsertCommand = cb.GetInsertCommand(true);

Metallo

searched all over, and no one mentioned binding the queries the command builder generates to the data adapter. Thanks for this. – Mordvin

Just a note that if you specify the dataAdaptor when creating the SqlCommandBuilder you DON'T need to set the commands, the SqlCommandBuilder registers itself as a listener. Documentation – Glia

Answer 2

What does the SqlCommand for Update look like? I see the command but I don't see any SqlText, that's what you're missing.

You need to define what .Update does by setting .UpdateCommand property on the SqlDataAdapter

This link gives a pretty good breakdown on how to go about it: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.updatecommand.aspx

Mahout

SqlText? Where would I put that? – Pippa

Answer 3

Try the below source.

private void btnSave_Click(object sender, EventArgs e)
{
    cb = new SqlCommandBuilder(dataAdapt);

    //Old source: DataRow daRow = dataRecipe.Tables["CookBookRecipes"].Rows[inc];

    //Added source code
    DataRow daRow = dataRecipe.Tables["CookBookRecipes"].NewRow();

    //Added source code
    dataRecipe.Tables["CookBookRecipes"].AddRow(daRow);

    daRow.BeginEdit();
    daRow[0] = tbRName.Text;
    daRow[1] = listBox1.SelectedItem.ToString();
    daRow[2] = tbRCreate.Text;
    daRow[3] = tbRIngredient.Text;
    daRow[4] = tbRPrep.Text;
    daRow[5] = tbRCook.Text;
    daRow[6] = tbRDirections.Text;
    daRow[7] = tbRYield.Text;
    daRow[8] = textBox1.Text;
    daRow.EndEdit();

    //Reset state of rows to unchanged
    dataRecipe.Tables["CookBookRecipes"].AcceptChanges();
    //Set modified. The dataAdapt will call update stored procedured 
    //for the row that has Modifed row state. 
    //You can also try SetAdded() method for new row you want to insert
    daRow.SetModified();

    if (MessageBox.Show("You wish to save your updates?", "Save Updates?", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {

        dataAdapt.Update(dataRecipe, "CookBookRecipes");

        MessageBox.Show("Recipe Updated", "Update");
    }
}

Pimpernel

I have to work with bulk update on occasion and I ALWAYS forget about marking the row as modified. That's exactly what was needed thank you for the reminder. – Jessjessa

Answer 4

Adding AcceptChangesDuringUpdate before Update works for me, example :

foreach (string tableName in tableNames)
        {             
            da = new SqlDataAdapter("SELECT * FROM " + tableName, cn);
            cb = new SqlCommandBuilder(da); //initialise the update, insert and delete commands of da
            da.AcceptChangesDuringUpdate = true;
            da.Update(myDataSet, tableName);               
        }

Ronna

Answer 5

You might need

DataAdapeter.AcceptChanges()

Hedrick

DataAdapter does not have a AcepptChanges() method. Actually, if you call that method, say from a DataSet before calling the the DataAdapter.Update() method, you will set all row states as Unchanged and prevent DataAdapter.Update() to search for flags in the modified rows, and therefore, prevent this method to pick the appropriate INSERT, UPDATE, DELETE command. – Suint

Answer 6

I had the same issue: Filled a new Dataset with some new rows, but nothing happened on update. I've used the MySqlDataAdapter which works similar.

It turns out that when you need the InsertCommand from the MySqlCommandBuilder you have to specify the rowstate as added. See also: MSDN

Tycho

Answer 7

//change this line

DataRow daRow = dataRecipe.Tables["CookBookRecipes"].NewRow();

daRow[0] = tbRName.Text;
daRow[1] = listBox1.SelectedItem.ToString();
daRow[2] = tbRCreate.Text;
daRow[3] = tbRIngredient.Text;
daRow[4] = tbRPrep.Text;
daRow[5] = tbRCook.Text;
daRow[6] = tbRDirections.Text;
daRow[7] = tbRYield.Text;
daRow[8] = textBox1.Text;

if (MessageBox.Show("You wish to save your updates?", "Save Updates?", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//add & change this too
dataRecipe.Tables["CookBookRecipes"].Rows.Add(daRow);
    dataAdapt.Update(dataRecipe, "CookBookRecipes");

    MessageBox.Show("Recipe Updated", "Update");
}

}

Basel

Answer 8

I have encountered the same problem. My dataadapter.fill works but dataadapter.update does not work. I realised the problem was that my database table does not contain a primary key. After I modified my table to include a column with primary key, dataadapter.fill works. Hope this helps someone.

Alie

Answer 9

Check the properties of your Database If you are using Local-DB.

In "Copy to Output Directory" property set the value "Copy if newer" and you are good to go.

Byssus

Answer 10

If the database you're using is added to your project, its "Copy to Output Directory" property might be set to "Copy always."

If it is, try changing it to "Copy if newer."

I got this solution from here.

Lysias