Help Please: .NET + SQLServer + Concurrency Violation

Discussion in 'Programming & Software Development' started by Deltoid, Oct 4, 2005.

  1. Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    I'm getting the following error.

    Concurrency violation: the UpdateCommand affected 0 records

    I don't know why. It happens everytime I update a record from within my program.

    I can do it within sqlserver fine.

    I basically have this for the code.

    Code:
            Dim dsChanges As dsUser
            xData = getUser(username)
            xData.User(0).Name = "test"
            dsChanges = CType(xData.GetChanges(), dsUser)
    
            If Not dsChanges Is Nothing Then
                connect()
                sdaUser.Update(dsChanges, "User")
                disconnect()
            End If
    
    Then I get the error, I've tried searching the net and can't find out what the hell is going wrong. Please help if you can.
     
  2. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Can you paste all of your script, because your never loading a record, and your always passing 0 into your user.
     
  3. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Code:
        Sub modifyUser()
    
            Dim test As xdsUser
            test = getUser()
            test.User(0).Name = "testing"
    
            Dim dsChanges As xdsUser
            dsChanges = CType(test.GetChanges(), xdsUser)
            If Not dsChanges Is Nothing Then
                SqlConnection1.Open()
                SqlDataAdapter1.Update(dsChanges, "User")
                SqlConnection1.Close()
            End If
        End Sub
    
    That better? Any help would be apprechiated. I can't work out what the hell is wrong. :(
     
  4. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Im only guessing here because im assuming your writing this with class's.

    If User is a contructor for the User class, and your passing 0 in every time, i would assume it is trying to load the UserID = 0 which will not return a record, then your setting the name to testing, and applying the update. But since it returned 0 rows it cannot update and your not catching the return from sql server.
     
  5. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    The getUser(username) line returns a populated dataset with all the users in the database.

    when I say User(0) thats refering to the first line of the dataset. its in VB so they use () instead of [] for arrays.
     
  6. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    True, I hate VB syntax :)

    This is a VERY different way to what i would write for an update and its 4.19am which isn;t helping me think.

    Are you using stored procedures?
     
  7. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    yeah stored procedures as well.

    The thing is that this code used to work and I didn't touch the code since it was working.

    I've used the same template for the code in a number of other classes as well and they all work.

    I've looked over all the stored procedures and they are the same as well.

    At the moment I'm redoing all the stored procedures to see what that yields.

    I'm using the following stored procedures.

    Code:
    CREATE PROCEDURE [user_select] 
    	@username varchar(20) = NULL,
    	@deptClient smallint = NULL,
    	@location smallint = NULL,
    	@isClient smallint = NULL,
    	@email varchar(50) = NULL
    
    AS
    
    SELECT *
    FROM [User]
    WHERE ((@username IS NULL) OR ([username] = @username))
    AND  ((@deptClient IS NULL) OR ([department/clientNo] = @deptClient)) 
    AND ((@isClient IS NULL) OR ([isClient] = @isClient))
    AND ((@email IS NULL) OR ([email address] = @email))
    
    GO
    Code:
    CREATE PROCEDURE [user_update]
    	(@username_1 	[varchar],
    	 @Email_Address_2 	[varchar](50),
    	 @Name_3 	[varchar](30),
    	 @Phone_No__4 	[varchar](20),
    	 @Fax_No__5 	[varchar](20),
    	 @Department_ClientNo_6 	[smallint],
    	 @isClient_7 	[bit],
    	 @Password_8 	[varchar](10))
    
    AS
    SET UPDATE [User] 
    
    SET  [Email Address]	 = @Email_Address_2,
    	 [Name]	 = @Name_3,
    	 [Phone No.]	 = @Phone_No__4,
    	 [Fax No.]	 = @Fax_No__5,
    	 [Department/ClientNo]	 = @Department_ClientNo_6,
    	 [isClient]	 = @isClient_7,
    	 [Password]	 = @Password_8 
    
    WHERE 
    	( [username]	 = @username_1)
    
    GO
    I'm using the datasets with a dataadapter for filling/updating. Thanks for your help so far.
     
  8. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Yeah, first of all in the update stored procedure i would add at the bottom.

    IF (@@RowCount > 0 AND @@Error = 0) BEGIN
    PRINT 'UPDATE SUCCEEDED'
    RETURN 0
    END ELSE BEGIN
    PRINT 'UPDATE FAILED'
    RETURN 1
    END

    Then when i send in the stored procedure, i would ask for a the return to check it.

    Code:
    		private bool Update()
    		{
    			DataAccess lDataAccess; // memory declaration
    			SqlCommand lCommand; // memory declaration
    			lDataAccess = new DataAccess();
    			lCommand = lDataAccess.CommandProcedure("updateProduct");
    			// adding paramters
    			lCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = _productid;
    			lCommand.Parameters.Add("@ProductGroupID", SqlDbType.Int).Value = _productgroupid;
    			lCommand.Parameters.Add("@Name", SqlDbType.VarChar, 100).Value = _name;
    			lCommand.Parameters.Add("@Price", SqlDbType.Money).Value = _price;
    			lCommand.Parameters.Add("@Description", SqlDbType.VarChar).Value = _description;
    			lCommand.Parameters.Add("@SmallImage", SqlDbType.Char, 5).Value = _smallimage;
    			lCommand.Parameters.Add("@LargeImage", SqlDbType.Char, 5).Value = _largeimage;
    			lCommand.Parameters.Add("@Active", SqlDbType.Bit).Value = _active;
    			lCommand.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    			lCommand.ExecuteNonQuery();
    			// checking if update happened
    			if (Convert.ToInt32(lCommand.Parameters["RETURN_VALUE"].Value) == 0) 
    			{
    				lDataAccess.Dispose();
    				return true;
    			}
    			else
    			{
    				lDataAccess.Dispose();
    				return false;
    			}
    		}
     
  9. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Thanks for the reply.
    I read your code, basically my getUsers() function does what you have there. I don't get a return value though.
    Just then I created a new table called xUsers and new stored procedures to match it.
    I then wrote a class in the exact same way as the other one. Ran the same code and it works.
    What the hell is the deal with that?

    So with that extra code in the Stored procedure does it basically just say if it fails or suceeds?
     
  10. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Well i split my project up into 3-tier arciteture (spelling?). Website layer (pages/codebehind), Business layer (class's), and Database (sql server).

    When i load a record i would call it like so.

    User lUser = new User();
    if (!lUser.Load( *pass in value by button argument or what ever* ) {

    throw new Exception("Failed to load user");
    // Error display code.

    } else {

    txtUserName.Text = lUser.UserName;

    }

    So basically if it fails to load the user, instead of throwing an asp.net error to say there was no record or displaying a blank page or whatnot, i can put a custom error message on the page to say

    Error: Failed to load user.

    or

    Error: Failed to update account details.
     
  11. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    At the moment I'm just letting the errors happen then catching them at the presentation layer and outputting a message to the user. Probally not the best way to go about it so I might implement it with the way you said if I get time.

    For the record I just created a new table (exactly the same as the former User table, created new stored procedures (same as the old ones but on the new table), created a new dataset for the new table, changed all the variables in my dataaccess class to refer to the new variables.

    Now it works. I mean seriously WTF is with that? Its the exact same as it was before. I copied and pasted the code between things.

    I havn't extensively tested it yet but in the process of it now.
     
  12. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Yeah thats pretty weird. The worst that has happened to me was i copied a class from 1 project to another and couldn;t figure out why i couldn;t reference the class. Till i relised i hadn't changed the namespace on the class to be the same as the project.

    I love this line of code.

    Product.ProductGroup = new ProductGroup(Convert.ToInt32(ddlProductGroup.SelectedValue));

    With the below property in my Product class.
    Code:
    		public ProductGroup ProductGroup 
    		{
    			get
    			{
    				// Has a ProductType object already been loaded?
    				if (_productgroup == null) { _productgroup = new Business.ProductGroup( _productgroupid ); }
    				// Return the existing or new ProductType object
    				return _productgroup;
    			}
    			set 
    			{
    				_productgroup = value;
    				_productgroupid = _productgroup.ID;
    			}
    		}
    
     
  13. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    I didn't follow with the code. What do you love about it?
     
  14. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Ok. Done a fair amount of testing on it it is working now. Seriously though WTF is with that. I've spent 4 hours trying to fix this damn thing and in the end I still don't know what caused it. I copied and pasted the code between stored procedures and ditto for the tables.

    Its such an anticlimax too. I'm like RAD I fixed the error. Then I realise that all that means is that I'm now 4 hours behind where I was before. Stupid world, always coming down on me.

    Thanks alot for your help though man, if I can get the time I will change my error handling like yours as its probally a better idea for me to handle it like that then just throw an exception in the data tier and catch it in the presentation tier.

    Out of interest you arn't a QUT student doing ITB642 are you?
     
  15. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    The code loads the productgroup object into the product object so now

    lProduct.ProductGroup.Name
    lProduct.ProductGroup.ID
    etc
    is populated, the same as if i was gonna do

    lProduct.ProductGroupID = lProductGroup.ID;

    Except i never set ID's, i just load the object.

    I was taught you shouldnt set, or shouldnt need to ID's, part of OOP.

    Nope, i failed 6th form year twice (year 12), and droped out and did a diploma in digital media, and a diploma in eCommerce. And now im a web developer.

    My downside is i learnt to program in OOP from day 1, (except in asp3, coldfusion, and php), but i don't think even if i knew procedual (spelling?) i would ever use it over OOP in asp.net
     
  16. grill

    grill Member

    Joined:
    Jun 27, 2001
    Messages:
    630
    Location:
    Perth
    For future reference you get a concurrency exception when your update statement in SQL Server fails to update any rows. EG
    Code:
    Update TableName
    SET Column1 = 'Test'
    WHERE IDColumn = 1
    
    will throw a concurrency exception if there are no records with IDColumn = 1. You can avoid this by checking for matching rows before you execute the update statement or by setting NOCOUNT = ON (Not sure about the second option but I think it works).
     
  17. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Ok, so the problem still exists. I don't know if it went away and came back or if I wasn't testing it right earlier.


    So you saying grill that if a record with the primary key of "1" didn't exist I'd get that error?

    I'm pretty sure it exists. Because that getUser("1"); statement makes a call to the database to get user.

    So tired, havn't slept yet trying to get the damn error to go away. I've given up for now and am going on with the error, its not a critical thing, the program can work without it but without getting it to work the program wont be complete.
     
  18. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    I am at work now, im so god damn tired i couldn't sleep at all last night.

    Anyway, 2 things,

    1) why do you put your inuts in brackets?
    Code:
    CREATE PROCEDURE [user_update]
    	(@username_1 	[varchar],
    	 @Email_Address_2 	[varchar](50),
    	 @Name_3 	[varchar](30),
    	 @Phone_No__4 	[varchar](20),
    	 @Fax_No__5 	[varchar](20),
    	 @Department_ClientNo_6 	[smallint],
    	 @isClient_7 	[bit],
    	 @Password_8 	[varchar](10))
    
    AS
    2)

    Code:
    CREATE PROCEDURE [user_update]
    	(@username_1 	[varchar],
    	 @Email_Address_2 	[varchar](50),
    	 @Name_3 	[varchar](30),
    	 @Phone_No__4 	[varchar](20),
    	 @Fax_No__5 	[varchar](20),
    	 @Department_ClientNo_6 	[smallint],
    	 @isClient_7 	[bit],
    	 @Password_8 	[varchar](10))
    
    AS
    [b]SET UPDATE [User] [/b]
    
    SET  [Email Address]	 = @Email_Address_2,
    	 [Name]	 = @Name_3,
    	 [Phone No.]	 = @Phone_No__4,
    	 [Fax No.]	 = @Fax_No__5,
    	 [Department/ClientNo]	 = @Department_ClientNo_6,
    	 [isClient]	 = @isClient_7,
    	 [Password]	 = @Password_8 
    
    WHERE 
    	( [username]	 = @username_1)
    
    GO
     
  19. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Basically for my insert, update and delete stored procedures I just used the wizard and let that sort it out.

    I will play with the brackets though and see if it does anything although I doubt it.
     
  20. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    I was just wondering, because i only see it used on user-defined functions. (im talking about ( ) at the start and end of the inputs)

    Code:
    CREATE PROCEDURE [user_update]
    	@username_1 	[varchar],
    	 @Email_Address_2 	[varchar](50),
    	 @Name_3 	[varchar](30),
    	 @Phone_No__4 	[varchar](20),
    	 @Fax_No__5 	[varchar](20),
    	 @Department_ClientNo_6 	[smallint],
    	 @isClient_7 	[bit],
    	 @Password_8 	[varchar](10)
    AS
    Anyway, take the SET out of SET UPDATE [User]
     

Share This Page

Advertisement: