Get ID and Value from a checkbox: Return to model and use in method to insert into sql database

I’m trying to create a checkbox list where a user is supposed to be able to choose one or more options, based on the choice: this is supposed to be inserted to a database table, where the id of the choice is inserted. (This is on a page where a user can “edit garage”), therefore the garageid is also supposed to be fetched and both the garageid and the choice id should be inserted to a cross table, that I have created as following:

   [ID]
  ,[GarageID]
  ,[RequestProperty]
  ,[CreatedDate]
  ,[CreatedBy]
  ,[UpdatedDate]
  ,[UpdatedBy]

I also have a stored procedure for the insert:

ALTER PROCEDURE [dbo].[spGarageGetRequestTypes]
    -- Add the parameters for the stored procedure here

    @GarageID INT,
    @RequestType INT

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO GarageCrossRequestType
    (GarageID, RequestProperty)
    VALUES (@GarageID, @RequestType)
    
END

And the “edit page” is working and functioning, it’s where I get the garageId as well. It looks like following in view:

<div class="form-group">
                <div class="row">
                    <label class="col-xs-2 control-label">Garage</label>
                    <input type="text" class="col-lg-10 form-control" name="GarageName" id="GarageName" placeholder="Name" required="" />
                </div>
            </div>
            <div class="form-group">
                <div class="row">
                    <label class="col-xs-2 control-label">Contact person</label>
                    <input type="text" class="col-lg-10 form-control" name="ContactPerson" id="ContactPerson" placeholder="ContactPerson" required="" />
                </div>
            </div>
            <div class="form-group">
                <div class="row">
                    <label class="col-xs-2 control-label">Email</label>
                    <input type="email" class="col-lg-10 form-control" name="Email" id="Email" placeholder="Email" required="" onblur="validateEmail(this.value);" /><p id="InvalidMeg" style="font-size: 25px; color: red">Invalid e-mail address</p>
                </div>
            </div>

<script type="text/javascript">
function editGarage(e) {
            e.preventDefault();
            var dataItem = this.dataItem($(e.currentTarget).closest("tr"));
            var garageId = dataItem.GarageId;
            countryId = dataItem.CountryId;
        var email = dataItem.Email;
            var contactperson = dataItem.ContactPerson;
            if (garageId != 0) {
                $("#EditGarageBtn").show();
                $("#saveNewGarageBtn").hide();
                $("#GarageName").val(name);
                $("#Country").val(countryId);
                $("#ContactPerson").val(contactperson);
                $("#Email").val(email);
              
            }
        }

 $("#EditGarageBtn").click(function () {
            var customerNumber = customerNumberOfEditingGarage;

            name = $("#GarageName").val();
            countryId = $("#Country").val();
            var garageId = $("#garageId").val();
            var contactperson = $("#ContactPerson").val();
            var email = $("#Email").val();
            $("#EditGarageBtn").hide();

            if (name.length > 0 && email.length > 0 && contactperson.length > 0) {
            $.ajax({
                url: '@Url.Action("EditGarage", "Garage")',
                dataType: 'JSON',
                data: {
                    name: name, countryId: countryId, garageId: garageId, 
                    contactperson: contactperson, email: email
                },
                success: function (data) {
                    if (data == "Failure") {
                        toastr["error"]("Error editing Garage");
                    }
                    else {
                        toastr["success"]("Garage successfully updated");
                        customerNumberOfEditingGarage = null;
                        refreshGrid();
                    }
                },
                error: function () {

                }
            });
            } else {
                toastr["error"]("Error editing Garage");
            }
        });

</script>

Model:

 public class GarageModel
    {
        public int GarageTypeId { get; set; }
        public int CountryId { get; set; }
        public string ContactPerson { get; set; }
        public string Email { get; set; }
        public int GarageId { get; set; }


    // for the choices in the checkbox
    public int ScheduledService { get; set; } = 1;
        public int Tires { get; set; } = 2;
    }

Method:

