Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to check weather the data has been update

Posted on 2003-10-25
32
Medium Priority
?
287 Views
Last Modified: 2010-04-16
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.
0
Comment
Question by:mrong
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 14
  • 2
32 Comments
 
LVL 10

Expert Comment

by:ptmcomp
ID: 9622345
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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9626415
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
 

Author Comment

by:mrong
ID: 9626858
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mrong
ID: 9626894
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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9626948
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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9626958
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
 

Author Comment

by:mrong
ID: 9627162
How to pass the value->'oldRegCode' from my Find( ) function to Update( ) function?
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9627172
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
 

Author Comment

by:mrong
ID: 9627213
So you call the Update function inside of Find function? Why?
Thanks.

0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9627263
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
 

Author Comment

by:mrong
ID: 9627266
Can I declare a global variable for oldRegCode? And how?

0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9627289
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
 

Author Comment

by:mrong
ID: 9627319
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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9627348
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
 

Author Comment

by:mrong
ID: 9627364
where should I declare the variable oldRegCode? inside of Find function?
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9627376
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
 

Author Comment

by:mrong
ID: 9627398
Here is the structure:

namespace FM
{

Find( ){  }

Update( ) { }


}
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9627410
You don't have a class? this is not possible
0
 

Author Comment

by:mrong
ID: 9627442
namespace FM
{
public class form1:...{

Find( ){  }

Update( ) { }

}

}
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9627502
then just write a member variable in the class:

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

then you can use it from both functions
0
 

Author Comment

by:mrong
ID: 9627580
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
 
LVL 20

Accepted Solution

by:
TheAvenger earned 240 total points
ID: 9629236
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
 
LVL 10

Expert Comment

by:ptmcomp
ID: 9629341
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
 

Author Comment

by:mrong
ID: 9633414
I used "if (res != DialogResult.OK)". It should also be fine,right?

0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9633423
It checks the opposite way, otherwise it's ok. So you have to change also the code in the if and else parts
0
 

Author Comment

by:mrong
ID: 9633443
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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9633483
Add curly brackets here:

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

The rest seams ok
0
 

Author Comment

by:mrong
ID: 9633618
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
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9633703
Maybe you have more than one record in the dataset and here you always take the values from the first record...
0
 

Author Comment

by:mrong
ID: 9633838
I have only one record and the searching result is unique.
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9633853
Then it should be no problem and also it should never be called again...
0
 
LVL 20

Expert Comment

by:TheAvenger
ID: 9633872
This question became too long and too many small details to handle so I will abandon it. Hope somebody else will help.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question