Problem in updating data

Question

5.00/5 (1 vote)

See more:

Hey guys,

I have made an application that uses sql server to keep data and retrieve it when needed. Today me and my friend tried
to update data of same record from different computers, it threw exception. I have read a lot about 'Optimistic concurrency' but to be honest, I don't know how to implement it. Can anyone please help me or atleast guide me
on how should i implement it.

Thanks and regards
ajinkya

Posted 7-Apr-11 21:12pm

ajinkya11121

Add a Solution

Comments

R. Giskard Reventlov 8-Apr-11 3:22am

What was the exception?

4 solutions

Solution 4

string ConStr = ConfigurationSettings.AppSettings["DATA_BASE"];<br />
private void control_click(object sender...)<br />
{<br />
  SqlConnection con = new SqlConnection(ConStr);<br />
  stirng Sql="UPDATE TBL_NAME SET (COLUMN) VALUES (@PARAMETER) <WHERE CONDITION IF REQUIRED>";<br />
  SqlCommand cmd = new SqlCommand(Sql, con);<br />
  cmd.Paramenter.AddWithValue("@PARAMENTER", TXT.text);<br />
  try<br />
  {<br />
     cmd.ExecuteNonQuery();<br />
     MessageBox("Updated Successfully");<br />
  }<br />
  catch<br />
  {<br />
     MessageBox("Error occured while updating");<br />
  }<br />
}

Posted 7-Apr-11 22:42pm

Santoshshrest

Comments

ajinkya11121 8-Apr-11 6:10am

hey thanks for your reply.
i am trying to include this in my code.
can you tell me what '@PARAMETER' stands for?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Eduard Keilholz · Accepted Answer · 2011-04-07T21:32:00

Most data 'layers' have some kind of check to see if the record is still the same by the time you commit changes to the database.
For example, you retrieve a record from the database containing 'a', and you change it to 'b'. When you commit that to the database, the command you execute will check for 'a' and update that to 'b'. Now if somebody in the meantime changes the 'a' in the database to a different value (say 'c', the command check for 'a' and change it to 'b' will result in nothing, because 'a' doesn't exist anymore (because it's already changed to 'c').

Your code 'knows' there's a change an expects your database to update 1 record, but the database returns a result of 0 changed records. That is exactly what your error means.

You need to find a way to handle that kind of changes depending on some choises. You can choose for example to retrieve the record from the database, update the user's changes and immidiately write it back to the database, but there's a chance you'll undo the changes made in the first transaction.

You may want to take a peek at this[^] page. It explains way better what's going on than I can.

Good luck!

Eduard

Sandeep Mewara · Accepted Answer · 2011-04-07T21:30:00

Solution 1

You need to read on SQL Locking.

Please go through them:
How to resolve blocking problems that are caused by lock escalation in SQL Server[^]
Advanced SQL Server Locking[^]
Reducing SQL Server Locks[^]
Understanding Locking in SQL Server[^]
Locking in Microsoft SQL Server[^]
SQL server locking[^]

Posted 7-Apr-11 21:30pm

Sandeep Mewara

Comments

Eduard Keilholz 8-Apr-11 4:05am

Good resources Sandeep! +5

Sandeep Mewara 8-Apr-11 6:17am

Thanks Eduard.

ajinkya11121 8-Apr-11 6:11am

Hey sandeep,
Thanks a lot man. These links are indeed very helpful.

Sandeep Mewara 8-Apr-11 6:17am

Glad it helps.

Santoshshrest · Accepted Answer · 2011-04-07T22:40:00

string ConStr = ConfigurationSettings.AppSettings["DATA_BASE"];
private void control_click(object sender...)
{
SqlConnection con = new SqlConnection(ConStr);
stirng Sql="UPDATE TBL_NAME SET (COLUMN) VALUES (@PARAMETER) <WHERE CONDITION IF REQUIRED>";
SqlCommand cmd = new SqlCommand(Sql, con);

cmd.Paramenter.AddWithValue("@PARAMENTER", TXT.text);

try
{
cmd.ExecuteNonQuery();
MessageBox("Updated Successfully");
}
catch
{
MessageBox("Error occured while updating");
}
}

Problem in updating data

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0