Show checkboxes that are already checked by getting the information from database

Question

I have created a method in my asp project where I can select a garage and fill in some checkboxes that I want associated with the garage. These are stored in a database cross table. Here's the table structure:

SELECT [ID]
      ,[GarageID]
      ,[RequestProperty]
      ,[Active]
      ,[CreatedDate]
      ,[CreatedBy]
      ,[UpdatedDate]
      ,[UpdatedBy]
  FROM [dbo].[GarageCrossRequestType]

Example data after checkboxes are sent:

ID  GarageID    RequestProperty Active  
299  64043       1                  1

Now, I would like these checkboxes to be crossed/checked by getting the information from the database, so for example when choosing garage with id 64043 the box for requestproperty with value 1 should be checked/crossed. I have created a c# method for getting the information from the database like following:

public List getRequestType(int garageId)
        {
            var rModel = new List();
            try
            {
                string sql = "SELECT GarageID, RequestProperty FROM¨
                GarageCrossRequestType WHERE GarageID = " + garageId;
                var cmd = new SqlCommand(sql, Connection);

                var dt = new DataTable();
                dt.Load(cmd.ExecuteReader());

                foreach (DataRow i in dt.Rows)
                {
                    var model = new GarageModel();
                    model.GarageId = Convert.ToInt32(i["GarageID"].ToString());
                    model.Values = Convert.ToInt32(i["RequestProperty"].ToString());

                    rModel.Add(model);
                }
            }
            catch(Exception ex)
            {
                var error = ex.ToString();
            }

            finally
            {
                Connection.Close();
            }

            return rModel; 
        }

When debugging, this works and I get the correct values. However, I am stuck on how I should proceed to fill the checkboxes? I will share my code on how I send the values to the database and how I fill the checkboxes below. Here's the c# 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("@Email", model.Email);
            cmd.Parameters.AddWithValue("@Note", model.Note);
            try
            {

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

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

            }
            finally
            {
                Connection.Close();
            }

            // Add request types for garage when editing garage
            if (model.garageCrossRequestType != null) { 
            foreach (var item in model.garageCrossRequestType)
            { 
               var cmd1 = new SqlCommand("spGarageGetRequestTypes", Connection);
               cmd1.CommandType = CommandType.StoredProcedure;
               cmd1.Parameters.AddWithValue("@GarageId", model.GarageId);
               cmd1.Parameters.AddWithValue("@RequestType", item);
               cmd1.Parameters.AddWithValue("@Active", 1);

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

            }
            }

            return valid;
        }

Html(Index):

  @foreach (var items in Model)
            {
                

                
                    Choose request types for garage:


                    
                        
                            Claim
                             
                        
                    
                    
                        
                            Scheduled Service
                            
                        
                    
                    
                        
                            Tires
                            
                        
                    
                    
                        
                            Rent Replacement Car
                            
                        
                    
                    
                        
                            Other Work
                            
                        
                    
                    
                        
                            Insurance
                            
                        
                    


                
            }

JavaScript/Jquery:

