Solved

How to check weather the data has been update

Posted on 2003-10-25
32
242 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 60 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now