Cannot open any more databases in MS Access and ASP.NET
-
Hi All, I developed one software in MS Access 2003 and ASP.NET C#, my client was having lot of queries written already in MS Access, I just need to give an ASP.NET UI for them. All went well, but the moment I started my testing with two users with two different system trying to execute same query,each query takes around 15 seconds to execute. But When I did that only for one user it returns the result and works fine, but for the other one it returns error given below "Cannot open any more databases. " Can anyone tell me what is the root cause, is it because the connection is closed. Below is my code to execute queries private OleDbDataReader getDataSet() { try { adap=new OleDbDataAdapter(); DataSet dsTemp=new DataSet(); /*connstr=*/ string connstr=System.Configuration.ConfigurationSettings.AppSettings["connStr"]; con=new OleDbConnection(@connstr); con.Open(); cmd=new OleDbCommand(); //I have query name to MS Access passed in QueryString cmd.CommandText="select * from "+Request.QueryString["rep"]+""; cmd.Connection=con; adap.SelectCommand =cmd; OleDbDataReader oRead=cmd.ExecuteReader(CommandBehavior.CloseConnection); return oRead; } catch(Exception ex) { Response.Write(ex.Message); con.Close(); return null; } } //In page load I am calling the getdattaset function, which returns me a oledbDataReader private void Page_Load(object sender, System.EventArgs e) { OleDbDataReader oRead=null; if(!Page.IsPostBack) { try { oRead=getDataSet(); dgControlCapacity.DataSource =oRead; dgControlCapacity.DataBind(); oRead.Close(); } catch(Exception ex) { Response.Write(ex.Message); oRead.Close(); } } } The problem is when two users runs the query at a time, one fellow get the result and the other fellow get "Cannot open any more databases." error. I tried the same which is returning dataset instead of Datareader, but that also gives the same problem. Can I restrict the access to a purticular function for each user, one at a time manner? So that even if two users execute at same moment, the next one will be in Queue. I tried to implement the same with Monitor.Enter(this) and Lock(this) etc, but no luck. Please can anyone help me in thi
-
Hi All, I developed one software in MS Access 2003 and ASP.NET C#, my client was having lot of queries written already in MS Access, I just need to give an ASP.NET UI for them. All went well, but the moment I started my testing with two users with two different system trying to execute same query,each query takes around 15 seconds to execute. But When I did that only for one user it returns the result and works fine, but for the other one it returns error given below "Cannot open any more databases. " Can anyone tell me what is the root cause, is it because the connection is closed. Below is my code to execute queries private OleDbDataReader getDataSet() { try { adap=new OleDbDataAdapter(); DataSet dsTemp=new DataSet(); /*connstr=*/ string connstr=System.Configuration.ConfigurationSettings.AppSettings["connStr"]; con=new OleDbConnection(@connstr); con.Open(); cmd=new OleDbCommand(); //I have query name to MS Access passed in QueryString cmd.CommandText="select * from "+Request.QueryString["rep"]+""; cmd.Connection=con; adap.SelectCommand =cmd; OleDbDataReader oRead=cmd.ExecuteReader(CommandBehavior.CloseConnection); return oRead; } catch(Exception ex) { Response.Write(ex.Message); con.Close(); return null; } } //In page load I am calling the getdattaset function, which returns me a oledbDataReader private void Page_Load(object sender, System.EventArgs e) { OleDbDataReader oRead=null; if(!Page.IsPostBack) { try { oRead=getDataSet(); dgControlCapacity.DataSource =oRead; dgControlCapacity.DataBind(); oRead.Close(); } catch(Exception ex) { Response.Write(ex.Message); oRead.Close(); } } } The problem is when two users runs the query at a time, one fellow get the result and the other fellow get "Cannot open any more databases." error. I tried the same which is returning dataset instead of Datareader, but that also gives the same problem. Can I restrict the access to a purticular function for each user, one at a time manner? So that even if two users execute at same moment, the next one will be in Queue. I tried to implement the same with Monitor.Enter(this) and Lock(this) etc, but no luck. Please can anyone help me in thi
Why use MS Access for your DB? MS Access is known to be a good program when you work on it alone, when you want to use the same MS Access file with multiple users it is guaranteed to go wrong. When you want to work with multiple users you should use MS SQL Server (if you want to keep in the MS environment), other one is MySQL (free).
-
Why use MS Access for your DB? MS Access is known to be a good program when you work on it alone, when you want to use the same MS Access file with multiple users it is guaranteed to go wrong. When you want to work with multiple users you should use MS SQL Server (if you want to keep in the MS environment), other one is MySQL (free).
Thank you very much KrIstOfK for your reply. But my client has written already around 200 views which are too complex, and client do not want the views to be modified, because it is their production database , MS Acces, in Germany.:( Plz suggest me anotherway to work around this problem, atleast can anyone tell me, ho can I make sure that my function is called one by one per user, by locking or anything? When two users execute at same time, I want the first request to be processed first, next second so on. I understand each requests each threads, so I tried to do synchronization for my function. But failed, dont know whether its because of my wrong approach or its incorrect. I remember learning something in java serialized{//code here }, which will allow only one thread(user) at a time to execute code present in the serialized block, something like that is present in .NET? Please reply.
-
Thank you very much KrIstOfK for your reply. But my client has written already around 200 views which are too complex, and client do not want the views to be modified, because it is their production database , MS Acces, in Germany.:( Plz suggest me anotherway to work around this problem, atleast can anyone tell me, ho can I make sure that my function is called one by one per user, by locking or anything? When two users execute at same time, I want the first request to be processed first, next second so on. I understand each requests each threads, so I tried to do synchronization for my function. But failed, dont know whether its because of my wrong approach or its incorrect. I remember learning something in java serialized{//code here }, which will allow only one thread(user) at a time to execute code present in the serialized block, something like that is present in .NET? Please reply.
Hi though i did not do anything with MS Access, I used ASP.NET Cache to store the result and in case if any exception, that stored result will be returned. did something like this public Dataset getDataSet() { if(Cache["rep"]!=null) { return (DataSet)Cache["rep"]; } else { try { //Fill dataset ..... //Store a copy in Cache Cache["rep"]=ds; } catch { if(Cache["rep"]!=null) { return (DataSet)Cache["rep"]; } } } As of now, its working fine, thank you all who tried to help me. Appreciate, Continue helping us. Once again thanks Sony