Saving changes from a DataGridView back to an SQL database?

Question

I'm trying to make a DataGridView that displays data from an SQL database (GSM.sdf) and saves changes made to the DataGridView back to the database when a save button is pressed. The data displays fine, but nothing happens when the save button is pressed. I've been following the top answer from this thread:

http://social.msdn.microsoft.com/Forums/en/csharpgeneral/thread/98bc0b4d-a2ea-4b74-81f0-473da624528a

But it isn't working out. Here is my code:

namespace WindowsFormsApplication5
{
    public partial class Zeelot : Form
    {
        DataTable table = new DataTable();
        SqlCeDataAdapter z;
        DataSet gSMDataSet = new DataSet();
        public Zeelot()
        {
            InitializeComponent();
        }
        private void Zeelot_Load(object sender, EventArgs e)
        {
            string b = @"Data Source =.\SQLEXPRESS;database=GSM;Integrated Security=FALSE;Connection Timeout=30;User Instance=FALSE";
            SqlCeConnection conn = new SqlCeConnection(b);
            conn.Open();
            string cd = "SELECT * FROM PhoneNumbers";
            z = new SqlCeDataAdapter(cd, conn);
            z.Fill(gSMDataSet, "PhoneNumbers");
            table = gSMDataSet.Tables[0];
            conn.Close();
            dataGridView1.DataSource = table;
        }
        private void SaveButton_Click(object sender, EventArgs e)
        {
            SqlCeCommandBuilder local_SqlCommandBuilder = new SqlCeCommandBuilder(z);
            local_SqlCommandBuilder.ConflictOption = System.Data.ConflictOption.OverwriteChanges;
            z.UpdateCommand = local_SqlCommandBuilder.GetUpdateCommand();
            z.Update(((System.Data.DataTable)this.dataGridView1.DataSource));
            ((System.Data.DataTable)this.dataGridView1.DataSource).AcceptChanges();
        }

    }
}

Answer 1

I believe your problem lies where you are casting a DataSource to System.Data.DataTable

Try z.Update(gSMDataSet);

I also don't believe you need AcceptChanges()

Answer 2

1. you dont need to call .AcceptChanges() on the DataGridView at the end. AcceptChanges() submits the changes from your DGV to the connected DataSource (your DataTable)

Try calling AcceptChanges() before you try to submit the changes to your Database.

1. I think your problem is with your connection. You manually create and open a connection and assign it to your DataAdapter. But within your _Load() Method, you close the connection. As the DataAdapter used inside the _Click EventHandler is the same as the one used in your _Load() Method (therefore uses the same connection). It can't submit any changes to your Database because you already closed the connection.

Don't you get any exceptions at runtime?

Try using breakpoints to examine the current state of your connection Object before you attempt to submit your changes to your Database.

Also, AFAIK MS advises against creating connections manually if you want to use them within a DataAdapter.

DataAdapter's Constructor can create a connection on its own