title: How to insert data into database using asp,net mvc and stored procedure
body: <p>I'm using stored procedure to insert data into a table database; however whenever I do a button, the data is not sent to the database. In addition; I would like to give an error if nothing was entered in the database or success message saying that it was entered. Not sure how to insert data into the database; what did I leave out</p>
  
  <p><strong>Form</strong></p>
  
  <pre><code>&lt;h2&gt;Requisition Screen&lt;/h2&gt;
  
  
  {
      @Html.AntiForgeryToken()
  &lt;div class="form-horizontal"&gt;
      &lt;h4&gt;Request Item&lt;/h4&gt;
      &lt;hr /&gt;
      @Html.ValidationSummary(true, "", new { @class = "text-danger" })
  
      &lt;div class="form-group"&gt;
          @Html.LabelFor(model =&gt; model.reqDate, htmlAttributes: new { @class = "col-md-2" })
  
          @Html.EditorFor(model =&gt; model.reqDate, new { htmlAttributes = new { @readonly = "readonly", @id = "reqDate", @class = "form-control" } })
          @Html.ValidationMessageFor(model =&gt; model.reqDate, "", new { @class = "text-danger" })
  
      &lt;/div&gt;
  
  
     &lt;div class="form-group"&gt;
          @Html.LabelFor(model =&gt; model.count, htmlAttributes: new { @class = "col-md-2" })
         &lt;text&gt; @Html.Encode(Model.items.Count()) &lt;/text&gt;
      &lt;/div&gt;
  
  
      &lt;div class="form-group"&gt;
          @Html.LabelFor(model =&gt; model.reqNumber, htmlAttributes: new { @class = "col-md-2" })
  
          @Html.EditorFor(model =&gt; model.reqNumber, new { htmlAttributes = new { @readonly = "readonly", @id = "reqnum", @class = "form-control" } })
          @Html.ValidationMessageFor(model =&gt; model.reqNumber, "", new { @class = "text-danger" })
  
      &lt;/div&gt;
  
      &lt;div class="form-group"&gt;
          @Html.LabelFor(model =&gt; model.department, htmlAttributes: new { @class = "col-md-2" })
  
          @*@Html.EditorFor(model =&gt; model.department, new { htmlAttributes = new { @class = "form-control" } })*@
          @Html.DropDownListFor(model =&gt; model.department, new SelectList(Model.departmentsList, "Text", "Value"), new { @id = "department", @class = "form-control" })
          @Html.ValidationMessageFor(model =&gt; model.department, "", new { @class = "text-danger" })
  
      &lt;/div&gt;
  
  
      &lt;div class="form-group"&gt;
          @Html.LabelFor(model =&gt; model.employeeDetails, htmlAttributes: new { @class = "col-md-2" })
          @*@Html.Label("Requested by:", htmlAttributes: new { @class = "col-md-2" })*@
          @if (Model.department == null)
          {
              @Html.DropDownListFor(model =&gt; model.employeeDetails.employeeNum, new SelectList(string.Empty, "Value", "Text"), new { @id = "empname", @class = "form-control" })
          }
          else
          {
              @Html.DropDownListFor(model =&gt; model.employeeDetails.employeeNum, new SelectList(ViewBag.ReceivedByReps, "Value", "Text"), "select", new { @id = "empname", @class = "form-control" })
          }
  
          @*@Html.EditorFor(model =&gt; model.employeeName, new { htmlAttributes = new { @class = "form-control" } })*@
          @Html.ValidationMessageFor(model =&gt; model.employeeDetails.employeeNum, "", new { @class = "text-danger" })
  
  
      &lt;/div&gt;
  
  
      @*@Html.DropDownListFor(model =&gt; model.employeeDetails.employeeNum, new SelectList((System.Collections.IEnumerable)TempData["company_dd3"], "Value", "Text"), "select", new { @class = "form-control" })*@
  
      @Html.HiddenFor(m =&gt; m.employeeDetails.firstName)
      @Html.HiddenFor(m =&gt; m.employeeDetails.lastName)
      @Html.HiddenFor(m =&gt; m.employeeDetails.Name)
  
  
  
  
      &lt;fieldset class="form-group"&gt;
          &lt;div class="form-horizontal"&gt;
              &lt;h4&gt;Enter Requested Item(s)&lt;/h4&gt;
  
              &lt;hr /&gt;
  
              &lt;div class="Row form-group"&gt;
                  &lt;div class="col-md-5"&gt;
                      @Html.LabelFor(model =&gt; model.item.itemNumber, htmlAttributes: new { @class = "col-md-3" })
                      @Html.TextBoxFor(model =&gt; model.item.itemNumber, null, new { @readonly = "readonly", @id = "itemNumber", @class = "form-control" })
                      @Html.ValidationMessageFor(model =&gt; model.item.itemNumber, "", new { @class = "text-danger" })
                  &lt;/div&gt;
  
                  &lt;div class="col-md-5"&gt;
                      @Html.LabelFor(model =&gt; model.item.description, htmlAttributes: new { @class = "col-md-3" })
                      @Html.TextBoxFor(model =&gt; model.item.description, null, new { @id = "description", @class = "form-control" })
                      @*&lt;font color="blue"&gt;Please use your keyboard arrow keys to select items&lt;/font&gt;*@
                      @*  @Html.EditorFor(model =&gt; model.item.description, new { @class = "control-label col-md-2", @id = "txtItem" })*@
                      @Html.ValidationMessageFor(model =&gt; model.item.description, "", new { @class = "text-danger" })
                     &lt;font color="red"&gt;@ViewBag.errorMessage&lt;/font&gt;
                  &lt;/div&gt;
              &lt;/div&gt;
  
              @Html.HiddenFor(m =&gt; m.item.lineNum)
  
  
  
  
              &lt;div class="Row  form-group"&gt;
                  &lt;div class="col-md-5"&gt;
                      @Html.LabelFor(model =&gt; model.item.price, htmlAttributes: new { @class = "col-md-3" })
                      @Html.TextBoxFor(model =&gt; model.item.price, new { @readonly = "readonly", @id = "price", @class = "form-control" })
                      @Html.ValidationMessageFor(model =&gt; model.item.price, "", new { @class = "text-danger" })
                  &lt;/div&gt;
  
                  &lt;div class="col-md-5"&gt;
                      @Html.LabelFor(model =&gt; model.item.expense_account, htmlAttributes: new { @class = "col-md-3" })
                      @Html.DropDownListFor(model =&gt; model.item.expense_account.index, new SelectList(Model.accountlist, "Value", "Text"), new { @class = "form-control" })
                      @Html.ValidationMessageFor(model =&gt; model.item.expense_account, "", new { @class = "text-danger" })
  
                  &lt;/div&gt;
  
  
                  @Html.HiddenFor(model =&gt; model.item.selecteduomtext, new { @id = "selecteduomtext" })
                  @Html.HiddenFor(model =&gt; model.item.lineNum)
                  @Html.HiddenFor(model =&gt; model.items)
  
  
                  &lt;div class="col-md-5"&gt;
                      @Html.LabelFor(model =&gt; model.item.quantity, htmlAttributes: new { @class = "col-md-3" })
                      @Html.EditorFor(model =&gt; model.item.quantity, new { htmlAttributes = new { @class = "form-control" } })
                      @Html.ValidationMessageFor(model =&gt; model.item.quantity, "", new { @class = "text-danger" })
                  &lt;/div&gt;
  
                  @Html.HiddenFor(model =&gt; model.inventory_account, new { htmlAttributes = new { @class = "form-control" } })  
  
                      &lt;div class="col-md-5"&gt;
                          @Html.LabelFor(model =&gt; model.item.UofM, htmlAttributes: new { @class = "col-md-3" })
                          @Html.DropDownListFor(model =&gt; model.item.UofM, new SelectList(string.Empty, "Text", "Value"), new { @id = "uofm", @class = "form-control" })
                          @Html.ValidationMessageFor(model =&gt; model.item.UofM, "", new { @class = "text-danger" })
                      &lt;/div&gt;
  
  
  
                  &lt;/div&gt;
  
  
                  &lt;div class="form-group"&gt;
                      &lt;div class="col-md-offset-4 col-md-12"&gt;
  
                          &lt;input type="submit" value="Add" name="Add" class="btn btn-default" width="89" /&gt;
                          &amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;
  
                          &lt;input type="button" value="Cancel" name="Cancel" margin="50px" onclick="location.href='@Url.Action("Cancel", "Requisition")' " class="btn btn-default" /&gt;
  
  
                      &lt;/div&gt;
                  &lt;/div&gt;
  
  
                  &lt;div class="form-group"&gt;
                      &lt;table id="myTable"&gt;
                          &lt;tr&gt;
                              &lt;th class="col-md-2"&gt;Item Number&lt;/th&gt;
                              &lt;th class="col-md-2"&gt;Item Description&lt;/th&gt;
                              &lt;th class="col-md-3"&gt;Expense Account&lt;/th&gt;
                              &lt;th class="col-sm-1"&gt;Quantity&lt;/th&gt;
                              &lt;th class="col-sm-1"&gt;UOM&lt;/th&gt;
  
                              &lt;th class="col-sm-1"&gt;Item Price&lt;/th&gt;
  
  
                              &lt;th&gt;&lt;/th&gt;
                          &lt;/tr&gt;
                          @{
  
                                  if (@Model.items.Count &gt; 0)
                                  {
                                      foreach (var item in @Model.items)
                                      {
                                          &lt;tr&gt;
                                              &lt;td class="col-md-3"&gt;@item.itemNumber&lt;/td&gt;
                                              &lt;td class="col-md-2"&gt;@item.description&lt;/td&gt;
                                              &lt;td class="col-md-3"&gt;@item.expense_account.getDescription&lt;/td&gt;
                                              &lt;td class="col-sm-1"&gt;@item.quantity.ToString()&lt;/td&gt;
                                              &lt;td class="col-sm-1"&gt;@item.selecteduomtext&lt;/td&gt;
  
                                              &lt;td class="col-sm-1"&gt;@item.price.ToString()&lt;/td&gt;
  
  
  
                                              &lt;td&gt;
                                                  @Html.ActionLink("Edit", "Edit", new { id = item.lineNum }) |
                                                  @Html.ActionLink("Remove", "Remove", new { id = item.lineNum }, new { onclick = "return confirm('Are you sure you would like to remove this item?'); " })
                                              &lt;/td&gt;
  
                                          &lt;/tr&gt;
  
  
                                  }
  
                              }
                          }
  
  
                      &lt;/table&gt;
  
  
                  &lt;/div&gt;
  
  
  
                  &lt;div class="form-group"&gt;
                      &lt;div class="col-md-offset-4 col-md-12"&gt;
  
  
                          &amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;
                          @*&lt;input type="button" value="Finish" name="Issue" margin="50px" onclick="location.href='/Requisition/ViewRequisition' " class="btn btn-default" /&gt;*@
                          &lt;input type="button" id="RequestItem" value="Request" name="Request"  class="btn btn-default" /&gt;
  </code></pre>
  
  <p><strong>Controller</strong></p>
  
  <pre><code>[HttpPost]
  public ActionResult RequestItem(Requisition requisition)
  {
      IssueDAO dbdata = new IssueDAO();
      dbdata.connectionString = ConfigurationManager.ConnectionStrings["TWCL_OPERATIONSConnectionString"].ConnectionString;
      getRequisition.reqDate = DateTime.Now; //Sets the transaction date to current date
      //getIssue.status = -1;
      Item item = new Item();
      try
      {
          dbdata.createRequisition(getRequisition, item);//Creates the issue in the database
      }
      catch (Exception ex)
      {
          LogWrite logWriter = new LogWrite(ex.ToString());
          ViewBag.errorMessage = "Unable to complete the Issue. Please see Log file for more Information";
          return View("RequisitionItem", getRequisition);
      }
      return View("RequisitionItem", getRequisition);
  }
  </code></pre>
  
  <p><strong>createRequisition Function</strong></p>
  
  <pre><code> public bool createRequisition(Requisition requisition, Item requisitionitem)
  {
      int rec_upd = 0;
      bool status;
      using (SqlConnection TWCLOPConnect = new SqlConnection(connectionString.ToString()))
      {
      SqlCommand command = new SqlCommand();
      SqlCommand requestcommand = new SqlCommand();
  
      //Command to update the requisition table with the header information
      requestcommand.Connection = TWCLOPConnect;
      requestcommand.CommandType = CommandType.StoredProcedure;
      requestcommand.CommandText = "requisition_sp_createRequestion";
      //issuecommand.Parameters.Add("@docnum", SqlDbType.VarChar).Value = issue.docNumber;
      //issuecommand.Parameters.Add("@doctype", SqlDbType.VarChar).Value = issue.docType;
      requestcommand.Parameters.Add("@req_num", SqlDbType.VarChar).Value = requisition.reqNumber;
      //issuecommand.Parameters.Add("@stores_rep_id", SqlDbType.VarChar).Value = issue.processedbyDetails.employeeNum;
      //issuecommand.Parameters.Add("@dept_rep_id", SqlDbType.VarChar).Value = issue.employeeDetails.employeeNum;
      //issuecommand.Parameters.Add("@inventory_acc_indx", SqlDbType.Int).Value = issue.inventory_acccount;
      requestcommand.Parameters.Add("@description", SqlDbType.Int).Value =requisitionitem.description;
      requestcommand.Parameters.Add("@Req_Date", SqlDbType.DateTime).Value = requisition.reqDate;
      requestcommand.Parameters.Add("@qty", SqlDbType.Float);
      //issuecommand.Parameters.Add("@department", SqlDbType.VarChar).Value = issue.department;
      //issuecommand.Parameters.Add("@status", SqlDbType.Int).Value = issue.status;
  
      //Command to update the issueitem table with details of the transaction 
      command.Connection = TWCLOPConnect;
      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = "createRequestedItem";
      command.Parameters.Add("@req_num", SqlDbType.VarChar).Value = requisition.reqNumber;
      //command.Parameters.Add("@inventory_acc_indx", SqlDbType.Int).Value = requisition.inventory_account;
      //command.Parameters.Add("@line_num", SqlDbType.Int);
      command.Parameters.Add("@itemnum", SqlDbType.VarChar);
      command.Parameters.Add("@qty", SqlDbType.Float);
      command.Parameters.Add("@issue_price", SqlDbType.Float);
      command.Parameters.Add("@expense_acc", SqlDbType.Int);
      //command.Parameters.Add("@inventory_acc", SqlDbType.Int);
      command.Parameters.Add("@uofm", SqlDbType.VarChar);
      try
      {
          TWCLOPConnect.Open();
          requestcommand.ExecuteNonQuery();
          foreach (var item in requisition.items)
          {
              //command.Parameters["@line_num"].Value = item.lineNum;
              command.Parameters["@itemnum"].Value = item.itemNumber;
              command.Parameters["@issue_price"].Value = item.price;
              command.Parameters["@qty"].Value = item.quantity;
              command.Parameters["@expense_acc"].Value = item.expense_account.index;
              //command.Parameters["@inventory_acc"].Value = item.inventory_account.index;
              command.Parameters["@uofm"].Value = item.selecteduomtext.Trim();
              rec_upd = command.ExecuteNonQuery();
          }
      }
      catch (Exception ex)
      {
          throw ex;
      }
      finally
      {
          TWCLOPConnect.Close();
      }
      if (rec_upd &gt; 0)
      {
          status = true;
      }
      else
      {
          status = false;
      }
  }
  return status;
  </code></pre>
  
  <p>}</p>
  
  <p><strong>Stored Procedures</strong></p>
  
  <pre><code>      CREATE PROCEDURE createrequesteditem
              @expense_acc varchar(50),
              @quantity_requested int,
              @inventory_acc varchar(50),
              @Req_No varchar(50),
              @unit_of_measure varchar(15), 
              @issue_price float
          AS
          BEGIN
              -- SET NOCOUNT ON added to prevent extra result sets from
              -- interfering with SELECT statements.
              SET NOCOUNT ON;
  
              -- Insert statements for procedure here
              Insert Into RequestedItem Values (
                  @expense_acc, 
                  @quantity_requested ,
                  @inventory_acc ,
                  @Req_No ,
                  @unit_of_measure, 
                  @issue_price );
          END
  
      CREATE PROCEDURE requisition_sp_createRequisition
              @Req_No varchar(50),    
              @Req_Date datetime,
              @description varchar,
              @quantity int
          AS
          BEGIN
              -- SET 
  
  NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;
  
          -- Insert statements for procedure here
          INSERT INTO Requisition VALUES (@Req_No,    
          @Req_Date, 
          @description,
          @quantity )
      END
  </code></pre>