locked
Using SQL statement to add data to Entity Framework DataBase RRS feed

  • Question

  • User-2111854606 posted

    Controller:

            [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Register(User user)
            {
                if (ModelState.IsValid)
                {
                    string sqlQuery = "INSERT INTO dbo.Users VALUES (" + user.ToString() + ")";
                    db.Database.SqlQuery<User>(sqlQuery);
                    db.SaveChanges();
    
                    Session["User"] = user.UserName;
                    Session["Pass"] = user.Password;
                    Session["RoleID"] = user.RoleID;
    
                    return RedirectToAction("Index", "Shop");
                }
    
                return View(user);
            }

    Everything in this action works besides the sqlQuery part, I got no errors during run-time or compilation.

    The data simply not being added to the Database table.

    The ToString() Simply return all the props with commas between each other:

    public partial class User
    {
        public User()
        {
            this.Orders = new HashSet<Order>();
        }
    
        public int UserID { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public int RoleID { get; set; }
    
        public virtual ICollection<Order> Orders { get; set; }
        public virtual Role Role { get; set; }
    
        public override string ToString()
        {
            return "'" + this.UserID + "','" + this.UserName + "','" + this.Password 
                + "','" + this.Name + "','" + this.Email + "','" + this.Address + "','" + this.RoleID + "'";
        }
    }

    Table Data Structure:

    CREATE TABLE [dbo].[Users]
    (
        [UserID] INT NOT NULL PRIMARY KEY IDENTITY, 
        [UserName] TEXT NOT NULL, 
        [Password] TEXT NOT NULL, 
        [Name] TEXT NOT NULL, 
        [Email] TEXT NOT NULL, 
        [Address] TEXT NOT NULL, 
        [RoleID] INT NOT NULL, 
        CONSTRAINT [FK_Users_Roles] FOREIGN KEY ([RoleID]) REFERENCES [Roles]     ([RoleID])
    )

    Stack Trace:

    [SqlException (0x80131904): String or binary data would be truncated.
    The statement has been terminated.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1787814
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5341674
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +546
       System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1693
       System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +869
       System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +413
       System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +163
       System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c) +10
       System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +72
       System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) +356
       System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() +103
       System.Data.Entity.Core.Objects.<>c__DisplayClass59.<ExecuteStoreCommand>b__58() +32
       System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +288
       System.Data.Entity.Core.Objects.<>c__DisplayClass59.<ExecuteStoreCommand>b__57() +82
       System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +164
       System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommand(TransactionalBehavior transactionalBehavior, String commandText, Object[] parameters) +157
       System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters) +56
       System.Data.Entity.Database.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters) +61
       System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[] parameters) +44
       Site.Controllers.AccountController.Register(User user) in e:\Development\workspace\Web\Augmented Reality\Site\Controllers\AccountController.cs:54
       lambda_method(Closure , ControllerBase , Object[] ) +104
       System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
       System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +156
       System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
       System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) +22
       System.Web.Mvc.Async.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult) +29
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32
       System.Web.Mvc.Async.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d() +50
       System.Web.Mvc.Async.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f() +225
       System.Web.Mvc.Async.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult) +10
       System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34
       System.Web.Mvc.Async.<>c__DisplayClass2b.<BeginInvokeAction>b__1c() +26
       System.Web.Mvc.Async.<>c__DisplayClass21.<BeginInvokeAction>b__1e(IAsyncResult asyncResult) +100
       System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
       System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
       System.Web.Mvc.Controller.<BeginExecuteCore>b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) +13
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +36
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
       System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +39
       System.Web.Mvc.Controller.<BeginExecute>b__15(IAsyncResult asyncResult, Controller controller) +12
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +28
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
       System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +29
       System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
       System.Web.Mvc.MvcHandler.<BeginProcessRequest>b__5(IAsyncResult asyncResult, ProcessRequestState innerState) +21
       System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +36
       System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +54
       System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +31
       System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
       System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9651796
       System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

     

    What am I doing wrong ?

    Sunday, May 3, 2015 6:13 AM

Answers

  • User1711366110 posted

    hi rubik951,
      According to your given table structure, "UserID" is identity column which means it can insert userid (auto-increment) based on seed like (1,2,3...)
    you cannot able to insert "UserId" column in your table manually, it will insert automatically 
    So you can replace your code like below :

    Controller :

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Register(User user)
    {
      if (ModelState.IsValid)
       {
        string sqlQuery = "INSERT INTO dbo.Users (UserName,Password,Name,Email,Address,RoleID) VALUES (" + user.ToString() + ")";
        db.Database.SqlQuery<User>(sqlQuery);
        db.SaveChanges();
    
        Session["User"] = user.UserName;
        Session["Pass"] = user.Password;
        Session["RoleID"] = user.RoleID;
    
        return RedirectToAction("Index", "Shop");
       }
    
      return View(user);
    }

    ToString() method :

    public partial class User
    {
        public User()
        {
            this.Orders = new HashSet<Order>();
        }
    
        public int UserID { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public int RoleID { get; set; }
    
        public virtual ICollection<Order> Orders { get; set; }
        public virtual Role Role { get; set; }
    
        public override string ToString()
        {
            return "'" + this.UserName + "','" + this.Password 
                + "','" + this.Name + "','" + this.Email + "','" + this.Address + "','" + this.RoleID + "'";
        }
    }

    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 4, 2015 1:12 AM

All replies

  • User1711366110 posted

    hi rubik951,
      According to your given table structure, "UserID" is identity column which means it can insert userid (auto-increment) based on seed like (1,2,3...)
    you cannot able to insert "UserId" column in your table manually, it will insert automatically 
    So you can replace your code like below :

    Controller :

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Register(User user)
    {
      if (ModelState.IsValid)
       {
        string sqlQuery = "INSERT INTO dbo.Users (UserName,Password,Name,Email,Address,RoleID) VALUES (" + user.ToString() + ")";
        db.Database.SqlQuery<User>(sqlQuery);
        db.SaveChanges();
    
        Session["User"] = user.UserName;
        Session["Pass"] = user.Password;
        Session["RoleID"] = user.RoleID;
    
        return RedirectToAction("Index", "Shop");
       }
    
      return View(user);
    }

    ToString() method :

    public partial class User
    {
        public User()
        {
            this.Orders = new HashSet<Order>();
        }
    
        public int UserID { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public int RoleID { get; set; }
    
        public virtual ICollection<Order> Orders { get; set; }
        public virtual Role Role { get; set; }
    
        public override string ToString()
        {
            return "'" + this.UserName + "','" + this.Password 
                + "','" + this.Name + "','" + this.Email + "','" + this.Address + "','" + this.RoleID + "'";
        }
    }

    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 4, 2015 1:12 AM
  • User-2111854606 posted

    Still not working, I have tried exactly what you wrote:

    * I got no errors

    * Nothing was add to the table

    Monday, May 4, 2015 4:42 AM