dataadapter.update() does not save to database

Question

The following code is not saving the changes from the dataset to the database via the dataadapter.update(). I display the data on a winform to text boxes.

I have a save button that should save the changes made to the database. the changes are only saved to the in memory copy of the dataset. what am i missing to get this to save the changes to the database?

public partial class Frm_Main : Form
{
    DataSet ds = new DataSet();
    SqlDataAdapter adapter = new SqlDataAdapter();

    BindingSource binding_Login = new BindingSource();
    SqlCommandBuilder builder = new SqlCommandBuilder();
    SqlConnection connection = new SqlConnection();
    SqlCommand sqlcommand = new SqlCommand();

    public Frm_Main()
    {
        InitializeComponent();

    }

    private void FrmMain_Load(object sender, EventArgs e)
    {
       this.Text = "Main (" + GlobalVars.username.ToString() + ")";
       this.AcceptButton = btnSearch;
       connection.ConnectionString = GlobalVars.sqlConnString;
    }

    private void FrmMain_Close(object sender, EventArgs e)
    {
        Close();
    }

    private void btnSearch_Click(object sender, EventArgs e)
    {

        if(!string.IsNullOrEmpty(txtSearch.Text))
        {
            Search();

        }
    }


    public void Search()
    {
        string sqlcommandstring = "select * from login where loginname like @search;";

        connection.Open();

        sqlcommand.CommandText = sqlcommandstring;
        sqlcommand.Connection = connection;

        sqlcommand.Parameters.AddWithValue("@search", "%" + txtSearch.Text + "%");

        adapter.SelectCommand = sqlcommand ;
        builder.DataAdapter = adapter;

        adapter.Fill(ds,"Login") ;

        BindControls();

        txtLoginName.DataBindings.Add(new Binding("Text", binding_Login, "LoginName"));
        txtPassword.DataBindings.Add(new Binding("Text", binding_Login, "Password"));

        adapter.UpdateCommand = builder.GetUpdateCommand();
        adapter.DeleteCommand = builder.GetDeleteCommand();
        adapter.InsertCommand = builder.GetInsertCommand();
    }

    private void btnNext_Click(object sender, EventArgs e)
    {
        binding_Login.MoveNext();
    }

    protected void BindControls()
    {

        binding_Login.DataSource = ds.Tables[0];

    }

    private void btnPrevious_Click(object sender, EventArgs e)
    {
        binding_Login.MovePrevious();
    }

    private void btnSave_Click(object sender, EventArgs e)
    {

        ds.AcceptChanges();
        adapter.Update(ds.Tables[0]);


    }
}

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, May 11 '15 at 02:03
Are you positive that your global for your `DataSet` is actually populated when you try to save? If you don't call search, it won't have any data and would be empty. — Greg, May 11 '15 at 03:29

score 0 · Answer 1 · answered May 15 '15 at 23:06

0

I was able to resolve the issue by changing the save buttons click event to the following:

private void btnSave_Click(object sender, EventArgs e)
    {
        this.binding_Login.EndEdit();
        adapter.Update(this.ds.Tables[0]);


    }

answered May 15 '15 at 23:06

Jason

21
2

score 0 · Answer 2 · answered May 07 '21 at 23:06

The problem was in this line:

private void btnSave_Click(object sender, EventArgs e)
{

    ds.AcceptChanges();//EDIT This is the problem!
    adapter.Update(ds.Tables[0]);


}

I had a similar problem and during debbuging I realized that if you call .AcceptChanges() before DataAdapter.Update(), all your modified rows will change their status to Unchanged. This means that DataAdapter.Update() will lose all the flags it needs to pick the right INSERT, UPDATE, DELETE command.

I also had problems using editing batches like:


row.BeginEdit();

// Modify several rows

row.EndEdit();

As I understand, the problem here happens because all changes are reserved until you call the AcceptChanges() method, thus causing all the row state flags to be set as Unchanged, making DataAdapter.Update() essentially blind.

In short:

Create a DataAdapter.
Set the InsertCommand, UpdateCommand, DeleteCommand, SelectCommand.
Fill a DataSet, DataTable, DataRow[], with the adapter.
Make changes to your DataSet, DataTable, DataRow[].
Make sure these changes are flagged in the RowState property of the modified row(s).
- To ensure this, don't use batch editing, and don't call AcceptChanges(), before the DataAdapter.Update() method.
Using the same adapter, call adapter.Update(DataSet|DataTable|DataRow[]).

dataadapter.update() does not save to database

2 Answers2