How to check weather the data has been update

Greetings,

I have a C# form which will allow the user to edit the record to an Oracle database from form. I want to know how to modified the adapter, so a warning message will popup if the record is dirty(record has been update by other people) when the user try to update/save it from C# form.

I know there is feature when you create an adapter from Form Design. I check-marked that feature for oleDbDataAdapter1 already,but it didn't give me any warning msg when I test it throught SQL Plus.

I have the following code for update:
oleDbDataAdapter1.Update(MyDataSet1);

                  MyDataSet1.AcceptChanges();
                  MessageBox.Show("Record has been updated!");
                  this.Refresh();
            myConn.Close();



Thanks in advance.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ptmcompCommented:
I think you should find the answer in here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/boagag.asp
There are also code samples and more information so I don't post the contents here.
0
TheAvengerCommented:
What the adapter will do is try to update the old record having all the original data in the update query like this (imagina a table users with 3 columns: id, first and last name):

update users set f_name = 'aaa', l_name = 'bbb' where users.id = 5 and users.f_name = 'aaa before' and users.l_name = 'bbb before'

If someone has changed the values in the meantime (so the record is dirty), at least one of the where conditions will fail. So the record will not be updated and you will get an exception that will show the problem, including the record. That's why you did not notice anything from SQL Plus.

If this is not enough for you, you should manually implement some method to do this, like making a stored procedure to update the record and returning a special result if the record was dirty or something similar.
0
mrongAuthor Commented:
Hi TheAvenger,

Here is my SQL stmt and I created it manually:
   
sql = "SELECT * ";
sql += "FROM ROOT.AE_P_PHS_C";
sql += " WHERE PROPOSAL =('"+proposal.Text+"') AND SORT_CODE ('"+phase.Text+"')" ;

I think "update users set f_name = 'aaa', l_name = 'bbb' where users.id = 5 and users.f_name = 'aaa before' and users.l_name = 'bbb before' " will work for me. But what exactly I should put in for 'aaa before' and 'bbb before'?

Thanks.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

mrongAuthor Commented:
TheAvenger,

