Waqas

Waqas

  • NA
  • 8
  • 0

ADO.Net

May 2 2008 9:16 AM

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


Answers (2)