// i m getting error when i update ID field of table.. all others field can be updated bt nt the field which i have used as where conditions of update command..!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsApplication1
{
    public partial class frmshowrecords : Form
    {
        public static string con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ "F:/Zcollege/mscit/WindowsApplication2/WindowsApplication2/hotel.mdb";
        private OleDbDataAdapter da;
        private DataSet ds;
        private DataTable dt;
        //private OleDbConnection conn;

        // Database objects
        private static string Id;
        private static string cname;
        private static string addr;
        private static string city;
        private static string contact;
        private static string state;
        private static string country;
        private static string person;
        private static string child;

        private static string rtype;
        private static string rent;
        private static string adate;
        //private static string atime;
        private static string rno;

        private string dq = "select * from newcusto";
        public int cnt = 0;
        public string id;

        public frmshowrecords()
        {
            InitializeComponent();
            da = new OleDbDataAdapter(dq, con);
            BuildCommands();
            ds = new DataSet();
            da.Fill(ds, "newcusto");
            Fill_lb();

        }
        private void Fill_lb()
        {
            txtid.Text = ds.Tables[0].Rows[0][0].ToString();
            txtname.Text = ds.Tables[0].Rows[0][1].ToString();
            txtadd.Text = ds.Tables[0].Rows[0][2].ToString();
            txtcity.Text = ds.Tables[0].Rows[0][3].ToString();
            txtcon.Text = ds.Tables[0].Rows[0][4].ToString();
            txtstat.Text = ds.Tables[0].Rows[0][5].ToString();
            txtcountry.Text = ds.Tables[0].Rows[0][6].ToString();
            txtperson.Text = ds.Tables[0].Rows[0][7].ToString();
            txtchild.Text = ds.Tables[0].Rows[0][8].ToString();
            cmbroomtype.Text = ds.Tables[0].Rows[0][9].ToString();
            txtrent.Text = ds.Tables[0].Rows[0][10].ToString();
            txtdate.Text = ds.Tables[0].Rows[0][11].ToString();
            cmbroomno.Text = ds.Tables[0].Rows[0][13].ToString();
            dt = ds.Tables[0];


        }
        private void LoadBuffers(DataRow prow)
        {
            Id = prow["ID"].ToString().Trim();
            cname = prow["cname"].ToString().Trim();
            addr = prow["addr"].ToString().Trim();
            city = prow["city"].ToString().Trim();
            contact = prow["contact"].ToString().Trim();
            state = prow["state"].ToString().Trim();
            country = prow["country"].ToString().Trim();
            person = prow["person"].ToString().Trim();
            child = prow["child"].ToString().Trim();
            rtype = prow["roomtype"].ToString().Trim();
            rent = prow["rent"].ToString().Trim();
            adate = prow["adate"].ToString().Trim();
            rno = prow["roomno"].ToString().Trim();
        }

        private void BuildCommands()
        {
            // Use the select command's connection again
            OleDbConnection connection =
                (OleDbConnection)da.SelectCommand.Connection;

            // Declare a reusable insert command with parameters
            da.InsertCommand = connection.CreateCommand();
            da.InsertCommand.CommandText =
                "insert into newcusto" +
                "(ID, cname,addr,city,contact,state,country,person,child,roomtype,rent,adate,roomno) " +
                "values " +
                "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            da.InsertCommand.Parameters.Add("ID", OleDbType.Char, 0, "ID");
            da.InsertCommand.Parameters.Add("cname", OleDbType.Char, 0, "cname");
            da.InsertCommand.Parameters.Add("addr", OleDbType.Char, 0, "addr");
            da.InsertCommand.Parameters.Add("city", OleDbType.Char, 0, "city");
            da.InsertCommand.Parameters.Add("contact", OleDbType.Char, 0, "contact");
            da.InsertCommand.Parameters.Add("state", OleDbType.Char, 0, "state");
            da.InsertCommand.Parameters.Add("country", OleDbType.Char, 0, "country");
            da.InsertCommand.Parameters.Add("person", OleDbType.Char, 0, "person");
            da.InsertCommand.Parameters.Add("child", OleDbType.Char, 0, "child");
            da.InsertCommand.Parameters.Add("roomtype", OleDbType.Char, 0, "roomtype");
            da.InsertCommand.Parameters.Add("rent", OleDbType.Char, 0, "rent");
            da.InsertCommand.Parameters.Add("adate", OleDbType.Char, 0, "adate");

            da.InsertCommand.Parameters.Add("roomno", OleDbType.Char, 0, "roomno");


            // Declare a reusable update command with parameters

            da.UpdateCommand = connection.CreateCommand();
            da.UpdateCommand.CommandText = "update newcusto " +
            "set ID= ? , cname = ? ,addr = ? , city= ? ,contact = ? , state= ? ,country = ? , person= ? ,child = ? , roomtype= ? ,rent = ? ,adate= ? , roomno= ? " +
        [COLOR="Green"] "where ID = ? ";[/COLOR]

            da.UpdateCommand.Parameters.Add("ID", OleDbType.Char, 0, "ID");
            da.UpdateCommand.Parameters.Add("cname", OleDbType.Char, 0, "cname");
            da.UpdateCommand.Parameters.Add("addr", OleDbType.Char, 0, "addr");
            da.UpdateCommand.Parameters.Add("city", OleDbType.Char, 0, "city");
            da.UpdateCommand.Parameters.Add("contact", OleDbType.Char, 0, "contact");
            da.UpdateCommand.Parameters.Add("state", OleDbType.Char, 0, "state");
            da.UpdateCommand.Parameters.Add("country", OleDbType.Char, 0, "country");
            da.UpdateCommand.Parameters.Add("person", OleDbType.Char, 0, "person");
            da.UpdateCommand.Parameters.Add("child", OleDbType.Char, 0, "child");
            da.UpdateCommand.Parameters.Add("roomtype", OleDbType.Char, 0, "roomtype");
            da.UpdateCommand.Parameters.Add("rent", OleDbType.Char, 0, "rent");
            da.UpdateCommand.Parameters.Add("adate", OleDbType.Char, 0, "adate");
            da.UpdateCommand.Parameters.Add("roomno", OleDbType.Char, 0, "roomno");
            da.UpdateCommand.Parameters.Add("ID", OleDbType.Char, 0, "ID");
          //  da.UpdateCommand.Parameters.Add(id, OleDbType.Char, 0, id);

            // Declare a reusable delete command with parameters
            da.DeleteCommand = connection.CreateCommand();
            da.DeleteCommand.CommandText =
                "delete from newcusto where ID= ?";
            da.DeleteCommand.Parameters.Add("ID", OleDbType.Char, 0, "ID");

        }
        private void btnmovef_Click(object sender, EventArgs e)
        {
            BindingContext[ds.Tables[0]].Position = 0;
            cnt = BindingContext[ds.Tables[0]].Position;
            txtid.Text = ds.Tables[0].Rows[0][0].ToString();
            txtname.Text = ds.Tables[0].Rows[0][1].ToString();
            txtadd.Text = ds.Tables[0].Rows[0][2].ToString();
            txtcity.Text = ds.Tables[0].Rows[0][3].ToString();
            txtcon.Text = ds.Tables[0].Rows[0][4].ToString();
            txtstat.Text = ds.Tables[0].Rows[0][5].ToString();
            txtcountry.Text = ds.Tables[0].Rows[0][6].ToString();
            txtperson.Text = ds.Tables[0].Rows[0][7].ToString();
            txtchild.Text = ds.Tables[0].Rows[0][8].ToString();
            cmbroomtype.Text = ds.Tables[0].Rows[0][9].ToString();
            txtrent.Text = ds.Tables[0].Rows[0][10].ToString();
            txtdate.Text = ds.Tables[0].Rows[0][11].ToString();
            cmbroomno.Text = ds.Tables[0].Rows[0][13].ToString();
        }

        private void btnmovep_Click(object sender, EventArgs e)
        {
            int c = Convert.ToInt32(ds.Tables[0].Rows.Count.ToString());

            if ((BindingContext[ds.Tables[0]].Position) == 0)
            {

                MessageBox.Show("Begin of Records Reached!!");


            }
            else
            {
                int i = BindingContext[ds.Tables[0]].Position -= 1;
                BindingContext[ds.Tables[0]].Position = i;

                txtid.Text = cmbroomno.Text = ds.Tables[0].Rows[i][0].ToString();
                txtname.Text = ds.Tables[0].Rows[i][1].ToString();
                txtadd.Text = ds.Tables[0].Rows[i][2].ToString();
                txtcity.Text = ds.Tables[0].Rows[i][3].ToString();
                txtcon.Text = ds.Tables[0].Rows[i][4].ToString();
                txtstat.Text = ds.Tables[0].Rows[i][5].ToString();
                txtcountry.Text = ds.Tables[0].Rows[i][6].ToString();
                txtperson.Text = ds.Tables[0].Rows[i][7].ToString();
                txtchild.Text = ds.Tables[0].Rows[i][8].ToString();
                cmbroomtype.Text = ds.Tables[0].Rows[i][9].ToString();
                txtrent.Text = ds.Tables[0].Rows[i][10].ToString();
                txtdate.Text = ds.Tables[0].Rows[i][11].ToString();
                cmbroomno.Text = ds.Tables[0].Rows[i][13].ToString();
            }

        }

        private void btnmoven_Click(object sender, EventArgs e)
        {
            int c = Convert.ToInt32(ds.Tables[0].Rows.Count.ToString());

            //MessageBox.Show(i.ToString());

            if ((BindingContext[ds.Tables[0]].Position) == c - 1)
            {

                MessageBox.Show("End of Records Reached!!");
                Fill_lb();
                BindingContext[ds.Tables[0]].Position = 0;


            }
            else
            {
                int i = BindingContext[ds.Tables[0]].Position += 1;
                BindingContext[ds.Tables[0]].Position = i;
                txtid.Text = cmbroomno.Text = ds.Tables[0].Rows[i][0].ToString();
                txtname.Text = ds.Tables[0].Rows[i][1].ToString();
                txtadd.Text = ds.Tables[0].Rows[i][2].ToString();
                txtcity.Text = ds.Tables[0].Rows[i][3].ToString();
                txtcon.Text = ds.Tables[0].Rows[i][4].ToString();
                txtstat.Text = ds.Tables[0].Rows[i][5].ToString();
                txtcountry.Text = ds.Tables[0].Rows[i][6].ToString();
                txtperson.Text = ds.Tables[0].Rows[i][7].ToString();
                txtchild.Text = ds.Tables[0].Rows[i][8].ToString();
                cmbroomtype.Text = ds.Tables[0].Rows[i][9].ToString();
                txtrent.Text = ds.Tables[0].Rows[i][10].ToString();
                txtdate.Text = ds.Tables[0].Rows[i][11].ToString();
                cmbroomno.Text = ds.Tables[0].Rows[i][13].ToString();
            }
        }

        private void btnmovel_Click(object sender, EventArgs e)
        {
            int c = Convert.ToInt32(ds.Tables[0].Rows.Count.ToString());

            BindingContext[ds.Tables[0]].Position = c-1;
            txtid.Text = ds.Tables[0].Rows[c - 1][0].ToString();
            txtname.Text = ds.Tables[0].Rows[c - 1][1].ToString();
            txtadd.Text = ds.Tables[0].Rows[c - 1][2].ToString();
            txtcity.Text = ds.Tables[0].Rows[c - 1][3].ToString();
            txtcon.Text = ds.Tables[0].Rows[c - 1][4].ToString();
            txtstat.Text = ds.Tables[0].Rows[c - 1][5].ToString();
            txtcountry.Text = ds.Tables[0].Rows[c - 1][6].ToString();
            txtperson.Text = ds.Tables[0].Rows[c - 1][7].ToString();
            txtchild.Text = ds.Tables[0].Rows[c - 1][8].ToString();
            cmbroomtype.Text = ds.Tables[0].Rows[c - 1][9].ToString();
            txtrent.Text = ds.Tables[0].Rows[c - 1][10].ToString();
            txtdate.Text = ds.Tables[0].Rows[c - 1][11].ToString();
            cmbroomno.Text = ds.Tables[0].Rows[c - 1][13].ToString();
        }

        private void btndelete_Click(object sender, EventArgs e)
        {
            DataRow selectedRow = dt.Rows[BindingContext[ds.Tables[0]].Position];
            string msg = selectedRow["ID"] + " deleted.";
            selectedRow.Delete();


            // apply delete to the database
            try
            {
                da.Update(ds, "newcusto");
                ds.AcceptChanges();


                // inform the user
                MessageBox.Show(msg);
                Application.DoEvents();
            }
            catch (OleDbException ex)
            {
                ds.RejectChanges();
                MessageBox.Show(ex.Message);
            }
            Fill_lb();
        }

        private void btnupdate_Click(object sender, EventArgs e)
        {

            DataRow selectedRow = dt.Rows[BindingContext[ds.Tables[0]].Position];
            LoadBuffers(selectedRow);

            Application.DoEvents();

            // Begin an edit transaction on the row.

            selectedRow.BeginEdit();
            selectedRow["ID"] = txtid.Text.Trim().ToString();
            selectedRow["cname"] = txtname.Text.Trim();
            selectedRow["addr"] = txtadd.Text.Trim();
            selectedRow["city"] = txtcity.Text.Trim();
            selectedRow["contact"] = txtcon.Text.Trim();
            selectedRow["state"] = txtstat.Text.Trim();
            selectedRow["country"] = txtcountry.Text.Trim();
            selectedRow["person"] = txtperson.Text.Trim();
            selectedRow["child"] = txtchild.Text.Trim();
            selectedRow["adate"] = txtdate.Text.Trim();
            selectedRow["roomtype"] = cmbroomtype.Text.Trim();
            selectedRow["rent"] = txtrent.Text.Trim();
            selectedRow["roomno"] = cmbroomno.Text.Trim();
            selectedRow.EndEdit();

            DataSet dsChanges =
                    ds.GetChanges(DataRowState.Modified);
            //MessageBox.Show(id.ToString());
            // inform the user

            bool okayFlag = true;
            if (dsChanges.HasErrors)
            {
                okayFlag = false;
                string msg = "Error in row";

                // Look at each table in the dataSet
                foreach (DataTable currTable in dsChanges.Tables)
                {
                    // Find the rows with errors if any table has errors
                    if (currTable.HasErrors)
                    {
                        // fetch the error rows
                        DataRow[] errorRows = currTable.GetErrors();

                        // Go through the rows and identify the ones
                        // with errors
                        foreach (DataRow currRow in errorRows)
                        {
                            msg = msg + currRow["id"];
                        }
                    }
                }
                MessageBox.Show(msg);
            }

            // No errors -- all okay
            if (okayFlag)
            {
                // apply updates to the database

                da.Update(ds, "newcusto");
                ds.AcceptChanges();

                // tell the user
                MessageBox.Show("Updated " + selectedRow["cname"]);
                Application.DoEvents();
            }
            else // if any errors then throw out the changes
                ds.RejectChanges();

        }
            }
        }


// i m getting error when i update ID field of table.. all others field can be updated bt nt the field which i have used as where conditions of update command..!

Edited 3 Years Ago by Dani: Formatting fixed

What's the exception?
I believe the problem you've two parameters called 'ID' you should have @OriginalID and @UpdatedID

i got the errror :"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

i got the errror :"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

Don't try to update the ID! I believe in the concurrency violation occurs regarding this. did you try this update statement in Access Query GUI?

If your is "AutoNumber" than its impossible to change it .
Try defining it as an Integer.

This article has been dead for over six months. Start a new discussion instead.