locked
How does data "creep" from one user to another? RRS feed

  • Question

  • I'm not sure if anyone has seen this or has an answer to it but we've experienced multiple instances of "data creep" for lack of a better term.  One user will be updating a record in our web app with a SQL Server 2005 backend while another user is updating a completely different record.  The first user's update somehow mysteriously gets updated on the second user's records.  I can provide details if necessary but just wondering if anybody has ever seen anything like this and what could be the cause.
    Monday, May 17, 2010 3:12 PM

Answers

  • Yes I've seen it a lot! As Todd points out almost every time it was about the application side. If you can provide some detail we can make more conribution.

    I thing I thougt of was the @@IDENTITY issue. Since you're saying "update" this may not be case but I could'n help myself. Are you using @@IDENTITY?

    Regards.

    Monday, May 17, 2010 7:34 PM
  • I see one important problem in this code that needs to be corrected before any other suggestions could be made.

    You need to use parameters and not string concatenation as you're currently using, e.g. instead of 

     

    command.CommandText = "INSERT INTO np_Request_Flat_Dollars (request_flat_dollars_number, request_flat_dollars_request_pk, request_flat_dollars_type_pk, request_flat_dollars_description, request_flat_dollars_amount) VALUES ('" + itemID + "', '" + itemReqPK + "', '" + itemType + "', '" + itemDescription + "', '" + itemAmount.Replace(",", "") + "')";
    command.ExecuteNonQuery();
    
    command.CommandText = "UPDATE np_Request_Flat_Dollars SET request_flat_dollars_type_pk = '" + ddl.SelectedValue + "', request_flat_dollars_description = '" + tb_desc.Text.Replace("'", "''") + "', request_flat_dollars_amount = '" + tb_amount.Text.Replace(",", "") + "WHERE request_flat_dollars_request_pk = '" + lblReqpk.Text + "' AND request_flat_dollars_number = '" + hdn_fld_num.Value + "'";
    result = command.ExecuteNonQuery();
    
    //you need to use
    
    command.CommandText = "INSERT INTO np_Request_Flat_Dollars (request_flat_dollars_number, request_flat_dollars_request_pk, request_flat_dollars_type_pk, request_flat_dollars_description, request_flat_dollars_amount) VALUES (@ItemID, @ItemReqPK, @ItemType, @ItemAmount)";
    command.Parameters.AddWithValue ("@ItemID",ItemID);
    //do the same for other parameters
    command.ExecuteNonQuery();
    
    command.CommandText = "UPDATE np_Request_Flat_Dollars SET request_flat_dollars_type_pk = @NewPK, request_flat_dollars_description = @Desc, request_flat_dollars_amount = @NewAmount WHERE request_flat_dollars_request_pk = @OldPk AND request_flat_dollars_number = @OldAmount";
    
    //Set all parameters
    result = command.ExecuteNonQuery();

     

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 18, 2010 5:52 PM
    Answerer
  • It may be part of the problem. You also need to verify the exact commands send to SQL Server by running SQL Profiler. It is possible you're not sending what you intended to send.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 18, 2010 6:51 PM
    Answerer

