programmatically write changes back to Access 2007 using C#

[Ming Luo]

Dear all: I am trying to use C# to communicate a small database build in Access 2007. I can read from the database and I can modify the values and save the changes back to database using the DataGridView. However, I am not sure how to modify the values programmatically. Here is the code building the connection.

string g_ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TestDB.accdb";

DataSet g_ds= new DataSet();
OleDbConnection g_con = new OleDbConnection(g_ConnectionString);
OleDbDataAdapter g_adapter = new OleDbDataAdapter("Select * from qrySuggestion", g_con);
g_adapter.Fill(g_ds, "qrySuggestion");

this.dataGridView1.DataSource = g_ds.Tables[0];
g_con.Close();

I have a button on the form and when the button is clicked, save the changes back to the database. The code is as follows:

   public void SaveTable(string tblName, DataTable tbl)
    {
        g\_adapter = new OleDbDataAdapter("select \* from " + tblName, g\_con);

        OleDbCommandBuilder bu = new OleDbCommandBuilder(g\_adapter);
        
        g\_adapter.UpdateCommand = bu.GetUpdateCommand();
        g\_adapter.Update(tbl);
        
        tbl.AcceptChanges();

        g\_con.Close();
    }

I can use the above code to read from the database and display the data in the DataGridView with no problem. And I can make modification in the DataGridView and save it back by calling the SaveTable method. However, the problem is that I want to make modification programmatically and reflect the changes back to the database. What I did was to add two statement at the beginning of the SaveTable method as:

DataRow row = tbl.Rows[0];
row[0] = 30;

assuming the column 0 i.e. row[0] is a int field. When I add those two statement at the beginning of the SaveTable method. An exception will occur at g_adapter.Update(tbl); the exception is something like:

Missing DataColumn"pk_ID" in SourceColumn "pk_ID" in DataTable "tblSuggestionBase"

Can anyone help me out with the problem please? Thanks

Asura