locked
How to save form data to SQL Server database in MVC5 RRS feed

  • Question

  • User-94285869 posted

    Hi,

    I'm developing a website that requires User Authentication.

    I have a form data in Create.cshtml.  In my controller named, SoilSamplesController, I have the following ActionResult:

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create([Bind(Include = "Email,FieldId,FieldAcres,StateName,CountyName,SoilType,TillageType,CoverCrop")] SoilSample soilSample)
    {
      try
      {
        if (ModelState.IsValid)
        {
            db.SoilSamples.Add(soilSample);
            db.SaveChanges();
            return RedirectToAction("SoilSamples/Index");
        }
      }
      catch(DataException)
      {
        ModelState.AddModelError("", "Unable to save changes. Try again.");
      }

      return View(soilSample);
    }

    I have a btnSave in Create.cshtml view as follows:
        <input id="btnSave" value="Save" />

    How do I call the Controller's ActionResult method when users click the button?

    The database is not a localdb.  It's in full Standard version of SQL Server.  I've created an edmx file for it in my project but  I'm unfamiliar with using Entity Framework database at this time. 

    Appreciate any help.

    Monday, December 10, 2018 5:14 PM

Answers

  • User1724605321 posted

    Hi mgambone , 

    You can refer to below code sample :

    Controller :

    public ActionResult Create()
            {
                State info = new State();
                var list = db.States.ToList();
                ViewData["State"] = new SelectList(list, "StateId", "StateName", info.StateName);
    
                return View();
            } 
    [ HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create([Bind(Include = "Email,FieldId,FieldAcres,StateName,CountyName,SoilType,TillageType,CoverCrop")] SoilSample soilSample)
    {
        try
        {
            if (ModelState.IsValid)
            {
                db.SoilSamples.Add(soilSample);
                db.SaveChanges();
                return RedirectToAction("SoilSamples/Index");
            }
        }
        catch (DataException)
        {
            ModelState.AddModelError("", "Unable to save changes. Try again.");
        }
    
        return View(soilSample);
    }
    

    View :

    <div class="form-group">
                @Html.LabelFor(model => model.StateName, htmlAttributes: new { @class = "control-label" })
                <div>
                    @Html.DropDownListFor(model => model.StateName, (SelectList)ViewData["State"], "--Select a State--", new { htmlAttributes = new { @class = "form-control" } })
                    @Html.ValidationMessageFor(model => model.StateName, "", new { @class = "text-red" })
                </div>
            </div>
    

    Hope it helps .

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 11, 2018 9:12 AM
  • User475983607 posted

    mgambone

    Hi, Nan Yu,

    I looked at what's stored in ViewData in debug mode and the values contained in the ViewData's Model are:

    CountyName = "undefined"  (string) [dropdownlist selection]
    CoverCrop = null (bool?)  [radio button selection]
    Email = correct value (string)
    FieldAcres = 10.5 -  correct value (decimal?)
    FieldId = 1 - correct value (int?)
    StateId = null (string) [should be "48" for Texas]
    StateName= "48" (string) [value should be for FieldId but is stored in FieldName]
    SoilType = "crop" [correct value]
    TillageType = "minTill" [Correct value]

    I'm not sure why the types for bool has question mark in it.  Also in the ModelState, it's missing StateId in Keys and Values.


    I'll check again why there are inconsistencies in the number of fields/columns in the ModelState and my db entities.

    This is expected results given the code snippet.

    StateId = null (string) [should be "48" for Texas]
    StateName= "48" (string) [value should be for FieldId but is stored in FieldName]

    This razor syntax submits the StateId in the StateName parameter.

    <div class="form-group">
        @Html.LabelFor(model => model.StateName, htmlAttributes: new { @class = "control-label" })
        <div>
            @Html.DropDownListFor(model => model.StateName, new SelectList(ViewBag.State, "StateId", "StateName"), "--Select a State--", new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.StateName, "", new { @class = "text-red" })
        </div>
    </div>

    The value of the DropDownListFor is stored in the value attribute.  If you want the name then change the SelectList so the name is added to the value attribute.

    new SelectList(ViewBag.State, "StateName", "StateName")

    Essentially, the code is functioning exactly as you wrote it. 

    There's no StateId parameter because, I assume, there's no form input named StateId.  I also assume the other null inputs have similar HTML Form (Razor) syntax errors.

    Generally, null or missing model values after model binding indicates a name mismatch between the forum inputs and the model properties or invalid markup.  Is there anyway you can provide the Model and View source code so we can see how you've code the HTML Form?

    Keep in mind the Getting Started tutorial on this site cover these fundamentals constructs.  It might be worth your while to go through a few fundamental tutorials or spend time learning how HTML Web Forms and model binding works. 

    https://www.w3schools.com/html/html_forms.asp

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/getting-started

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 11, 2018 5:48 PM

All replies

  • User475983607 posted

    I have a btnSave in Create.cshtml view as follows:
        <input id="btnSave" value="Save" />

    The HTML is invalid and missing the name and most importantly the type attribute.

    <input id="btnSave" type="submit" name="btnSave" value="Save" />

    How do I call the Controller's ActionResult method when users click the button?

    The html form's action attribute defines the post action which is not included in the code snippet.    I recommend going through the Getting Started tutorials from the learn link above.  The tutorials cover MVC fundamentals like submitting an HTML form to a controller.

    https://www.asp.net/mvc

    The database is not a localdb.  It's in full Standard version of SQL Server.  I've created an edmx file for it in my project but  I'm unfamiliar with using Entity Framework database at this time. 

    I'm not sure what problem you are having with the database.  Can you clarify?   

    Monday, December 10, 2018 5:31 PM
  • User-94285869 posted

    Hi,

    Thanks for the prompt reply.

    I added the type="submit" and name="btnSave".  The problem is, it's not posting the data into the back-end database after clicking the button.

    Monday, December 10, 2018 5:51 PM
  • User475983607 posted

    I added the type="submit" and name="btnSave".  The problem is, it's not posting the data into the back-end database after clicking the button.

    I recommend that you add a breakpoints in the target action to debug the code.  Verify the form is pointing to the expected action if the breakpoint is not getting hit.

    Post all the relevant code if you are still having issues.  The posted code is missing too much information to guess where the bug is located.

    Monday, December 10, 2018 6:09 PM
  • User-94285869 posted

    Hi,

    I placed a break point on my Action and it's raising an error on the dropdownlist for StateName:


    <div class="form-group">
        @Html.LabelFor(model => model.StateName, htmlAttributes: new { @class = "control-label" })
        <div>
            @Html.DropDownListFor(model => model.StateName, new SelectList(ViewBag.State, "StateId", "StateName"), "--Select a State--", new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.StateName, "", new { @class = "text-red" })
        </div>
    </div>

    Error says, "Value cannot be null.  Parameter name:  items"

    I have a State class that has two properties:  StateId and StateName.

    Monday, December 10, 2018 9:52 PM
  • User1724605321 posted

    Hi mgambone , 

    You can refer to below code sample :

    Controller :

    public ActionResult Create()
            {
                State info = new State();
                var list = db.States.ToList();
                ViewData["State"] = new SelectList(list, "StateId", "StateName", info.StateName);
    
                return View();
            } 
    [ HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create([Bind(Include = "Email,FieldId,FieldAcres,StateName,CountyName,SoilType,TillageType,CoverCrop")] SoilSample soilSample)
    {
        try
        {
            if (ModelState.IsValid)
            {
                db.SoilSamples.Add(soilSample);
                db.SaveChanges();
                return RedirectToAction("SoilSamples/Index");
            }
        }
        catch (DataException)
        {
            ModelState.AddModelError("", "Unable to save changes. Try again.");
        }
    
        return View(soilSample);
    }
    

    View :

    <div class="form-group">
                @Html.LabelFor(model => model.StateName, htmlAttributes: new { @class = "control-label" })
                <div>
                    @Html.DropDownListFor(model => model.StateName, (SelectList)ViewData["State"], "--Select a State--", new { htmlAttributes = new { @class = "form-control" } })
                    @Html.ValidationMessageFor(model => model.StateName, "", new { @class = "text-red" })
                </div>
            </div>
    

    Hope it helps .

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 11, 2018 9:12 AM
  • User-94285869 posted

    Hi,

    I'm having trouble getting the variable "list" in ActionResult Create() because my source for the list of states is not the database but a CSV file.  I'm reading the CSV file and getting the state name and corresponding counties from the CSV file.  That's why my list is as follows:

    string stPath = System.AppDomain.CurrentDomain.BaseDirectory;

    List<State> states = System.IO.File.ReadAllLines(stPath + "/data/conus_states.csv")
        .Skip(1)
        .Select(v => State.FromCsv(v))
        .ToList();

    ViewBag.State = states;

    The error is the same: "Value cannot be null.  Parameter name: items"

    Tuesday, December 11, 2018 4:35 PM
  • User475983607 posted

    This post is becoming far to confusing.  The title states the issue is saving data in SQL.  The next post indicates there an issue submitting an HTML form.  Now it seems the issue is related to reading content from a CSV file used to populate a select. 

    What exactly do you need help with? 

    Tuesday, December 11, 2018 4:46 PM
  • User-94285869 posted

    Hi, Nan Yu,

    I looked at what's stored in ViewData in debug mode and the values contained in the ViewData's Model are:

    CountyName = "undefined"  (string) [dropdownlist selection]
    CoverCrop = null (bool?)  [radio button selection]
    Email = correct value (string)
    FieldAcres = 10.5 -  correct value (decimal?)
    FieldId = 1 - correct value (int?)
    StateId = null (string) [should be "48" for Texas]
    StateName= "48" (string) [value should be for FieldId but is stored in FieldName]
    SoilType = "crop" [correct value]
    TillageType = "minTill" [Correct value]

    I'm not sure why the types for bool has question mark in it.  Also in the ModelState, it's missing StateId in Keys and Values.


    I'll check again why there are inconsistencies in the number of fields/columns in the ModelState and my db entities.

    Tuesday, December 11, 2018 5:20 PM
  • User475983607 posted

    mgambone

    Hi, Nan Yu,

    I looked at what's stored in ViewData in debug mode and the values contained in the ViewData's Model are:

    CountyName = "undefined"  (string) [dropdownlist selection]
    CoverCrop = null (bool?)  [radio button selection]
    Email = correct value (string)
    FieldAcres = 10.5 -  correct value (decimal?)
    FieldId = 1 - correct value (int?)
    StateId = null (string) [should be "48" for Texas]
    StateName= "48" (string) [value should be for FieldId but is stored in FieldName]
    SoilType = "crop" [correct value]
    TillageType = "minTill" [Correct value]

    I'm not sure why the types for bool has question mark in it.  Also in the ModelState, it's missing StateId in Keys and Values.


    I'll check again why there are inconsistencies in the number of fields/columns in the ModelState and my db entities.

    This is expected results given the code snippet.

    StateId = null (string) [should be "48" for Texas]
    StateName= "48" (string) [value should be for FieldId but is stored in FieldName]

    This razor syntax submits the StateId in the StateName parameter.

    <div class="form-group">
        @Html.LabelFor(model => model.StateName, htmlAttributes: new { @class = "control-label" })
        <div>
            @Html.DropDownListFor(model => model.StateName, new SelectList(ViewBag.State, "StateId", "StateName"), "--Select a State--", new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.StateName, "", new { @class = "text-red" })
        </div>
    </div>

    The value of the DropDownListFor is stored in the value attribute.  If you want the name then change the SelectList so the name is added to the value attribute.

    new SelectList(ViewBag.State, "StateName", "StateName")

    Essentially, the code is functioning exactly as you wrote it. 

    There's no StateId parameter because, I assume, there's no form input named StateId.  I also assume the other null inputs have similar HTML Form (Razor) syntax errors.

    Generally, null or missing model values after model binding indicates a name mismatch between the forum inputs and the model properties or invalid markup.  Is there anyway you can provide the Model and View source code so we can see how you've code the HTML Form?

    Keep in mind the Getting Started tutorial on this site cover these fundamentals constructs.  It might be worth your while to go through a few fundamental tutorials or spend time learning how HTML Web Forms and model binding works. 

    https://www.w3schools.com/html/html_forms.asp

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/getting-started

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 11, 2018 5:48 PM
  • User-94285869 posted

    Hi, mgebhard:

    Here are my code snippets:

    MODEL:  
    
    namespace HSoil.Models
    {
        using System;
        using System.Collections.Generic;
        
        public partial class SoilSample
        {
            public string Email { get; set; }
            public Nullable<int> FieldId { get; set; }
            public Nullable<decimal> FieldAcres { get; set; }
            public string StateId { get; set; }
            public string StateName { get; set; }
            public string CountyName { get; set; }
            public string SoilType { get; set; }
            public string TillageType { get; set; }
            public Nullable<bool> CoverCrop { get; set; }
        }
    }
    ======================================================
    
    VIEW:  Create.cshtml:
    
    @using (Html.BeginForm())
    {
        @Html.AntiForgeryToken()
    
        <h2>@ViewBag.Title.</h2>
    
    
        <div class="form-group">
            @Html.LabelFor(model => model.Email, htmlAttributes: new { @class = "control-label" })
            <div>
                @Html.EditorFor(model => model.Email, new { htmlAttributes = new { @class = "form-control", @Value = ViewBag.Email } })
                @Html.ValidationMessageFor(model => model.Email, "", new { @class = "text-red" })
            </div>
        </div>
        <br /><br />
    
        <div class="form-group">
            @Html.LabelFor(model => model.FieldId, htmlAttributes: new { @class = "control-label" })
            <div>
                @Html.EditorFor(model => model.FieldId, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.FieldId, "", new { @class = "text-red" })
            </div>
        </div>
        <br /><br />
    
        <div class="form-group">
            @Html.LabelFor(model => model.FieldAcres, htmlAttributes: new { @class = "control-label" })
            <div>
                @Html.EditorFor(model => model.FieldAcres, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.FieldAcres, "", new { @class = "text-red" })
            </div>
        </div>
        <br /><br />
    
        <div class="form-group">
            @Html.LabelFor(model => model.StateName, htmlAttributes: new { @class = "control-label" })
            <div>
                @Html.DropDownListFor(model => model.StateName, new SelectList(ViewBag.State, "StateId", "StateName"), "--Select a State--", new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.StateName, "", new { @class = "text-red" })
            </div>
        </div>
        <br /><br />
    
        <div class="form-group">
            @Html.LabelFor(model => model.CountyName, htmlAttributes: new { @class = "control-label" })
            <div>
                @Html.DropDownListFor(model => model.CountyName, new List<SelectListItem>(), "--Select a County--", new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CountyName, "", new { @class = "text-red" })
            </div>
        </div>
    
        <div class="form-group">
            <input type="radio" name="soilType" value="crop" asp-for="Input.SoilType" />Crop<br /><br />
            <input type="radio" name="soilType" value="hay" asp-for="Input.SoilType" />Hay<br /><br />
            <input type="radio" name="soilType" value="rangeland" asp-for="Input.SoilType" />Rangeland<br /><br />
            <input type="radio" name="soilType" value="golfcourse" asp-for="Input.SoilType" />Golf Course<br /><br />
            <input type="radio" name="soilType" value="garden" asp-for="Input.SoilType" />Garden<br /><br />
            <input type="radio" name="soilType" value="lawn" asp-for="Input.SoilType" />Lawn<br /><br />
            <input type="radio" name="soilType" value="wildlife" asp-for="Input.SoilType" />Wildlife&nbsp;Food&nbsp;Plot
            @Html.ValidationMessageFor(model => model.SoilType, "", new { @class = "text-red" })
        </div>
    
        <div class="hmy-content hmy-left">
        <div class="form-group" id="tillageBtns">
            <h3>Type of Tillage</h3>
            <input type="radio" name="tillageType" value="conventional" asp-for="Input.TillageType" />Conventional<br /><br />
            <input type="radio" name="tillageType" value="minTill" asp-for="Input.TillageType" />Minimum Tillage<br /><br />
            <input type="radio" name="tillageType" value="noTill" asp-for="Input.TillageType" />No Tillage<br /><br />
            @Html.ValidationMessageFor(model => model.TillageType, "", new { @class = "text-red" })
        </div>
        </div>
    
        <div class="hmy-content hmy-left">
        <div class="form-group" id="coverCropBtns">
            <h3>Cover Crop</h3>
            <input type="radio" name="coverCrop" value="yes" asp-for="Input.CoverCrop" />Yes<br /><br />
            <input type="radio" name="coverCrop" value="no" asp-for="Input.CoverCrop" />No<br />
        </div>
        </div>
    }
    
    ======================================================
    Public Classes:  This is a cascading dropdownlist.  User selects the state from state dropdownlist, the stateId is matched to the County, and county dropdownlist is populated.
    
        public class State
        {
            public string StateId { get; set; }  //STATE_FIPS
            public string StateName { get; set; }
    
            public static State FromCsv(string csvLine)
            {
                string[] values = csvLine.Split(',');
                State state = new State();
    
                state.StateId = values[1];
                state.StateName = values[0].Replace("'", "");            
                
                return state;
            }
        }
    
        public class County
        {
            public string STATE_FIPS { get; set; } 
            public string FIPS { get; set; }  //FIPS
            public string NAME { get; set; }
    
            public static County FromCsv(string csvLine)
            {
                string[] values = csvLine.Split(',');
                County county = new County();
                county.STATE_FIPS = values[2];
                county.FIPS = values[4];
                county.NAME = values[0].Replace("'", "");
                return county;
            }
        }
    ======================================================
    CONTROLLER:
    
    SoilSamplesController:
    
    // GET: SoilSamples/Create
        public ActionResult Create()
        {
                //Populate State and County dropdownlists
                string stPath = System.AppDomain.CurrentDomain.BaseDirectory;
    
                List<State> states = System.IO.File.ReadAllLines(stPath + "/data/conus_states.csv")
                    .Skip(1)    //skip row header
                    .Select(v => State.FromCsv(v))
                    .ToList();
    
                ViewBag.State = states;
                ViewBag.Email = User.Identity.Name;
    
                return View();
        }
    
    // POST: SoilSamples/Create
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include = "Email,FieldId,FieldAcres,StateId,StateName,CountyName,SoilType,TillageType,CoverCrop")] SoilSample soilSample)
        {
                try
                {
                    if (ModelState.IsValid)
                    {
                        db.SoilSamples.Add(soilSample);
                        db.SaveChanges();
                        return RedirectToAction("Index");
                    }
                }
                catch (DataException)
                {
                    ModelState.AddModelError("", "Unable to save changes.  Try again.");
                }
    
                return View(soilSample);
        }
    
    
    ======================================================
    
    State CSV file snippet:
    
    STATE_NAME,STATE_FIPS,STATE_ABBR,STATE_ID
    Alabama,01,AL,1
    Arizona,04,AZ,4
    Arkansas,05,AR,5
    California,06,CA,6
    Colorado,08,CO,8
    Connecticut,09,CT,9
    Delaware,10,DE,10
    District of Columbia,11,DC,11
    Florida,12,FL,12
    Georgia,13,GA,13
    
    ======================================================
    
    County CSV file snippet:
    
    NAME,STATE_NAME,STATE_FIPS,CNTY_FIPS,FIPS
    Anderson,Texas,48,001,48001
    Andrews,Texas,48,003,48003
    Angelina,Texas,48,005,48005
    Archer,Texas,48,009,48009
    Armstrong,Texas,48,011,48011
    Atascosa,Texas,48,013,48013
    Austin,Texas,48,015,48015
    Bailey,Texas,48,017,48017
    Bandera,Texas,48,019,48019
    Bastrop,Texas,48,021,48021
    Baylor,Texas,48,023,48023
    Bee,Texas,48,025,48025
    Bell,Texas,48,027,48027
    Bexar,Texas,48,029,48029
    Blanco,Texas,48,031,48031
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    Thanks for all the help so far.

    Wednesday, December 12, 2018 3:36 PM
  • User-94285869 posted

    Hi,

    For some reason, I can't get this to work using Entity Framework database.  So I went back to good old-fashioned jQuery/JavaScript functions combined with Ajax calls to my Controller Actions. 

    I created an event listener in JavaScript to trigger when users click the Save button, then Ajax-call my Controller ActionResult method to Save the form data using SqlConnection and stored procedure command.

    It was easier for me to do this than using Entity Framework data.  I keep trying this EF thing but most samples on the web do not deal with complex web controls like cascading dropdownlists or multiple types of radio buttons.

    Anyway, thanks for all the help.

    Tuesday, December 18, 2018 5:30 PM
  • User-94285869 posted

    Final Note to All:  12/19/2018:

    The answers provided by mgebhard and Nan Yu are correct.  I went back to my codes and MY MISTAKE was that one of my radio button data type was boolean but I was trying to save it as a string.  When I got that fixed, then Entity Framework worked with scaffolded Views.  I didn't have to use JavaScript to save my form data to the database.

    Whew!  What a mess I made.  Many Thanks to the advisors.

    Wednesday, December 19, 2018 10:19 PM