Fire separate insert for each checkboxlist selection.

Question

I have a form that a user selects lines to be set up with some information. I would like the user to be able to select all the lines they need and then do a separate insert statement for for each selection as a value for a parameter. For example user Select lines 2,3, and 25 . I need to do fire the insert statement 3 times and each time change the @line parameter to the next line selected. How would I do that? This is as far as I have gotten.

        protected void btn_test_Click(object sender, EventArgs e)
        {

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Insert into t_ap_line_setup  (line,date) values (@line,getdate())";
            //string strLines = "";

            // cmd.Parameters.Add("@line", SqlDbType.Int).Value = Convert.ToInt32(strLines);

            cmd.Connection = this.sqlConnection1;
            this.sqlConnection1.Open();

            for (int i = 0; i < lines_list.Items.Count; i++)
            {
                if (lines_list.Items[i].Selected)
                {
                    cmd.Parameters.Add("@line", SqlDbType.Int).Value = Convert.ToInt32(lines_list.Items[i].Text);
                    cmd.ExecuteNonQuery();
                }
            }
            this.sqlConnection1.Close();
        }

One more thing with the above logic I get two inserts and it only works with one selection. Could someone point me in the right direction?

score 0 · Accepted Answer · edited May 23 '17 at 12:03

0

You can use a loop:

var selectedLines = CheckBoxList1.Items.Cast<ListItem>()
             .Where(li => li.Selected)
             .Select(li => li.Text);
using (var con = new SqlConnection(connectionString))
using (var cmd = new SqlCommand("Insert into t_ap_line_setup  (line,date) values (@line,getdate())", con))
{
    con.Open();
    foreach (string line in selectedLines)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@line", int.Parse(line));
        cmd.ExecuteNonQuery();
    }
}

Note that i have used the using-statement to ensure that the connection gets disposed(closed) as soon as i'm finished with it(even in case of exception). That is better than using a "global" connection(i hope it is not static). All the more in ASP.NET.

edited May 23 '17 at 12:03

Community

1
1

answered Dec 04 '12 at 23:17

Tim Schmelter

450,073
74
686
939

I got this error : Error 1 'System.Web.UI.WebControls.ListItemCollection' does not contain a definition for 'Cast' and the best extension method overload – briskovich Dec 04 '12 at 23:28
If you're on .NET 2 replace the Linq query with a simple loop(as in your original code). But maybe you just have to add `using System.Linq;`. – Tim Schmelter Dec 04 '12 at 23:29
Is there a way to get the values without using cast? – briskovich Dec 04 '12 at 23:57
Yes, just use a loop like you did above. – Tim Schmelter Dec 05 '12 at 07:48

Hiral · Answer 2 · 2012-12-05T08:58:40.453

It is better to use bulk insert instead of inserting each record.

I will send you code here.

create one datatable say dtDetail which is having same fields in t_ap_line_setup table. Loop through checkboxlist and fill dtDetail datatable with values.

// Code for bulk insert
if (dtDetail.Rows.Count > 0)
{
    string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    SqlBulkCopy bulk = new SqlBulkCopy(connectionString);
    bulk.DestinationTableName = "t_ap_line_setup";
    bulk.WriteToServer(dtDetail);
}

Advantage of above code is that it will reduce sql server trip.Increase productivity.

Fire separate insert for each checkboxlist selection.

2 Answers2