CODEWITHSUNDEEP
c#visual-studio-2008
Manos Dilaverakis
Manos Dilaverakis

Reputation: 5869

Cannot commit DataSet changes to database

I've written a small form that reads the data from a database table (SQL CE 3.5) and displays it in a DataGridView control. This works fine. I then modified it to make a change to the data before displaying it, which also seems to work fine with the exception that it doesn't seem to actually commit the changes to the database. The code is as follows:

        using (SqlCeConnection conn = new SqlCeConnection(
            Properties.Settings.Default.Form1ConnectionString
            )) {

            conn.Open();
            using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(
                "SELECT * FROM People", conn
                )) {

                //Database update command
                adapter.UpdateCommand = new SqlCeCommand(
                    "UPDATE People SET name = @name " +
                    "WHERE id = @id", conn);
                adapter.UpdateCommand.Parameters.Add(
                    "@name", SqlDbType.NVarChar, 100, "name");
                SqlCeParameter idParameter = adapter.UpdateCommand.Parameters.Add(
                    "@id", SqlDbType.Int);
                idParameter.SourceColumn = "id";
                idParameter.SourceVersion = DataRowVersion.Original;

                //Create dataset
                DataSet myDataSet = new DataSet("myDataSet");
                DataTable people = myDataSet.Tables.Add("People");

                //Edit dataset
                adapter.Fill(myDataSet, "People");
                people.Rows[0].SetField("name", "New Name!");
                adapter.Update(people);

                //Display the table contents in the form datagridview
                this.dataGridView1.DataSource=people;
            }
        }

The form displays like so:

alt text

Looking at the table via Visual Studio's Server Explorer however, doesn't show any change to the table.

What am I doing wrong?

Upvotes: 0

Views: 1755

Answers (3)

Manos Dilaverakis
Manos Dilaverakis

Reputation: 5869

I found it. It took days but I found it.

Properties.Settings.Default.Form1ConnectionString is "Data Source=|DataDirectory|\Form1.sdf". The update works if I replace the automatically generated "|DataDirectory|" with the actual path. Oddly enough reading from the database works either way.

Upvotes: 1

Rich
Rich

Reputation: 2096

I would make sure the DataSet believes it's been changed. Invoke DataSet.HasChanges (returns bool) and DataSet.GetChanges, which returns a delta of the DataSet from the original.

Have you also tried this against Sql Server Express just to eliminate any issues with the CE data adapter?

Upvotes: 0

ktharsis
ktharsis

Reputation: 3190

Shouldn't the update line be 

adapter.Update(myDataSet, "People")

Upvotes: 0

Related Questions