listbox values to database

Question

I have two list boxes. I am able to add values to one listbox through a textbox. From this listbox i add the desired values to listbox2 by clicking the add button.

Now i am not able to get how to add the values from the second listbox to a database table called Machines. these values get added to different rows.

Listbox2

PC1 

PC2

PC3

and add these to database table Machines

Please help me out

This is the code for passing values from one listbox to other...

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Data.SqlClient;
using System.IO;

namespace WebApplication3
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        ArrayList lasset = new ArrayList();
        ArrayList lsubordinate = new ArrayList();
        static ArrayList UpdateList = new ArrayList();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void ListBox2_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
        //add one by one//
        protected void Button4_Click(object sender, EventArgs e)
        {
            if (ListBox1.SelectedIndex >= 0)
            {
                for (int i = 0; i < ListBox1.Items.Count; i++)
                {
                    if (ListBox1.Items[i].Selected)
                    {
                        if (!lasset.Contains(ListBox1.Items[i]))
                        {
                            lasset.Add(ListBox1.Items[i]);
                        }
                    }
                }
                for (int i = 0; i < lasset.Count; i++)
                {
                    if (!ListBox2.Items.Contains(((ListItem)lasset[i])))
                    {
                        ListBox2.Items.Add(((ListItem)lasset[i]));
                    }
                    ListBox1.Items.Remove(((ListItem)lasset[i]));
                }
            }
        }
        //Add all
        protected void Button5_Click(object sender, EventArgs e)
        {
            while (ListBox1.Items.Count != 0)
            {
                for (int i = 0; i < ListBox1.Items.Count; i++)
                {
                    if (!lasset.Contains(ListBox1.Items[i]))
                    {
                        lasset.Add(ListBox1.Items[i]);
                    }
                }
                for (int i = 0; i < lasset.Count; i++)
                {
                    if (!ListBox2.Items.Contains(((ListItem)lasset[i])))
                    {
                        ListBox2.Items.Add(((ListItem)lasset[i]));
                    }
                    ListBox1.Items.Remove(((ListItem)lasset[i]));
                }
            }
                    }
        //remove from listbox2 and add to listbox1//
        protected void Button6_Click(object sender, EventArgs e)
        {
            if (ListBox2.SelectedItem != null)
            {
                for (int i = 0; i < ListBox2.Items.Count; i++)
                {
                    if (ListBox2.Items[i].Selected)
                    {
                        if (!lsubordinate.Contains(ListBox2.Items[i]))
                        {
                            lsubordinate.Add(ListBox2.Items[i]);
                        }
                    }
                }
                for (int i = 0; i < lsubordinate.Count; i++)
                {
                    if (!ListBox1.Items.Contains(((ListItem)lsubordinate[i])))
                    {
                        ListBox1.Items.Add(((ListItem)lsubordinate[i]));
                    }
                    ListBox2.Items.Remove(((ListItem)lsubordinate[i]));
                    UpdateList.Add(lsubordinate[i]);
                }
            }

        }
        //remove all
        protected void Button7_Click(object sender, EventArgs e)
        {
            while (ListBox2.Items.Count != 0)
            {
                for (int i = 0; i < ListBox2.Items.Count; i++)
                {
                    if (!lsubordinate.Contains(ListBox2.Items[i]))
                    {
                        lsubordinate.Add(ListBox2.Items[i]);
                    }
                }
                for (int i = 0; i < lsubordinate.Count; i++)
                {
                    if (!ListBox1.Items.Contains(((ListItem)lsubordinate[i])))
                    {
                        ListBox1.Items.Add(((ListItem)lsubordinate[i]));
                    }
                    ListBox2.Items.Remove(((ListItem)lsubordinate[i]));
                    UpdateList.Add(lsubordinate[i]);
                }
            }
        }

Answer 1

You supplied this code in your comment.

myConn.Open();
OleDbCommand dataCommand = new OleDbCommand();
if (ListBox2.Items.Count > 0) {
    foreach (ListItem i in ListBox2.Items) {
        insertContractCmd = ("insert into table column1) values ('"
                            + ListBox2.Items
                            + "')", myConn);
        }
    }
    myConn.Open();
    dataCommand.ExecuteNonQuery();
}

I see three problems with the code:

First, a parenthesis mismatch in the insert command. Change ("insert into table column1) to "insert into table (column1)

Second, you open the connection twice. Lose the second myConn.Open();

Third, and this is the biggest problem - you are trying to concatenate a string with the entire list of items. What you should do is concatenate each item separately, like this:

// note: this is a bad example, use the next one instead
insertContractCmd = "insert into table (column1) values (";
foreach (ListItem item in ListBox2.Items) {
    insertContractCmd = insertContractCmd + "'" + item.Text + "'";
}
insertContractCmd = insertContractCmd + ")";

Now you only have to deal with one small problem - this code is bad because it causes a lot of string concatenation which is inefficient and consumes a lot of memory. You should use a StringBuilder instead:

StringBuilder commandBuilder = new StringBuilder("insert into table (column1) values (");
foreach (ListItem item in ListBox2.Items) {
    commandBuilder.AppendFormat("'{0}'", item.Text);
}
commandBuilder.Append(")");
insertContractCmd = commandBuilder.ToString();

Note: I also used AppendFormat for the same efficiency reasons. You could also use commandBuilder.Append("'" + item.Text + "'"); to the same effect.

Hope that helps, configurator.