MVC - Insert post data into database

Question

I'm working with MVC and have created a form to insert values into my database.

My database structure has the relationships:

• One -> Many
• Many -> Many

For Example: The User table contains a foreign key to the table Company. In my View, I have a dropdownlist which contains all the companies.

What I probably need is a working example of inserting data into tables with foreign keys. I have searched around and tried many solutions but cannot figure out how to make it work.

Here's my code:

Model:

 public class DefaultConnection : DbContext
    {
        public DefaultConnection()
            : base("DefaultConnection")
        {
        }

        public DbSet<User> users{ get; set; }
    }

    public class Company
    {
        public int Id { get; set; }
        public string Name{ get; set; }
    }
public class User
    {
        public int Id { get; set; }

        [ForeignKey("Company")]
        public int Id_Company { get; set; }
        public Company Company{ get; set; }
    }

ViewModel:

public class MyViewModel
    {
        public MyViewModel()
        {
            this.u= new User();
            this.cl = new Companylist();  <== another class which i have create a selectlist
        }

        public User u{ get; set; }
        public Companylist cl{get;set;}
}

View:

<ol>
   <li>
      @Html.LabelFor(m => m.cl.Ncompany)<br />
      @Html.DropDownListFor(o => o.cl.Ncompany, Model.cl.Ncompany, "-- Select --", new { @class = "abc" })
    </li>
    <li>
      @Html.LabelFor(m => m.u.Name)<br />
      <input type="text" name="Name" value="" />
    </li>
</ol>

Controller:

 [HttpPost]
        public ActionResult Create(MyViewModel model)
        {
            if (ModelState.IsValid)
            {
                db.users.Add(model);?????????????????
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            return View(model);
        }

Solution 1

First, it appears that your DropDownListFor is not setup quite right in the sense that the drop down is for User.Id_Company and not for the company list. It most likely should be:

<li>
      @Html.LabelFor(m => m.cl.Ncompany)<br />
      @Html.DropDownListFor(o => o.u.Id_Company, Model.cl.Ncompany, "-- Select --", new { @class = "abc" })
    </li>

Next, in your controller, you can insert directly from the model being returned, but at some point, you are probably going to quit using Domain Models (models of your DB tables) and start to use View Models. With that said, you could add a new user as such:

[HttpPost]
public ActionResult Create(MyViewModel model)
{
    if (ModelState.IsValid)
    {
        var db = new DefaultConnection(); (?? is this what your context is called?)
        db.users.Add(new User{
           Id_Company = model.u.Id_Company, 
           Name = model.u.Name
        });
        db.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(model);
}

Solution 2

Your ViewModel has a Company and User. You need to match the entity with the database table entity when inserting.

Try doing this:

[HttpPost]
        public ActionResult Create(MyViewModel model)
        {
            if (ModelState.IsValid)
            {
                db.users.Add(model.User);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            return View(model);
        }

Inserting into a table with a foreign key should just be like any other insert. Assuming you have a database row for the company, it should run without a error.

User user = new User
{
    Id_Company = 1
}

db.users.Add(user);
db.SaveChanges();

Comments

[alfoks]

You can also try and create a new MVC controller using the option "MVC Controller with read/write ... using Entity Framework". Then examine the code Visual Studio is generating for the controller and the views.

[Nilesh]

You don't need a List of companies in the VIewModel. Change the view model to hold the user data and the company ID just like the User Entity.

[Moe Bataineh]

Maybe it's a problem with your database? Make sure the application is not recreating the database every time.

[user2232273]

i try it but without success :(

[Moe Bataineh]

@user2232273 Are you using a @Html.Beginform() to rally the new data to the controller?

[Moe Bataineh]

Debug and make sure the values are going to your controller properly.

[user2232273]

thanks tommy for the post, but now i get this error ==> No parameterless constructor defined for this object.

[Tommy]

Where do you declare your db variable? Where did this error you mention occur?

[user2232273]

i declare my db variable in my controller above... private DefaultConnection db = new DefaultConnection();

[Tommy]

You should place a breakpoint in your controller and find out which line is causing the crash...

[user2232273]

hmm ok tommy i go check it

[user2232273]

i got the error at line -> db.SaveChanges(); Error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_User_Company". The conflict occurred in database "Mydatabase", table "dbo.Company", column 'Id'.

[user2232273]

i got the error at line -> db.SaveChanges(); Error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_User_Company". The conflict occurred in database "Mydatabase", table "dbo.Company", column 'Id'.

[Tommy]

what is the value that comes back from the model for the CompanyId?

[Moe Bataineh]

@user2232273 Did you make sure there was data in for the ID you referenced? For example, you need to have a User of ID 2 if you made a new Company Row that takes in a foreign key of User 2.

[user2232273]

my company table have data. because i can select the company in my dropdownlist. and i try to insert in the table user with this information