All replies

  • As a SQL guy, I'll point to the application code EVERY time!

    How are the updates being handled? In a transaction? Are records identified by User (in other words, is UserID one of the fields in the Primary Key?)


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, May 17, 2010 5:18 PM
  • Yes I've seen it a lot! As Todd points out almost every time it was about the application side. If you can provide some detail we can make more conribution.

    I thing I thougt of was the @@IDENTITY issue. Since you're saying "update" this may not be case but I could'n help myself. Are you using @@IDENTITY?

    Regards.

    Monday, May 17, 2010 7:34 PM
  • If you're using @@Identity and not scope_Identity() there are chances you're getting completely wrong ID if the triggers are involved.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, May 17, 2010 8:33 PM
    Answerer
  • I'll provide any detail you want, just not sure what to provide.  I am using transactions but not using @@IDENTITY or scope_identity().  Specifically in my case, one user was adding something in the web app and another user was updating something else in the web app but the update overwrote the insert.  It was a completely different record and should never have happened but now we're trying to figure out how it happened.

    command.CommandText = "INSERT INTO np_Request_Flat_Dollars (request_flat_dollars_number, request_flat_dollars_request_pk, request_flat_dollars_type_pk, request_flat_dollars_description, request_flat_dollars_amount) VALUES ('" + itemID + "', '" + itemReqPK + "', '" + itemType + "', '" + itemDescription + "', '" + itemAmount.Replace(",", "") + "')";
    command.ExecuteNonQuery();

    command.CommandText = "UPDATE np_Request_Flat_Dollars SET request_flat_dollars_type_pk = '" + ddl.SelectedValue + "', request_flat_dollars_description = '" + tb_desc.Text.Replace("'", "''") + "', request_flat_dollars_amount = '" + tb_amount.Text.Replace(",", "") + "WHERE request_flat_dollars_request_pk = '" + lblReqpk.Text + "' AND request_flat_dollars_number = '" + hdn_fld_num.Value + "'";
    result = command.ExecuteNonQuery();

     

    Tuesday, May 18, 2010 3:49 PM
  • I see one important problem in this code that needs to be corrected before any other suggestions could be made.

    You need to use parameters and not string concatenation as you're currently using, e.g. instead of 

     

    command.CommandText = "INSERT INTO np_Request_Flat_Dollars (request_flat_dollars_number, request_flat_dollars_request_pk, request_flat_dollars_type_pk, request_flat_dollars_description, request_flat_dollars_amount) VALUES ('" + itemID + "', '" + itemReqPK + "', '" + itemType + "', '" + itemDescription + "', '" + itemAmount.Replace(",", "") + "')";
    command.ExecuteNonQuery();
    
    command.CommandText = "UPDATE np_Request_Flat_Dollars SET request_flat_dollars_type_pk = '" + ddl.SelectedValue + "', request_flat_dollars_description = '" + tb_desc.Text.Replace("'", "''") + "', request_flat_dollars_amount = '" + tb_amount.Text.Replace(",", "") + "WHERE request_flat_dollars_request_pk = '" + lblReqpk.Text + "' AND request_flat_dollars_number = '" + hdn_fld_num.Value + "'";
    result = command.ExecuteNonQuery();
    
    //you need to use
    
    command.CommandText = "INSERT INTO np_Request_Flat_Dollars (request_flat_dollars_number, request_flat_dollars_request_pk, request_flat_dollars_type_pk, request_flat_dollars_description, request_flat_dollars_amount) VALUES (@ItemID, @ItemReqPK, @ItemType, @ItemAmount)";
    command.Parameters.AddWithValue ("@ItemID",ItemID);
    //do the same for other parameters
    command.ExecuteNonQuery();
    
    command.CommandText = "UPDATE np_Request_Flat_Dollars SET request_flat_dollars_type_pk = @NewPK, request_flat_dollars_description = @Desc, request_flat_dollars_amount = @NewAmount WHERE request_flat_dollars_request_pk = @OldPk AND request_flat_dollars_number = @OldAmount";
    
    //Set all parameters
    result = command.ExecuteNonQuery();

     

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 18, 2010 5:52 PM
    Answerer
  • I will start doing that.  Could that be a part of the problem?  This is truly frustrating to debug since it only seems to happen around this one feature and we can't seem to reproduce it.
    Tuesday, May 18, 2010 6:12 PM
  • It may be part of the problem. You also need to verify the exact commands send to SQL Server by running SQL Profiler. It is possible you're not sending what you intended to send.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, May 18, 2010 6:51 PM
    Answerer
  • Thank you all.  I will try to get information from SQL Profiler.
    Wednesday, May 26, 2010 4:17 PM