ASP.NET Pass multiple checkbox values into a SELECT query

Question

I am working on displaying a students timetable based on possible Module choices. Each time a check box is selected I want to pass through the value selected "ModuleId" to use in a SELECT query to display the timetable for all modules selected. So if a user selects 3 check boxes the 'ModuleId' from each row selected will be passed into the SELECT query.

I don't know how to store each selected "ModuleId" and add it into my select query.

Below is how I retrieve the checked value:

<asp:TemplateField>
    <ItemStyle HorizontalAlign="Center" Width="40px"></ItemStyle>
          <ItemTemplate>
             <asp:CheckBox ID="chkRow" runat="server" ToolTip='<%# Eval("ModuleId") %>' OnCheckedChanged="module_Changed" />
         </ItemTemplate>
<asp:TemplateField>

Below is my method to display the value in a label (just for testing purposes):

     protected void module_Changed(object sender, EventArgs e)
      {
         // Retrieve the check box ModuleId value to add to my SELECT query
         string moduleid = ((CheckBox)sender).ToolTip;
     }

Below is my method which contains the select query to display the timetable:

 public String[] getModulesAtCurrentSlot(int timeslotInt, String moduleID, String Day)
        {
            List<String> modulesList = new List<string>();
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "SELECT DISTINCT Module.ModuleCode,ClassType.ClassTypeName,Convert(time,Class.StartTime), Convert(time,Class.EndTime),Building.BuildingName,RoomCode.RoomCode,Class.Color" +
                     " FROM Class INNER JOIN Module ON Class.ModuleId = Module.ModuleId INNER JOIN RoomCode ON Class.RoomCodeId = RoomCode.RoomcodeId INNER JOIN Building ON RoomCode.BuildingId = Building.BuildingId INNER JOIN Days ON Class.DayId = Days.DayID INNER JOIN ClassType ON Class.ClassTypeId = ClassType.ClassTypeId WHERE " +
                     " Module.ModuleId = " + moduleID + " AND Convert(Date,StartTime) = '" + Day + "' AND " + timeslotInt.ToString() + " BETWEEN ClassScheduleStartTimeId and ClassScheduleEndTimeId";
            SqlDataReader dr = newCmd.ExecuteReader();
            while (dr.Read())
            {
                String current = "<div class='slot' " + (!dr.IsDBNull(6) ? "style=\"background-color: " + dr.GetString(6) + ";\"" : "") + ">";
                current += "<div class='line1'>" + dr.GetString(0) + "&nbsp;" + dr.GetString(1) + "</div>";// +"<br />";
                current += "<div class='line2'>" + dr.GetTimeSpan(2).ToString().TrimEnd('0').TrimEnd('0').TrimEnd(':') + "&nbsp;-&nbsp;" + dr.GetTimeSpan(3).ToString().TrimEnd('0').TrimEnd('0').TrimEnd(':') + "</div>";// +"<br />";
                current += "<div class='line3'>" + dr.GetString(4) + "&nbsp;" + dr.GetString(5) + "</div>";
                current += "</div>";
                modulesList.Add(current);
            }
            conn.Close();
            return modulesList.ToArray();
        }

On a previous page where the timetable is only displaying data for one ModuleId I've used the below query string to pass through the value.

 String module_ID = "2";
        if (Request.QueryString["module"] != null)
        {
            module_ID = Request.QueryString["module"];
        }
        else
        {
            Response.Write("Missing ?module=XX from url :(");
            Response.End();// EndRequest;
        }

DBAccess.cs screenshot:

Error screenshot:

Damo · Accepted Answer · 2017-04-22 13:46:45Z

1

If I understand correctly, you could have a session variable that contains a List of strings which holds all of the selected moduleID's

You could then use module_Changed event to add or remove moduleIDs from this List and then call getModulesAtCurrentSlot in a loop for each moduleid in the list and concatenate the returned string[]s into one longer string[] or List which you then display.

there may be some errors in the code below as I'm just doing it from memory but it should give you an idea!

protected void module_Changed(object sender, EventArgs e)
{
    List<string> lst;
    if( Session["lst"]!=null)
        lst = (List<string>)Session["lst"];
    else
           Session.Add("lst", new List<string>());


     // Retrieve the check box ModuleId value to add to my SELECT query
     string moduleid = ((CheckBox)sender).ToolTip;

    // add your own code to check if checkbox is checked or unchecked to see if you need to add or remove the ID from the list

     // to add
     if(lst.Contains(moduleid) == false)
         lst.Add(moduleid);

     // to remove - add your own code

     List<string> lstResult = new List<string>();
     foreach(var moduleID in lst)
     {
         lstResult.Add(getModulesAtCurrentSlot(timeslotInt, moduleID, Day));
     }

     // do something to display lstResult
     // e.g. drag a Gridview control on your aspx page and bind the results list to it - this is just to give you a rough idea but you'll need to play around with it to get it to work as you want, and hopefully learn something in the process ;)
     Gridview1.DataSource = lstResult;
     Gridview1.Databind();

 }

edited Apr 22, 2017 at 13:46

answered Apr 21, 2017 at 14:54

Damo

3605 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

dcraven Over a year ago

Thank you very much for providing the above code. I read through your comments and code and I think its a smart idea to use a session to pass through a string containing all ModuleId's. I am quite new to c# programming and so I am not totally sure how to pass through all ModuleId's selected as a session. Ive added code above from a previous page.

Damo Over a year ago

no problem, make sure you save your updated list back to the session too otherwise you'll lose it

dcraven Over a year ago

I have tried to implement this code in but it is throwing an error with the following: Session.Add(new List<string>)); . I understand what it is you are trying to do now. I just don't know how all the ModuleId's selected are passed through on the list. I get you use a foreach loop but all selected ModuleId's will need to be displayed in the timetable at once. The getModulesAtCurrentSlot will only replace the ModuleId with one value. Please advise :)

Damo Over a year ago

Session.Add("lst", new List<string>());

Damo Over a year ago

i've gotta go out now but i've added some extra code to give you some clues

|

Collectives™ on Stack Overflow

ASP.NET Pass multiple checkbox values into a SELECT query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related