CODEWITHSUNDEEP
c#sql-serverwinformsdatagridviewado.net
Reeggiie
Reeggiie

Reputation: 822

Why wont DataGridView Update Database?

I have a DataGridView populated with a DataSet from my Database. I am trying to make changes in the DataGridView and apply those changes to the Database whenever I press 'Enter'.

Iv read alot of this same question, and researched the topic, but am still having trouble figuring out why I cannot apply changes made in my DataGridView to my Database. (I know this has been asked before, but still cant figure this out).

Can anyone show me what im doing wrong?

DataSet ds = new DataSet();
string constring = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlDataAdapter da;
public ListForm()
{
    //Setting up DataGridView with data.
    InitializeComponent();
    da = new SqlDataAdapter("Select * from Contact_List", constring);

    SqlCommandBuilder cmb = new SqlCommandBuilder(da);
    da.UpdateCommand = cmb.GetUpdateCommand();
    da.Fill(ds, "Contact_List");
    dataGridView1.DataSource = ds;
    dataGridView1.DataMember = "Contact_List";
}

//Trying to update database with DataAdapter
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    using (SqlConnection con = new SqlConnection(constring))
    {
        con.Open();
        //I believe that the changes to the database should be applied here
        //Buts its not working
        da.Update(ds, "Contact_List",); 
        ds.AcceptChanges();
        con.Close();
    }  
}

Upvotes: 1

Views: 143

Answers (2)

Reza Aghaei
Reza Aghaei

Reputation: 125322

You should end current edit before trying to save changes:

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    var cm = dataGridView1.BindingContext[ds, "Contact_List"];
    cm.EndCurrentEdit();
    da.Update(ds, "Contact_List");
}

Some other notes

  • You don't need a DataSet. A DataTable is enough.
  • When you create a SqlCommandBuilder by passing a SqlDataAdapter to the constructor, all insert, update and delete commands will be generated automatically and you don;t need to do anything yourself, so GetUpdateCommand() is not necessary.
  • Calling EndCurrentEdit cause the changes which you made on an IEditableObject be saved to underlying data source. DataRowView which is the object behind rows of grid is an IEditableObject and you should call EndCurrentEdit of the currency manager which cause EndEdit of DataRowView be called and commits changes to the underlying DataRow and ends the editing session.
  • If you bind the grid to a BindingSource, calling EndEdit of BindingSource will do the same.
  • After saving data, you don't need to call AcceptChanges manually.
  • You need to add exception handling to code.

Upvotes: 2

Beth
Beth

Reputation: 9617

it's because you're using SqlCommandBuilder, I haven't used it in a long time and I'm looking for more info, but I believe you can only update one table, no joins, and there has to be a unique key defined, otherwise you may want to generate your UPDATE statement manually.

reference

Upvotes: 0

Related Questions