Here is my code for update and what is the easiest way to modified? Thanks.
private void update_Click(object sender, System.EventArgs e)
            {
                  string sql;  
                  DataSet MyDataSet1 = new DataSet();
             
                  sql = "SELECT * ";
                  sql += "FROM ROOT.AE_P_PHS_C";
                  sql += " WHERE PROPOSAL =('"+proposal.Text+"') AND SORT_CODE =('"+phase.Text+"')" ;
                  
                  OleDbConnection myConn = oleDbConnection1;


                  myConn.Open();

                  
                  OleDbCommand myComm = new OleDbCommand();
                  myComm.Connection = myConn;
                  myComm.CommandText = sql;

            oleDbDataAdapter1.SelectCommand = myComm;
           oleDbDataAdapter1.Fill(MyDataSet1);
                  
            MyDataSet1.Tables[0].Rows[0]["REGION_CODE"]=campus.Text;
                  MyDataSet1.Tables[0].Rows[0]["BLDG"]=bldg.Text;
                  MyDataSet1.Tables[0].Rows[0]["FAC_ID"]=zone.Text;
                  MyDataSet1.Tables[0].Rows[0]["AF1"]=rupo.Text;
                  MyDataSet1.Tables[0].Rows[0]["AF4"]=this.yn.Text;
                  MyDataSet1.Tables[0].Rows[0].EndEdit();
           
                  oleDbDataAdapter1.Update(MyDataSet1);

                  MyDataSet1.AcceptChanges();
                  MessageBox.Show("Record has been updated!");
                  this.Refresh();
            myConn.Close();
           
0
TheAvengerCommented:
The old values from the database. I explained you how the OleXXX work but this is actually a part of the DataSet work. Every data set keeps several instances of every record: the original one and the current one are important for you. So the dataset would say to the adapter: this record was changed, it had original the values 'aaa before' and 'bbb before' and now has the values 'aaa' and 'bbb'. The adapter will then be able to create the correct query.

Now if you implement this yourself, you need to also keep the original values in a separate container and compare it before sending the new values. If you use a data set, you can use the following indexer of DataRow (note, the row) to get the old values:

row[column, DataRowVersion.Original]

I would however suggest that you create a standard data adapter and data set and use this functionality ready
0
TheAvengerCommented:
In your code you can take the old values by the following lines:

oldRegCode = MyDataSet1.Tables[0].Rows[0]["REGION_CODE", DataRowVersion.Original];
oldBLDG = MyDataSet1.Tables[0].Rows[0]["BLDG", DataRowVersion.Original];
0
mrongAuthor Commented:
How to pass the value->'oldRegCode' from my Find( ) function to Update( ) function?
0
TheAvengerCommented:
Just add a param to the Update function and give it a value from the Find function.

void Update (string oldValue) {
....
}

void Find () {
....
Update (oldRegCode);
.....
}
0
mrongAuthor Commented:
So you call the Update function inside of Find function? Why?
Thanks.

0
TheAvengerCommented:
You asked how to send a parameter from one function to another and I showed you a way. If you don't call the update from the find function, then create a member variable,set it's value to the old reg code in the find function and when you call the update function use this member variable to get the old value.
0
mrongAuthor Commented:
Can I declare a global variable for oldRegCode? And how?

0
TheAvengerCommented:
Global variables are not nice (you can make a "global" variable by declaring it static in your class) so it's better to find a way to send parameters from one function to another in this case.
0
mrongAuthor Commented:
Error msg "C:\Documents and Settings\MRong\My Documents\Visual Studio Projects\FMEDB\Form1.cs(1318): Cannot implicitly convert type 'object' to 'string' "
for line->  string oldRegCode = MyDataSet1.Tables[0].Rows[0]["REGION_CODE", DataRowVersion.Original];
0
TheAvengerCommented:
Put (string) in front like this:

string oldRegCode = (string)MyDataSet1.Tables[0].Rows[0]["REGION_CODE", DataRowVersion.Original];

Be sure that the Region code is a string
0
mrongAuthor Commented:
where should I declare the variable oldRegCode? inside of Find function?
0
TheAvengerCommented:
I don't know what you code is, where the find and the update functions are. Without this information I cannot tell you where to put it
0
mrongAuthor Commented:
Here is the structure:

namespace FM
{

Find( ){  }

Update( ) { }


}
0
TheAvengerCommented:
You don't have a class? this is not possible
0
mrongAuthor Commented:
namespace FM
{
public class form1:...{

Find( ){  }

Update( ) { }

}

}
0
TheAvengerCommented:
then just write a member variable in the class:

public class form1:...{
private string oldRegCode;
....
}

then you can use it from both functions
0
mrongAuthor Commented:
TheAvenger,

I add a popup msg as following in my update function:
if (MyDataSet1.Tables[0].Rows[0]["REGION_CODE"].ToString()!=oldRegCode)
                  {
                        
                              MessageBox.Show("Record has been changed by another party. Do you want to overwrite it?.");
                              this.campus.Focus( );
                              return;
                        
                  }

I tested it already and it is able to detect weather the rec is dirty or not. But I'd like to have the msg box in the way that the user can either click on "ok" or "cancel" to decide either save or not save the record. If the user click on 'save', the code will keep going. otherwise focus on form.

Thanks.
0
TheAvengerCommented:
do it like this:
DialogResult res = MessageBox.Show ("Record has been changed by another party. Do you want to overwrite it?.", "Question title", MessageBoxButtons.OKCancel);

if (res == DialogResult.OK)
// update the record
else
// do not update the record
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ptmcompCommented:
You can use the built in functionality for most of it:

  // Add a handler for Row Update events
  MyDataSet1.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);

  // Update the database
    oleDbDataAdapter1.Update(MyDataSet1);
    MyDataSet1.AcceptChanges();
}

protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs
args)
{
  if (args.RecordsAffected == 0)
  {
    DialogResult res = MessageBox.Show ("Record has been changed by another party. Do you want to overwrite it?.", "Question title", MessageBoxButtons.OKCancel);

    if (res == DialogResult.OK)
        // update the record without offline locking
    else
        args.Status = UpdateStatus.SkipCurrentRow;
  }
}
0
mrongAuthor Commented:
I used "if (res != DialogResult.OK)". It should also be fine,right?

0
TheAvengerCommented:
It checks the opposite way, otherwise it's ok. So you have to change also the code in the if and else parts
0
mrongAuthor Commented:
Hi TheAvenger,

Here is my code in Update function,please let me know if it is ok.

