How to insert multiple values from checkboxlist

Question

I have a CheckBoxList control and update button:

<asp:CheckBoxList ID="moduleselect" runat="server" DataSourceID="semester2" DataTextField="module_name" DataValueField="module_id"></asp:CheckBoxList>
            <br />
                <asp:Button ID="uploadbutton" runat="server" Text="Choose Modules" OnClick="uploadbutton_Click" CssClass="submitbtn" />
                <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="Cancel" CssClass="submitbtn" />

This contains a list of modules for students to choose and when the upload button is clicked then it should update a database table.

I have the following C#

public partial class test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        String name = Request.QueryString["studentno"];
        username.Text = name;
    }
    protected void uploadbutton_Click(object sender, EventArgs e)
    {
        String user = username.Text;

        string ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection myConnection = new SqlConnection(ConnectionString);

        myConnection.Open();

        int count = moduleselect.Items.Count;

        for (int i = 0; i < count; i++)
        {
            if (moduleselect.Items[i].Selected)
            {
                string value = moduleselect.Items[i].Value;

                String query = "INSERT INTO students_vs_modules (student_no, module_id) VALUES (@student_no, @module_id)";

                SqlCommand myCommand = new SqlCommand(query, myConnection);
                myCommand.Parameters.AddWithValue("@student_no", user);
                myCommand.Parameters.AddWithValue("@module_id", value);

                myCommand.ExecuteNonQuery();

                myConnection.Close();
            }
        }  
    }
}

However, when I select a number of modules when the upload button is clicked it only updates the initial option selected and not all of them. I'm pretty new to web development so any help would be greatly appreciated.

Nic · Accepted Answer · 2015-10-27 01:24:46Z

2

First you need loop to get checked value for the checkbox. Using SelectedValue will only get the first value you selected

For example as per below

int count = moduleselect.Items.Count;

for(int i = 0; i < count; i++)
{
    if (moduleselect.Items[i].Selected)
    {
        string value = moduleselect.Items[i].Value;

         // Do Insert
    }
}

This will insert a lot of line with different module..
If you want to add in 1 line and separate by ,
As per below

int count = moduleselect.Items.Count;
string value = string.Empty;            

for(int i = 0; i < count; i++)
{
    if (moduleselect.Items[i].Selected)
    {
        value = moduleselect.Items[i].Value + ",";
    }
}

// Trim the last ,
value = value.TrimEnd(',');

// Insert here

answered Oct 27, 2015 at 1:24

Nic

1,0247 silver badges15 bronze badges

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

2 Comments

mcclosa Over a year ago

Thanks for the help, could you maybe have a look at my amended code above and let me know where I went wrong. I am getting an error ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Nic Over a year ago

@mcclosa Put the myConnection.Close(); out of the for loop... You hit the error because second time you want to update/insert you have closed the Connection...

Collectives™ on Stack Overflow

How to insert multiple values from checkboxlist

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related