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