private void update_Click(object sender, System.EventArgs e)
            {  
                  
                  string sql;  
                  DataSet MyDataSet1 = new DataSet();
             
            /* Declare the original value for Region_code*/
                  
                  sql = "SELECT * ";
                  sql += "FROM ROOT.AE_P_PHS_C";
                  sql += " WHERE PROPOSAL =('"+proposal.Text+"') AND SORT_CODE =('"+phase.Text+"')" ;
                  
                  OleDbConnection myConn = oleDbConnection1;


                  /*myConn.Open(); */

                  
                  OleDbCommand myComm8 = new OleDbCommand();
                  myComm8.Connection = myConn;
                  myComm8.CommandText = sql;

            oleDbDataAdapter1.SelectCommand = myComm8;
                  oleDbDataAdapter1.Fill(MyDataSet1);

                  if (MyDataSet1.Tables[0].Rows[0]["REGION_CODE"].ToString()!=oldRegCode || MyDataSet1.Tables[0].Rows[0]["FAC_ID"].ToString()!=oldZone|| MyDataSet1.Tables[0].Rows[0]["BLDG"].ToString()!=oldBldg || MyDataSet1.Tables[0].Rows[0]["Af1"].ToString()!=oldRupo || MyDataSet1.Tables[0].Rows[0]["AF4"].ToString()!=oldYN)
                  {
                        
                   DialogResult res = MessageBox.Show ("Record has been changed by another party. Do you want to overwrite it?.", "Warning!", MessageBoxButtons.OKCancel);

                   if (res != DialogResult.OK)
                              this.campus.Focus( );
                              return;
                        
                  }
           
            /*
                  campus.LimitToList = true;
            campus.NotInList += new CancelEventHandler(combobox_NotInList);
                  this.Controls.Add(campus);
                  
                  zone.LimitToList = true;
            zone.NotInList += new CancelEventHandler(combobox_NotInList);
                  this.Controls.Add(zone);
            */


                  MyDataSet1.Tables[0].Rows[0]["REGION_CODE"]=campus.Text;
                  MyDataSet1.Tables[0].Rows[0]["BLDG"]=bldg.Text;
                  MyDataSet1.Tables[0].Rows[0]["FAC_ID"]=zone.Text;
                  MyDataSet1.Tables[0].Rows[0]["AF1"]=rupo.Text;
                  MyDataSet1.Tables[0].Rows[0]["AF4"]=this.yn.Text;
                  MyDataSet1.Tables[0].Rows[0].EndEdit();
           
                  
            oldRegCode = (string)MyDataSet1.Tables[0].Rows[0]["REGION_CODE"];
                  oldZone = (string)MyDataSet1.Tables[0].Rows[0]["FAC_ID"];
                  oldBldg = (string)MyDataSet1.Tables[0].Rows[0]["BLDG"];
                  oldRupo = (string)MyDataSet1.Tables[0].Rows[0]["AF1"];
                  oldYN = (string)MyDataSet1.Tables[0].Rows[0]["AF4"];
            oleDbDataAdapter1.Update(MyDataSet1);

                  MyDataSet1.AcceptChanges();
                  MessageBox.Show("Record has been updated!");
                  this.Refresh();
            myConn.Close();
           

            }
0
TheAvengerCommented:
Add curly brackets here:

if (res != DialogResult.OK) {
                         this.campus.Focus( );
                         return;
}

The rest seams ok
0
mrongAuthor Commented:
I reset the following values in update function.

 oldRegCode = (string)MyDataSet1.Tables[0].Rows[0]["REGION_CODE"];
               oldZone = (string)MyDataSet1.Tables[0].Rows[0]["FAC_ID"];
               oldBldg = (string)MyDataSet1.Tables[0].Rows[0]["BLDG"];
               oldRupo = (string)MyDataSet1.Tables[0].Rows[0]["AF1"];
               oldYN = (string)MyDataSet1.Tables[0].Rows[0]["AF4"];
            oleDbDataAdapter1.Update(MyDataSet1);

Coz I don't want the user to see the warning message if they update the record more than one time themselves after they open the form. But it didn't work out. Why?
0
TheAvengerCommented:
Maybe you have more than one record in the dataset and here you always take the values from the first record...
0
mrongAuthor Commented:
I have only one record and the searching result is unique.
0
TheAvengerCommented:
Then it should be no problem and also it should never be called again...
0
TheAvengerCommented:
This question became too long and too many small details to handle so I will abandon it. Hope somebody else will help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.