public bool EditGarage(GarageModel model)
        {
            var valid = false;

            var cmd = new SqlCommand("spGarageEditGarage", Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@GarageId", model.GarageId);
            cmd.Parameters.AddWithValue("@CountryId", model.CountryId);
            cmd.Parameters.AddWithValue("@Name", model.Name);
            cmd.Parameters.AddWithValue("@ContactPerson", model.ContactPerson);
            cmd.Parameters.AddWithValue("@Email", model.Email);
            try
            {

                int result = cmd.ExecuteNonQuery();
                if (result == 1)
                    valid = true;
            }

            catch (SqlException ex)
            {
                throw new Exception(ex.Message);

            }
            finally
            {
                Connection.Close();
            }


        // for the choices in the checkbox (not working!)
            List<int> newlist = new List<int>();
            newlist.Add(model.Tires);
            newlist.Add(model.ScheduledService);

            foreach (var item in newlist)
            {
                if (newlist != null)
                {
                    var cmd1 = new SqlCommand("spGarageGetRequestTypes", Connection);
                    cmd1.CommandType = CommandType.StoredProcedure;
                    cmd1.Parameters.AddWithValue("@GarageId", model.GarageId);
                    cmd1.Parameters.AddWithValue("@RequestType", newlist.First());

                    int result = cmd1.ExecuteNonQuery();
                    if (result == 1)
                        valid = true;
                }
            }

            return valid;
        }

If you look at my comments in the model and the method, you can see what I’ve added for the “choices” function I’m trying to implement. Here’s the html i created for the input type as well:

@foreach (var items in Model)
                    {
                        <div class='form-group' style="margin-left: 60%;">
                            <div class="row">
                                <label class="ab">Tires</label>
                                <input type="checkbox" class="checkbclass" name="@items.Tires" id="Tires" placeholder="Tires" required="" value="@items.Tires" />
                            </div>
                        </div>

                        <div class='form-group' style="margin-left: 60%;">
                            <div class="row">
                                <label class="ab">Scheduled Service</label>
                                <input type="checkbox" class="checkbclass" name="@items.ScheduledService" id="Scheduled" placeholder="Scheduled" required="" value="@items.ScheduledService" />

                            </div>
                        </div>
                    }

Now, to the problems:

1: I need some sort of method for making sure which or if a checkbox is checked, and this needs to be returned to the model or controller in some way. I only want to return it’s numeric value, as seen in the model I want Tires to have the numeric value of 2 etcetera.

2. The datebase insert works (so at least that’s something), but the table only accepts RequestProperty and GarageID, meaning that if a user chooses 2 of the checkboxes, I need to update the database twice, creating 2 rows, but with the same garageid.

I tried posting a question about this earlier, but it was poorly explained from my side, so I’m trying again, and hoping I included everything this time. I’m open to any help/solutions that can help me solve this.

Answer

First, You need to remove all GarageCrossRequestType that containe current GarageID as the checkbox may be checked and unhacked later on edit.

This how I would do it.

Note: make sure to read the comment

javascript

$("#EditGarageBtn").click(function() {
  var customerNumber = customerNumberOfEditingGarage;
  // I assumed that you want name as the int of RequestType eg 1 or 2 that are checked
  var garageCrossRequestType = $(".checkbclass:checked").map(function(x) {
   return   parseInt($(x).attr("name"));
   });
  name = $("#GarageName").val();
  countryId = $("#Country").val();
  var garageId = $("#garageId").val();
  var contactperson = $("#ContactPerson").val();
  var email = $("#Email").val();
  $("#EditGarageBtn").hide();

  if (name.length > 0 && email.length > 0 && contactperson.length > 0) {
    $.ajax({
      url: '@Url.Action("EditGarage", "Garage")',
      dataType: 'JSON',
      data: {
        name: name,
        countryId: countryId,
        garageId: garageId,
        contactperson: contactperson,
        email: email,
        garageCrossRequestType: garageCrossRequestType // here send the checked checkedboxes
      },
      success: function(data) {
        if (data == "Failure") {
          toastr["error"]("Error editing Garage");
        } else {
          toastr["success"]("Garage successfully updated");
          customerNumberOfEditingGarage = null;
          refreshGrid();
        }
      },
      error: function() {

      }
    });
  } else {
    toastr["error"]("Error editing Garage");
  }
});

C#

// create an sqlProcedure or something instead of this, this is only to show how it work
// You have to clear all GarageCrossRequestType that containe the current GarageID
// An after insert the newly checked items
new SqlCommand("delete GarageCrossRequestType where GarageID = " + model.GarageId, Connection).ExecuteNonQuery();
List <int> newlist = new List<int>();
if (model.garageCrossRequestType != null)
    newlist.AddRange(model.garageCrossRequestType);
  foreach(var item in newlist) {
    //newlist cant be null becouse you are already in a loop. 
    // and also newlist is never null
    // if (newlist != null)

    var cmd1 = new SqlCommand("spGarageGetRequestTypes", Connection);
    cmd1.CommandType = CommandType.StoredProcedure;
    cmd1.Parameters.AddWithValue("@GarageId", model.GarageId);
    // instead of newlist.First() you are looping throw newlist eg checkboxes then it should be item
    cmd1.Parameters.AddWithValue("@RequestType", item);

    int result = cmd1.ExecuteNonQuery();
    if (result == 1)
      valid = true;

  }