Help Please: .NET + SQLServer + Concurrency Violation

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

  1. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Ok, thanks for the suggestion. I tidyed up the update stored procedure but still the same error message.

    I really don't know what to do about it now. I tried everything I could think of
     
  2. fox1

    fox1 Member

    Joined:
    Jun 28, 2001
    Messages:
    3,086
    Location:
    Brisbane
    I'm probably just shooting blanks ( :Paranoid: ) but when I read Concurrency Violation, I read, too many connections.

    Tried rebooting?
     
  3. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Thanks for the suggestion Fox1. I read it as that first as well and did reboot, also tried running it on another machine. But had no luck. Just crashed for a couple of hours on the couch as a result of no sleep.

    I went through all my backups trying to find when the error started happening. It started along time ago and I just didn't realise as I'd finished my data tier and tested it.

    I was screwing around with the database after that though which might have been what caused it. Who knows. I've emailed my lecturer asking for help from him as well
     
  4. fox1

    fox1 Member

    Joined:
    Jun 28, 2001
    Messages:
    3,086
    Location:
    Brisbane
    hey, a few things came up in a google search.

    This microsoft one

    etc etc


    This one
    Guy there has the same trouble, worked before but not now.
    And theres a bit of an explaination from someone.


    Handling Data Concurrency Using ADO.NET, Part 2


    edit: ok I admit it, I haven't read this thread fully. :(
     
    Last edited: Oct 5, 2005
  5. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Thanks for the reply again fox1. I read through them both and still no luck. The article is talking about dealing with when the problem is being caused byt two people accessing the same table at once which isn't whats happening.

    The second was more online but didn't help much. I rewrote the update stored procedure manually but no luck:

    Code:
    CREATE PROCEDURE [user_update]
    	@username varchar(20),
    	@emailAddress varchar(50),
    	@name varchar(30),
    	@phoneNo varchar(20),
    	@faxNo varchar(20),
    	@deptClientNo smallint,
    	@isClient bit,
    	@password varchar(10)
    
    AS
    UPDATE [User]
    
    SET 
    	[Email Address] = @emailAddress,
    	[Name] = @name,
    	[Phone No.] = @phoneNo,
    	[Fax No.] = @faxNo,
    	[DeptClientNo] = @deptClientNo,
    	[isClient] = @isClient,
    	[Password] = @password
    
    WHERE
    	([username] = @username)
    GO
    
     
  6. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Hmm, atleast your not getting max pool size reached error. Geez that took me 3 weeks to figure out why that was happening.

    To be honest i don't think i could decifer (spelling?) your error without viewing your entire project.

    However if i was to do that (which i don;t mind doing) i would have to do it from work since i don't have VB.Net at home. (since i mainly program in C#).

    When i get home i can send you a basic project which is divided into a 3 tier project which connects to the database pulls records, or individual records and updates adds etc etc so you can see the difference. I only say this because your function to me is confusing. Where i think mine is more logical and makes sense.
     
  7. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Yeah thanks for the offer, it'd be easier for me to just take my laptop into uni and see my lecturer tomorrow and see if he can help me. Too much screwing around trying to get it to work otherwise.

    Just wish I could figure out what the hell is going on. I've written the code which is using it though so hopefully once I work it out that part of the program is done. I've just commented out the code which calls it at the moment. So you can add new users to the system you just can't modify or delete them. Even if I can't get that worked out I'd assume I wouldn't lose that many marks as a result unless my error is bleedingly obvious.

    I'm supposed to be doing the assignment in a group of 3 but due to my working schedule and past bad experiences I decided to just do it solo. But its just a heap of work and I'm a complete newb to C# and ASP.NET so when I get stuck the only place I have to turn is ocau. (none of my friends known any .net).

    Thanks for everyones help so far, I really apprechiate it.
     
    Last edited: Oct 5, 2005
  8. festerous_one

    festerous_one Member

    Joined:
    Jan 27, 2003
    Messages:
    123
    Location:
    Rockhampton, Queensland
    basically, when i had this problem, i was trying to update a row in a table with no data that differed. i ended up recoding everything so that there was guaranteed something to change.

    this sounds REALLY silly i know, but try manually code the sql transaction you are trying to acomplish and see what sql it generates as it passes it through. it might be trying something like i said above, updating the original value to the same value and spittinng the dummy.

    it was a few months ago i had the problem so i am trying to remember exactle what it was :/ sorry if i waste your time.
     
  9. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    I will give that ago. I think I might packup now and head to uni and see my lecturer about it. Will let you know how it goes
     
  10. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Seriously.......WTF.

    So I'm setting up the application so that I have a testdriver so I can simply press a button and have the error occur rather then screw around entering shit.

    So I set up the testdriver and the damn thing works now. Who knows what the hell is happening.

    Anyway I'm going to back it up right now and try and fix the others. Will keep you guys posted.
     
  11. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Are you using Visual Studio? Cos you can use breakpoints and watch's to see where exactly its going wrong.

    You can see exactly what content is being passed into the objects and see if its the code or the procedure.
     
  12. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Yeah I'm using visual studio.

    Rather then doing breakpoints instead I just had a foreach loop which would output each line to the console with the values of each line (after I did the getChanges() method). All the lines checked out


    I went through and ONLY changed the stored procedures for the other two that weren't working and they work as well now. I have no idea what the deal is. The stored procedure made by the wizard is exactly the same as the one I wrote just I didn't have so many brackets.
     
  13. festerous_one

    festerous_one Member

    Joined:
    Jan 27, 2003
    Messages:
    123
    Location:
    Rockhampton, Queensland
    well at least its working :)

    moral of the story - dont trust wizards....


    i mean have you seen the wizard that pops ip during the windows xp install... WOULD YOU TRUST THAT MAN?
    i think not
    why would you have stars on your hat... really.


    anyway, congrats.
     
  14. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Yeah all appears to be working at the moment.

    Now its just a matter of trying to get it finished in time. Not due till next friday but doing my best to finish it by this sunday

    thanks again for everyones help.
     
  15. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Hey guys have a quick question if your able to help.

    At the moment I have about 8 classes in my dataaccess tier. For each class I declare the connection string inside it. I'm trying to work out a way to store this connection string in a single location that all 8 classes reference so when I change the connection string I just have to do it once. Not 8 times.

    Any help? Everything I seem to find is for reading from the web.config but I'm assuming that due to it being in a different tier I shouldn't be reading the ConnectionString from there for the datatier.
     
  16. festerous_one

    festerous_one Member

    Joined:
    Jan 27, 2003
    Messages:
    123
    Location:
    Rockhampton, Queensland
    a string enum or something ?

    read it from a config.cfg type file (noting that you shouldnt use cfg file as it is readable from the net, use a .config extension or something) <- NOT RECCOMENDED for real life, or for assignment, but will work.
     
  17. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    I would suggest a dataaccess class.

    Note in asp.net the web.config file is not readable from client side, if it was then people could see all your session info, authrorization type, so on and so forth, however you can write the connection string in 1 class. But writing it in the webconfig file will allow you to debug at home, take your website to school, upload it, change the connection string in the webconfig file, and the sit will still work without having to be compiled again.

    Basically ill just copy paste these 2 class's so you can see, and explain the webconfig connection string method.

    dataaccess class:
    Code:
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    
    namespace Two.Business
    {
    	/// <summary>
    	/// Summary description for dataAccess.
    	/// </summary>
    	
    
    	//Imports System.Data.SqlClient
    	public class DataAccess 
    	{
    
    		// Our internal variables
    		private SqlConnection _connection;
    		private SqlCommand _command; 
    
    		public SqlConnection Connection()
    		{
    			// This method creates our connection
    			_connection = new SqlConnection();
    			_connection.ConnectionString = [b]"CONNECTION STRING"[/b];
    			_connection.Open();
    			return _connection;
    		}
    
    		public SqlCommand CommandProcedure(string command)
    		{
    			// This method takes the name of a stored procedure and returns a command object for it
    			_command = new SqlCommand(command, this.Connection());
    			_command.CommandType = CommandType.StoredProcedure;
    			return _command;
    		}
    
    		public void Dispose()
    		{
    			_connection.Close();
    			if (_connection != null) 
    			{
    				_connection.Dispose();
    			}
    		}
    
    	} //End Class
    }
    This is the function from another class:
    Code:
    public bool Load(int ProductID) 
    {
    	DataAccess lDataAccess = new DataAccess(); // memory declaration
    	SqlCommand lCommand; // memory declaration
    	
    	lCommand = lDataAccess.CommandProcedure("getProduct");
    	SqlParameter lParameter;
    	lParameter = lCommand.Parameters.Add("@ProductID", SqlDbType.Int, 4);
    	lParameter.Direction = ParameterDirection.Input;
    	lParameter.Value = ProductID;
    	SqlDataReader lReader;
    	lReader = lCommand.ExecuteReader();
    	if ( lReader.HasRows & lReader.Read() ) 
    	{
    		_productid = Convert.ToInt32(lReader["ProductID"]);
    		_name = Convert.ToString(lReader["Name"]);
    		if (!Convert.IsDBNull(lReader["Price"])) { _price = Convert.ToDouble(lReader["Price"]); }
    		if (!Convert.IsDBNull(lReader["Description"])) { _description = Convert.ToString(lReader["Description"]); }
    		_productgroupid = Convert.ToInt32(lReader["ProductGroupID"]);
    		_active = Convert.ToBoolean(lReader["Active"]);
    		lDataAccess.Dispose();
    		return true;
    	}
    	else
    	{
    		// No records returned
    		lDataAccess.Dispose();
    		return false;
    	}
    }
    Now, the webconfig file has application settings in it, if its not in there by default you can add it

    <appSettings>
    </appSettings>

    This goes inside the <configuration> tag.

    Inside you can put your connection string
    *note, key and value you can set to what you like, so you can use it for adding GST on multiply pages. naming the key to something like "addGST" or "GST" etc, then the value as what ever the markup is, and if gst ever changes, you can change it on the webconfig file to alter every page.

    <add key="Connectionstring" value="server=;database=;User id=;Password=;Connection Lifetime=10;Max Pool Size=1000;connection reset=true;Enlist=true" />

    Now, normally in your data connection where you put the connection string in, you simply import the following namespace:

    System.Configuration;

    And add the below string, instead of the usual connection string thing (i cant be bothered typing its fucking 7-30am i got to get ready for work)

    ConfigurationSettings.AppSettings["Connectionstring"]

    -----------------------------------

    Any questions, ask, ill answer after 9am when i get to work.
     
  18. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    Thanks again Elyzion. I put the connectionstring into the web.config file and it works a treat. Much easier changing it there then in 8 different classes and then having to recompile.

    Is it fine to have it in the web.config? I mean shouldn't I have it in a config file in the DataAccess tier?


    Here is another question, I assumed you being a web developer might have had experience with this before. I want to make a master-detail page. Both the master and detail are editable. Know anyway to do it? Considering there are many details to each master I want like a "next" and "prev" button which will allow you to navigate through eaach of the details but while keeping the master static on the page.

    The best way I can think to do this is with frames. Problem is though that the page also needs to read a query string from the command line. So I'd call the page "pageFrame.htm?job=112". PageFrame's master frame links to "jobMaster.aspx", and detail to "jobDetail.aspx".

    Thing is jobMaster also takes a query string and so does jobDetail. Do you know anyway I could do that. I was trying to do it with frames but gave up as I didn't know how to pass the query string onto the jobMaster and jobDetail pages from the pageFrame page.

    Sorry for all the questions. I have googled this. I only found one page which explained what I was after butit didn't deal with query strings for the initial pages.
     
  19. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    NP

    Im 100% sure its fine, and no one has ever said otherwise. The web.config file cannot be accessed so no one will see it. However i think it would pay to make a dataaccess class, just makes you write less code cos you never have to re-write the connection.


    I really don't understand what you mean by this. Are you wanting to page the data? say like...

    master.aspx?id=3&page=2

    If so, paging is quite difficult in asp.net/sqlserver2k because unlike using mysql, sqlserver doesn't have a limit fuction.

    In mysql if you want to page you just pass in the start limit and finish limit, like results 15, 25 - would get results from 15 to 25. In sqlserver however you have to pull ALL of the results out at once. If what your after is paging, i can teach you over msn or aim, i have never found a decent tutorial for paging on the net before, and am tempted to write one.
     
  20. OP
    OP
    Deltoid

    Deltoid Member

    Joined:
    May 24, 2003
    Messages:
    9,515
    Location:
    Brisbane
    I will explain better. Probally easiest way for me to explain it is by saying its for creating an invoice.

    So we have an invoice header and an invoice lines.

    Master table: Invoice Header
    Fields: Invoice No, customer, customer address, date

    Detail Table: Invoice lines
    Fields: Job No., Line No., Item No., Item Description, Qty, Price


    So for an invoice you'd have 1 invoice header record and multiple invoice line records. You'd have a new invoice line for each different item you purchased. So say someone bought 3 things the tables would be something like this:

    Invoice Header:
    10021, OCAU, www.overclockers.com.au, 07/10/2005

    Invoice Lines:
    10021, 1, NOTEBOOKA460, 64 page A4 Notebook, 3, 5
    10021, 2, RULER30, 30cm Ruler, 2, 3.45
    10021, 3, STAPLES500, 500, 4.50


    Follow? So I'd like a page that looks like this:

    Code:
    -------------------------------------
    Invoice header fields
    Customer: ______
    Date: _______
    etc: _______
    --------------------------------------------------
    Invoice line fields (showing one record at a time)   
    Item No: ______     Qty. ______
    Descrip: _______    Etc _____                
                                                    
    << (prev button)                  (next button)>>      
    -------------------------------------------------
    
    Follow? So when I press the prev/next button it will just repopulate the invoice lines part of the page with the next invoice line record.
     
    Last edited: Oct 7, 2005

Share This Page

Advertisement: