How to save form data to SQL Server database in MVC5

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.

Answer 1

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?   

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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"

Answer 7

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? 

Answer 8

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.

Answer 9

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

Answer 10

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.

Answer 11

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.

Answer 12

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.