$("#EditGarageBtn").click(function () {
            var customerNumber = customerNumberOfEditingGarage;
            name = $("#GarageName").val();
            countryId = $("#Country").val();
            var garageId = $("#garageId").val();
            var note = $("#Note").val();
            var email = $("#Email").val();

            var garageCrossRequestType  = $(".checkbclass:checked").map(function () {
                return $(this).val(); // to see which request types are checked
            }).toArray();
            console.log(garageCrossRequestType);

            $("#EditGarageBtn").hide();

            if (countryId == "Norway")
                countryId = 2;
            if (countryId == "Finland")
                countryId = 4;

            if (name.length > 0 && email.length > 0 && phone.length > 0 && contactperson.length > 0) {
            $.ajax({
                url: '@Url.Action("EditGarage", "Garage")',
                type: 'POST',
                dataType: 'JSON',
                data: {
                    garageCrossRequestType: garageCrossRequestType,
                    name: name, countryId: countryId, garageId: garageId, 
                    note: note, 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");
            }
        });

Now, I've tried doing a ajax call similar to the code above to at least get the data from the database when using console.log, but I can't seem to get the data from the controller to the index either. So I guess what I need help with is 1. How I can get the data that I get in my c# method to show in the view and 2. How can I connect this so that the correct checkboxes gets checked with the values? Or maybe I am completly off and there's a simpler way to do this?

Thankful for any help!

UPDATE: So I created added this Ajax call:

 $.ajax({
                    type: "POST",
                    url: '@Url.Action("getRequestType", "Garage")?garageId=' + garageId,
                      dataType: 'JSON',
                      data: {
                          garageId: garageId, Values: Values
                      },
                        sucess: function (data) {
                            if (data != "Failure") {
                                //return garageId, value;
                        }
                    }
                  }); console.log(garageId, Values);

It works etcetera, I can see in the console log that I can get the garageId and the Value. However (lol) the problem now is that I can test for example in the model like this:

public int Values { get; set; } = 5;

which will result in showing a number 5 and the correct garageid when i use console log. But the values from the method getRequestType does not seem to connect to the model. As seen I use a foreach loop in the method and set model.Values with the database value from RequestProperty. So If I set the model to:

public int? Values { get; set; } = null;

for example, It will show null etcetera. So something is clearly not working correctly. I can't seem to figure out what about the method is wrong however, so again very thankful for any help!! (Classic programming solving one problem but finding another one lol)

user_10527 · Answer

Well, I think a listView works better.

and for the list of selections, use a checkBoxlist.

So we have

tblGarage       - our list of garages
tblitemsInGarge - list of times in each garage
tblItems        - our list of possible choices.

So, say this markup:

    
        
            
                
                
            
             
                
                Items in Garage
                 
                    
                
              
        
        
            
                
                    GarageName
                    GarageType

And we now load ListView, but for each row, we pull choices from the database. (row data bound).

So, our code to load is thus this:

    DataTable rstItems = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadMyView();
    }

    public void LoadMyView()
    {
        rstItems = MyRst("SELECT ID, ItemName from tblItems ORDER BY ItemName");

        LvGarage.DataSource = MyRst("SELECT * From tblGarage ORDER BY GarageName");
        LvGarage.DataBind();
    }

    public DataTable MyRst(string strSQL)
    {
        var rst = new DataTable();
        using (SqlCommand cmdSQL = new SqlCommand(strSQL,
            new SqlConnection(Properties.Settings.Default.TEST4)))
        {
            cmdSQL.Connection.Open();
            // fill items table
            rst.Load(cmdSQL.ExecuteReader());
        }
        return rst;
    }

But, we need on Row data bind to fill out the check box list (the table of choices), and ALSO set the selected ones (from tbleItemsInGarage).

We have this:

    protected void LvGarage_ItemDataBound(object sender, ListViewItemEventArgs e)
    {
        if (e.Item.ItemType == ListViewItemType.DataItem)
        {
            CheckBoxList ckList = (CheckBoxList)e.Item.FindControl("ckList");

            ckList.DataSource = rstItems;
            ckList.DataBind();

            // now get all rows for this garage
            int ID = (int)LvGarage.DataKeys[e.Item.DataItemIndex]["ID"];
            DataTable rstItemsChecked = new DataTable();
            rstItemsChecked = MyRst("SELECT * FROM tblItemsInGarage WHERE Garage_ID = " + ID);
            foreach (DataRow OneRow in rstItemsChecked.Rows)
                ckList.Items.FindByValue(OneRow["Item_ID"].ToString()).Selected = true;
        }
    }

And the output is now this:

All we need now, is a single save button to send any changes you make back to the database. And that code looks like this:

    protected void cmdSave_Click(object sender, EventArgs e)
    {
        foreach (ListViewItem lvRow in LvGarage.Items)
        {
            // now get all rows for this garage
            int ID = (int)LvGarage.DataKeys[lvRow.DataItemIndex]["ID"];
            string strSQL = "SELECT ID, Item_ID, Garage_ID FROM tblItemsInGarage WHERE Garage_ID = " + ID;

            using (SqlCommand cmdSQL = new SqlCommand(strSQL,
                new SqlConnection(Properties.Settings.Default.TEST4)))
            {
                cmdSQL.Connection.Open();
                SqlDataAdapter dUpdate = new SqlDataAdapter(cmdSQL);
                SqlCommandBuilder sUpdate = new SqlCommandBuilder(dUpdate);
                DataTable rstItemsChecked = new DataTable();
                rstItemsChecked.Load(cmdSQL.ExecuteReader());

                // remove all selected
                foreach (DataRow OneRow in rstItemsChecked.Rows)
                    OneRow.Delete();

                // now add back ONLY checked items
                CheckBoxList ckList = (CheckBoxList)lvRow.FindControl("ckList");
                foreach (ListItem citem in ckList.Items)
                {
                    if (citem.Selected)
                    {
                        DataRow OneRow = rstItemsChecked.NewRow();
                        OneRow["Item_ID"] = citem.Value;
                        OneRow["Garage_ID"] = ID;
                        rstItemsChecked.Rows.Add(OneRow);
                    }
                }
                dUpdate.Update(rstItemsChecked);
            }
        }
    }

Now a bit of code, but not too much!!!

Using listview, dropping in that checkBox list and DRIVING the checkbox list from the table of choices.

We get:

not a lot of markup
not a lot of code to load
But REALLY make gains in the database update process.

All in all, not a lot of code, given that we have to pull + push back the selecting from that check box list.

Show checkboxes that are already checked by getting the information from database

1 Answers