problem with simultaneous/concurrent access to database

asp.net , db Thailand
  • 12 years ago

    Hi, has anyone experienced a scenario wherein the datatable returned from a class is not the datatable that i asked for? Or the parameters that i passed is not the values that are retrieved? Actually, i can't explain the main problem because I really don't know what causes this. It's just that, when 1-5 users access the system, the program runs well (all the queries are fine and all the datatables returns what it is to be returned) but when the users exceeds to 5 (users simultaneous/concurrent accessing the site), the datatable returned from the class doesn't contain the data that i asked for.

    1. Is there something to add to connection string (or anywhere else) to permit the maximum number of users to access the site?
    2. When does Windows Server 2003 "cut" the connection? Or is it really "cutting" it?
    3. Does MS SQL Server 2005 have the option to set the maximum number of users? (except from setting the maximum user to 0, which is unlimited)

    I really need help. Kindly reply to this if ever you have any idea. Thanks in advance.

  • 12 years ago

    Here is a quote from MS.

    "The SQL engine supports 1 CPU, 1 GB RAM, and a 4 GB database size. This mechanism permits easy differentiation from other SQL Server 2005 editions by having well defined cut-off points. Otherwise, there is no workload throttle and the engine performs as in other editions. There is no hard-coded limit to the number of users that can attach to SQL Server Express but their CPU and memory limits impose practical limits on the number of users that can achieve acceptable response times from a SQL Server Express database."

    Here is the link.

    http://msdn.microsoft.com/en-us/library/ms345154.aspx

     Hope this helps, your host maybe setting a limit on you?

  • 12 years ago

    I'm using SQL Server 2005 (Standard Edition), is it the same? I have set the maximum user count in connections tab of server properties to 0, I don't know if that makes any sense. 

    1. Is it possible to for a 50-or-more-users group to access the same system?
    2. Is there a possiblity where the variables' value in code behind just "rumble" theirselves? For example, there are 2 browsers, is it possible that that certain system gets the textbox1 value of browser1 and the textbox2 value of browser2, like it is mixing up values?

    I can't go any clearer..  :'(

     Thanks in advance...

  • 12 years ago

    I was just showing that MS doesn't set a max users on the 2005 express so they surly would not set a max on Standard Edition. As far as one browser get the variables values that was mean for another browser? No I could not see that ever happening. Something I can think of is.

    1. Your host, do they set a max on your connections?

    2. When you open a connection after you are done with it are you closing it?

    3. Are you locking the database, I know this one is a little far. Because you would only lock a table to insert, update, or delete. Not to just read.

  • 12 years ago

     Put your  code and store proc to understand  problem

     

     

  • 12 years ago

    Hi there, i'm not using stored procedures.. Here are my codes..

    public DataTable qry_ValAcct(String strUName, String strPwd)
    {
     DataTable dtDataTable = new DataTable("dtDataTable");
     OpenConnection();
     strCommand = "select * from tablename"; // i have my query here 
     dtDataTable = qry_process_fill();
     return dtDataTable;
    }

    public DataTable qry_process_fill()
    {
     format_sql();
     SqlDataAdapter = new SqlDataAdapter(strCommand, SQLCurrentConnection);
     SqlDataAdapter.Fill(dtDataTable);
     SqlLocConn.Dispose();
     SqlLocConn.Close();
     return dtDataTable;
    }

    protected void OpenConnection()
    {
     String strConfigManConnString;
     strConfigManConnString = ConfigurationManager.ConnectionStrings["strConnString"].ToString();
     SqlConnection ConnSql = new SqlConnection(strConfigManConnString);
     ConnSql.Open();
     SqlLocConn = ConnSql;
    }

     

  • 12 years ago

    Is this working code ?

    if yes  then u need to check variable declaration  

  • 12 years ago

    yes, this is a working [edited] code... what variable do i have to check? these are my variables..

    SqlCommand SqlCmd = new SqlCommand();

    SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();

    private SqlConnection SqlLocConn;

    public string strCommand;

    format_sql() is a function that converts sql statement to access statement

    these previous codes are from a class.. i declare and instantiate the class by the following:

    protected static clsTimeInOut objTimeInOut;

    objTimeInOut = new clsTimeInOut();

     

    What do I have to change?

    thanks [again] in advance.. Smiley Face

  • 12 years ago

     do one thing  print ur   strCommand ; variable in your out put and check it

     

     

  • 12 years ago

    the system is working fine, as long as less than 10 users are using / accessing it..

    is there a possibility where the SQL server don't know who calls the query so it just sends the previous query data?

    i'm just hoping that you have any idea about this.. i know that this is an ASP.net forum (but just trying.. ü)

  • 12 years ago

    my  Friend   your  code is  working properly  so its  very difficult to say anything from here ..

     thats  why i told u  print you sql query  which u r passing to sql  and  check it after 10 connection as you say 

    if  printed  query  is  right for wrong out put then its dam sure that problem with  ur sql server  configuration  or sql memory problem

    so  test it  first  print  all the  variables   which u r passing to sql  and  check it

     

     


     

  • 12 years ago

    ok, thanks.. i'll try it first.. ü

  • 12 years ago

    [Late response.]

    This problem has been solved. I just omitted the "SqlLocConn.Dispose".

    Thanks for the advices.

    Yes

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“C++: an octopus made by nailing extra legs onto a dog.” - Steve Taylor