When running this locally this works fine, but after publishing and running it I get "There is already an open DataReader associated with this Command which must be closed first."
Is there a proper way to generate multiple objects on different tables using the same collections data?
This may inherently be the wrong method of handling such a situation.
Im basically using the information from one objects form to create another object as well.
Localhost and the published version are using the same connection string. I've tried settings MARS to true
[HttpPost]
[ValidateAntiForgeryToken]
[ActionFilters.AuditInventory]
public ActionResult Create([Bind(Include = "CheckoutID,Date,Provider,Injection,Patient,Chart,Location,Lot,Restocked,Authorized,Accountable,Quantity")] CheckOutInjectionViewModel checkOutInjectionViewModel)
{
foreach (var i in db.Inventories)
{
if (i.Location_Injection.Bottle == null)
{
if (i.Location_Injection.Location.Location == checkOutInjectionViewModel.Location && i.Location_Injection.Injection.Injection == checkOutInjectionViewModel.Injection)
{
i.Quantity -= checkOutInjectionViewModel.Quantity;
}
}
}
if (ModelState.IsValid)
{
db.CheckedOutInjections.Add(checkOutInjectionViewModel);
db.SaveChanges();
var injection = new Injections
{
Accountable = checkOutInjectionViewModel.Accountable,
Date = checkOutInjectionViewModel.Date,
Injection = checkOutInjectionViewModel.Injection,
Location = checkOutInjectionViewModel.Location,
MySignature = checkOutInjectionViewModel.MySignature,
Provider = checkOutInjectionViewModel.Provider,
Quantity = checkOutInjectionViewModel.Quantity,
};
db.InjectionsAggregate.Add(injection);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(checkOutInjectionViewModel);
}
Stack Trace
[InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.]
System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) +2076159
System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +328
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +161
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +121
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +202
System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +104
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +505
System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +37
[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.]
System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +126
System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +1047
System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +469
System.Data.Entity.Core.Objects.<>c__DisplayClass7.<GetResults>b__5() +205
System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +238
System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +349
System.Data.Entity.Core.Objects.DataClasses.EntityReference`1.Load(MergeOption mergeOption) +188
System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.DeferredLoad() +449
System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.LoadProperty(TItem propertyValue, String relationshipName, String targetRoleName, Boolean mustBeNull, Object wrapperObject) +131
System.Data.Entity.Core.Objects.Internal.<>c__DisplayClass7`2.<GetInterceptorDelegate>b__2(TProxy proxy, TItem item) +151
System.Data.Entity.DynamicProxies.Inventory_AAF69D7ACE3EC0D7440AC81F5B4FEF9AA1625FB9547A97AF87B7EEE3DB9F6C07.get_Location_Injection() +39
Hughston_Injections.Controllers.CheckOutInjectionViewModelsController.Create(CheckOutInjectionViewModel checkOutInjectionViewModel) +123
lambda_method(Closure , ControllerBase , Object[] ) +139
System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +209
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +35
System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +39
System.Web.Mvc.Async.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) +67
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +42
System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d() +72
System.Web.Mvc.Async.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +386
System.Web.Mvc.Async.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +386
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +42
System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +30
System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +186
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +38
System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +29
System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +65
System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +53
System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +36
System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +38
System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +44
System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +65
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +38
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +399
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +157
This is the solution I came up with, I continued to receive the open DataReader errors ONLY when running it from the server I published to, everything worked fine running locally.
After converting everything into a sql command the final thing I had to do was move the foreach loop that referenced the db context (declared in the controller) and it had to be AFTER I saved changes to the new context instance, before the try for example would still result in failure.
[HttpPost]
[ValidateAntiForgeryToken]
[ActionFilters.AuditInventory]
public ActionResult Create([Bind(Include = "CheckoutID,Date,Provider,Injection,Patient,Chart,Location,Lot,Restocked,Authorized,Accountable,Quantity")] CheckOutInjectionViewModel checkOutInjectionViewModel)
{
if (ModelState.IsValid)
{
StringBuilder sb = null;
using (SqlConnection conn = new SqlConnection("CONNECTION_STRING"))
{
try
{
sb = new StringBuilder("INSERT INTO Injections VALUES (");
sb.Append("'" + (checkOutInjectionViewModel.Date.HasValue ? checkOutInjectionViewModel.Date.Value.ToString("yyyy-MM-dd") : "1999-01-01") + "',");
sb.Append("'" + checkOutInjectionViewModel.Provider + "', ");
sb.Append("'" + checkOutInjectionViewModel.Quantity.ToString() + "', ");
sb.Append("'" + checkOutInjectionViewModel.Accountable + "', ");
sb.Append("'" + checkOutInjectionViewModel.Location + "', ");
sb.Append("NULL,");
sb.Append("'" + checkOutInjectionViewModel.Injection + "');");
sb.Append("INSERT INTO CheckOutInjectionViewModels VALUES (");
sb.Append("'" + (checkOutInjectionViewModel.Date.HasValue ? checkOutInjectionViewModel.Date.Value.ToString("yyyy-MM-dd") : "1999-01-01") + "',");
sb.Append("'" + checkOutInjectionViewModel.Patient + "', ");
sb.Append("'" + checkOutInjectionViewModel.Chart + "', ");
sb.Append("'" + checkOutInjectionViewModel.Lot + "', ");
sb.Append("'FALSE',");
sb.Append("NULL,");
sb.Append("'FALSE',");
sb.Append("'" + checkOutInjectionViewModel.Provider + "', ");
sb.Append("'" + checkOutInjectionViewModel.Injection + "', ");
sb.Append("'" + checkOutInjectionViewModel.Location + "', ");
sb.Append("'" + checkOutInjectionViewModel.Accountable + "', ");
sb.Append("'" + checkOutInjectionViewModel.Quantity.ToString() + "', ");
sb.Append("NULL);");
conn.Open();
var context = new ApplicationDbContext();
context.Database.ExecuteSqlCommand(sb.ToString());
context.SaveChanges();
foreach (var i in db.Inventories)
{
if (i.Location_Injection.Bottle == null)
{
if (i.Location_Injection.Location.Location == checkOutInjectionViewModel.Location && i.Location_Injection.Injection.Injection == checkOutInjectionViewModel.Injection)
{
i.Quantity -= checkOutInjectionViewModel.Quantity;
}
}
}
}
catch (Exception ex)
{
ViewBag.Error = ex.Message + Environment.NewLine + sb.ToString();
ViewBag.Locations = new SelectList(db.Locations, "Location", "Location");
ViewBag.Providers = new SelectList(db.Providers, "Provider", "Provider");
ViewBag.Injections = new SelectList(db.Injections, "Injection", "Injection");
ViewBag.Accountables = new SelectList(db.Accountables, "Accountable", "Accountable");
}
finally
{
conn.Close();
}
return RedirectToAction("Index");
}
}
return View(checkOutInjectionViewModel);
}