How to reference Html.TextBoxFor value in Controller action (Updating a table in asp.net-mvc)

Question

I am creating a sales web application. I want to update the quantity left in stock after a sales is input. I set an id for the textbox, and am using the textbox id inside the query. I believe am doing it wrongly and I will appreciate any help. Please see my View and Model codes below. Thanks in advance. (I will also appreciate any other thought to do this)

My View Code

<div class="container" style="width:40%; margin-top:2%">
@using (Html.BeginForm("SaveSales", "Sales", FormMethod.Post))
{

    @Html.DropDownListFor(model => model.Prod_id, ViewBag.SalesName as        SelectList, "--Select Product--", new { id = "ProdIds", @class = "form-control"        })
    <br />
    @Html.TextBoxFor(model => model.Unit_purchase, new { id = "quant", @class = "form-control", @placeholder = "Amount Purchase" })
    <br />
    @Html.TextBoxFor(model => model.Unit_price, new { @class = "form-control", @placeholder = "Unit Price" })
    <br />
    @Html.DropDownListFor(model =>model.Emp_id, ViewBag.EmpName as SelectList, "--select Employee--", new { @class = "form-control" })
    <br />
    //@Html.TextAreaFor(model => model.Dates, new{@placeholder = "Date", @type = "date", @Value = Model.Dates.ToString("yyyy-MM-dd") })
    @Html.EditorFor(model => model.Dates, new { @type = "date" })
    <br />
    <br />

    @Html.DropDownListFor(model => model.Cust_id, ViewBag.CustName as SelectList, "--Select Customer--", new { @class = "form-control" })
    <br />
    <input type="Submit" value=" Submit" />  <input type="reset" value=" Reset" />
}
 </div>

My Controller Code (updating query code start at ---- using(SqlConnection……)

    namespace Salesapp.Controllers
     {
     public class SalesController : Controller
      {
        // GET: Sales
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult SalesIndex()
        {
            SalesLayanEntities3 db = new SalesLayanEntities3();
            List<Product> list = db.Products.ToList();
            ViewBag.SalesName = new SelectList(list, "prod_id", "prod_name");

            List<Employee> listi = db.Employees.ToList();
            ViewBag.EmpName = new SelectList(listi, "emp_id", "emp_name");


            List<Customer> listiw = db.Customers.ToList();
            ViewBag.CustName = new SelectList(listiw, "cust_id", "cust_name");

            return View();
        }

        public ActionResult SaveSales(SalesForm model)
        {
            try
            {
                SalesLayanEntities3 db = new SalesLayanEntities3();
                Sales_Record sale_prod = new Sales_Record();

                sale_prod.unit_price = model.Unit_purchase;
                sale_prod.unit_purchase = model.Unit_price;
                sale_prod.prod_id = model.Prod_id;
                sale_prod.emp_id = model.Emp_id;
                sale_prod.Dates = model.Dates;
                sale_prod.cust_id = model.Cust_id;

                db.Sales_Record.Add(sale_prod);
                db.SaveChanges();
                int latestProdId = sale_prod.sales_id;
                TempData["status"] = "Success";

                using (SqlConnection sqlCon = new SqlConnection(@"Data Source=servername;Initial Catalog=SalesLayan;User ID=username;Password=mypassword;"))
                {
                    sqlCon.Open();
                    SqlCommand cmd12 = sqlCon.CreateCommand();
                    cmd12.CommandType = CommandType.Text;
                    cmd12.CommandText = "update product set prod_quantity=prod_quantity-" + quant.Text "where prod_id=" + ProdIds.Text;
                    cmd12.ExecuteNonQuery();
                }

            }

            catch (Exception ex)
            {
                throw ex;


            }
            return RedirectToAction("SalesIndex");
        }
    }  
}

Answer 1

In your query you may have an incorrect variable reference for the quantity to be deducted from the stock.

cmd12.CommandText = "update product set prod_quantity=prod_quantity-" + quant.Text "where prod_id=" + ProdIds.Text;

On the line above quant.Text may be an incorrect variable reference.

quant.Text may need to be sale_prod.unit_price or model.Unit_purchase

Your sql command is setting the prod_quantity = prod_quantity - quant.Text.

However there is no variable quant.Text in the SaveSales method.

When using the Helper TextBoxFor it uses the model name as the input field name:

Example: TextBoxFor() in Razor View

@model Student

@Html.TextBoxFor(m => m.StudentName, new { @class = "form-control" })  

Html Result:

<input class="form-control" 
        id="StudentName" 
        name="StudentName" 
        type="text" 
        value="John" />

TextBoxFor

TextBoxFor helper method is a strongly typed extension method. It generates a text input element for the model property specified using a lambda expression. TextBoxFor method binds a specified model object property to input text. So it automatically displays a value of the model property in a textbox and visa-versa.

Reference:

• Create TextBox using HtmlHelper in ASP.Net MVC

When the form is posted back .NET MVC composes the form values into the Action parameter type using what is called Model Binding:

ASP.NET MVC model binding allows you to map HTTP request data with a model. It is the process of creating .NET objects using the data sent by the browser in an HTTP request. The ASP.NET Web Forms developers who are new to ASP.Net MVC are mostly confused how the values from View get converted to the Model class when it reaches the Action method of the Controller class, so this conversion is done by the Model binder.

Reference

• ASP.NET MVC - Model Binding

In relation to Joel's comment, it would be best practice when passing parameters to an sql statement to use SqlParameters, as in the example below:

// 1. declare command object with parameter
  SqlCommand cmd = new SqlCommand(
      "select * from Customers where city = @City", conn);

Using parameterized sql commands would help to prevent sql injection attacks. A good practice would also be to filter and validate your user input, since you are using it in your sql update command.

References

• preparing a SqlCommand Object for Parameters

Answer 2

Looking at this section:

using (SqlConnection sqlCon = new SqlConnection(@"Data Source=servername;Initial Catalog=SalesLayan;User ID=username;Password=mypassword;"))
{
    sqlCon.Open();
    SqlCommand cmd12 = sqlCon.CreateCommand();
    cmd12.CommandType = CommandType.Text;
    cmd12.CommandText = "update product set prod_quantity=prod_quantity-" + quant.Text "where prod_id=" + ProdIds.Text;
    cmd12.ExecuteNonQuery();
}

Do it like this instead to avoid sql injection issues and improve performance:

string sql = "update product set prod_quantity=prod_quantity- @sold_quantity where prod_id= @Prod_ID";
using (var sqlCon = new SqlConnection(@"Data Source=servername;Initial Catalog=SalesLayan;User ID=username;Password=mypassword;"))
using (var cmd = new SqlCommand(sql, sqlCon))
{
    //Have to guess at types and lengths here. Use actual types and lengths from the database
    cmd.Parameters.Add("@sold_quantity", SqlDbType.Int).Value = int.Parse(quant.Text);
    cmd.Parameters.Add("@Prod_ID", SqlDbType.Int).Value = int.Parse(ProdIds.Text);
    sqlCon.Open();
    cmd12.ExecuteNonQuery();
}