I am connectiin MS access with C# using data Adapters and dataset and then view the result on dataGridView by binding dataset with it. Now my database has a field called KEY which is not populated. I want to populate the KEY field at runtime using other fields. I do it using a dataset and that works fine and changes are visible on the grid but those changes are not reflected back to the database. Here is the code which i wam using:using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.OleDb;using control;
namespace handler{ class StudentDBHandler { string query = ""; string u_query = ""; int count = 0; int[] index_arr; string[] name_arr; string[] key_arr; Boolean status = true; DataSet dataset; OleDbDataAdapter dataAdapter; OleDbCommandBuilder commandBuilder;
OleDbConnection objConnection = null;
string connectionString = ""; public DataSet selectAllRecords() { dataAdapter = null; dataset = new DataSet(); string path = "DATA.mdb"; query = "SELECT * FROM DATA"; connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=" + path; try { dataAdapter = new OleDbDataAdapter(query, connectionString); commandBuilder = new OleDbCommandBuilder(dataAdapter); // fill the data set object dataAdapter.Fill(dataset, "AllRecords"); count = dataset.Tables[0].Rows.Count; index_arr = new int[count]; name_arr = new string[count]; key_arr = new string[count]; if (status) { for (int i = 0; i < count; i++) { index_arr[i] = (int)dataset.Tables["AllRecords"].Rows[i]["ID"]; name_arr[i] = (string)dataset.Tables["AllRecords"].Rows[i]["NAME"]; key_arr[i] = name_arr[i].Substring(0, 3); } }//end if }//end of try catch (Exception exc) { Console.WriteLine(exc.Message); }//end of catch return dataset;
}//end of the method selectAllReords
public Boolean generateKey() { objConnection = new OleDbConnection(connectionString); objConnection.Open(); Console.WriteLine(count); for (int i = 0; i < index_arr.GetLength(0) && i < name_arr.GetLength(0); i++) { dataset.Tables["AllRecords"].Rows[i]["KEY"] = key_arr[i]; } try { //dataAdapter.Update(dataset,"AllRecords"); } catch (Exception e) { Console.WriteLine("exception..........." + e.Message.ToString()); } dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); dataAdapter.Update(dataset, "AllRecords"); dataset.AcceptChanges(); status = false; objConnection.Close(); return true; } }//end of class}
The method called generateKey is use to update the database but inspite of updating the database it gives exception "Syntax error in UPDATE" at :
dataAdapter.Update(dataset, "AllRecords");
Please look into the problem and make some changes in the above code so that it works fine.
Thanks