// 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..!
diya0076 0 Newbie Poster
Ramy Mahrous 401 Postaholic Featured Poster
What's the exception?
I believe the problem you've two parameters called 'ID' you should have @OriginalID and @UpdatedID
diya0076 0 Newbie Poster
i got the errror :"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
diya0076 0 Newbie Poster
i got the errror :"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
Ramy Mahrous 401 Postaholic Featured Poster
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?
jen140 0 Junior Poster
If your is "AutoNumber" than its impossible to change it .
Try defining it as an Integer.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.