Data Read / Write problem with concurrent users on with dll

Hi,

I have a web app, that runs fine, except for one particular section that uses a class called by an event in the code behind. The class resides as a dll in the bin folder.

We had no problems during testing, when only one user was running this dll. Problems soon occurred when multiple users tried running it. Here's the error & stack:

06/02/2007 09:25:26 ==> cburns ==> There is already an open DataReader associated with this Command which must be closed first. at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ESP.Validator.Data.DatabaseEvents.DatabaseEventManager.Read(IEventable eventObject, Int16 eventType, DateTime earliestDate, DateTime latestDate) in C:\My Path\Validator\Validator.NET\Data\DatabaseEvents\DatabaseEventManager.cs:line 92
at ESP.Validator.Data.Translink.CATCard.GetDespatchDate() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\CATCard.cs:line 94
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.ReadCards() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 215
at ESP.Validator.Data.Translink.ExistingSchemeEntitlement.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\ExistingSchemeEntitlement.cs:line 147
at ESP.Validator.Data.Translink.TranslinkApplicant.ReadEntitlements() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 369
at ESP.Validator.Data.Translink.TranslinkApplicant.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\TranslinkApplicant.cs:line 353
at ESP.Validator.Data.Translink.PrePrintedLetter.Read() in C:\My Path\Projects\Validator\Validator.NET\Data\Translink\PrePrintedLetter.cs:line 282
at ESP.Validator.ValidationProcessor.Read(ValidationSubject subject) in C:\My Path\Projects\Validator\Validator.NET\ValidationProcessor.cs:line 82
at clear_applications_scan_applications.ProcessValidation()

It seems the data reader is getting reused. We have ensured after each read the reader is closed. Though all users are using the same connection string. Could it be a connection pool problem, with the connection being overwritten during execution? Should i edit it according to the users logon?

I am really at a loss for ideas, and I don't mind admitting I am a bit out of my depth with this one!!

Any ideas/suggestions would be greatly appreciated.

Thanks

Hi Assimalyst,

Based on the exception message, we can see that this is not because of reusing of the SqlDataReader, but reusing the SqlConnection.

Generally, on a single SqlConnection, there can only be 1 open SqlDataReader exist at the same time. If a using is trying to open another one, this exception is thrown.

In ADO.NET 2.0, a new concept is involved called Multiple Active Result Sets (MARS). This enables you open multiple DataReaders on a single connection. To achieve this, you only need to add "MultipleActiveResultSets=True" in your connection string. That will prevent the exception from being thrown.

Please check the following links for more information:

http://msdn2.microsoft.com/en-us/library/cfa084cz.aspx

HTH. If anything is unclear, please feel free to mark this post as Not Answered and post your reply. Thanks!

sql

Data modification tracking

Hi,

We have a little problem with some data in a particular table. One day the data is ok, and the other day it is not. To be able of trapping the data when it is changing, I was thinking for a trigger.

I could used the profiler, but Profiler tends to show too much data which would be too hard to manage.

With the Trigger, I can go check at the sysprocesses to know which program name has run the problematic query and even capture the computer name. (I haven't construct the trigger yet, but by searching the sysprocesses for runnable process I think I can find the right path to the program running the problematic query.)

The problem is that I will need to know the problematic query. Is there a way to know, within the trigger, which query fired the current trigger ?

Thanks in advance

FrancoisThe trigger can't tell who fired it, but you can easily insert some code in your procedure that will insert an entry into an audit log, or update a LastProcessToMessWithThisData field in your table.

Use the trigger for maintaining to audit columns in your table: Modified (DateTime) and Modifier (nvarchar(50)).

blindman|||The trigger doesn't know what process changed the data, but you can use it to track the system user and the time the change occured.

Try adding some code to your stored procedure so that it logs its execution in an audit table.

blindman|||The trigger doesn't know what process changed the data, but you can use it to track the system user and the time the change occured.

Try adding some code to your stored procedure so that it logs its execution in an audit table.

blindman