Insert listbox contents to database

I am creating an application with several textboxes and a few listboxes. The first listbox is getting a list of games from a SQL table which contains ID, Name and Type columns. The listbox is display and value for the listbox is the Name. When clicking a button I can move the name from the first listbox to the second. Now I want to be able to write the ID of the items in the second listbox to another SQL table when saving.

The below code executes fine and inserts to the promo table. But when I try either adding a second insert statement (INSERT INTO promo_games(promo_ID, Game_ID) VALUES(@PID, @GameID)) to newPromo or creating a new string with a separate SQL insert for the listbox is when I run into problems. I am just not sure how to handle this to insert the multiple items and to take the ID from the item.

string conn = System.Configuration.ConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;

SqlConnection misc = new SqlConnection(conn);

string newPromo = "INSERT INTO admin.promo (ID, masterId, Master, name, ...more text fields)"

"VALUES(@ID, @MasterID,@Master, @Name,...more text fields)";

SqlCommand insPromo = new SqlCommand(newPromo, misc);

insPromo.Parameters.AddWithValue("@ID", txtID.Text);

insPromo.Parameters.AddWithValue("@MasterID", txtMasterID.Text);

insPromo.Parameters.AddWithValue("@Master", chkMaster.Checked);

insPromo.Parameters.AddWithValue("@Name", txtName.Text);

...

misc.Open();

insPromo.ExecuteNonQuery();

misc.Close();

Here's an example for you:

private void InsertRecords(StringCollection sc){
const string sqlStatement = "INSERT INTO Employee (Name) VALUES (@param1)";
foreach (string item in sc){
using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
using(SqlCommand cmd = new SqlCommand(sqlStatement ,connection)){
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@param1", item)
cmd.ExecuteNonQuery();
}
}
}
//bind GridView after
BindGrid(sc);
}
private void BindGrid(StringCollection sc){
string[] strArray = new string[sc.Count];
sc.CopyTo(strArray,0);
var parms = strArray.Select((s, i) => "@param1" + i.ToString()).ToArray();
var inclause = string.Join(",", parms);
string sqlStatement = "SELECT * FROM Employee WHERE Name IN ({0})";
using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
using(SqlCommand cmd = new SqlCommand(string.Format(sqlStatement, inclause),connection)){
for (var i = 0; i < valuearray.Length; i++)
{
cmd.Parameters.AddWithValue(parms[i], strArray[i]);
}
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dt);
if (dt.Rows.Count > 0) { //check if the query returns any data
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//No records found
}
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
StringCollection sc = new StringCollection();
foreach (ListItem item in ListBox1.Items)
{
if (item.Selected){
sc.Add(item.Text);
}
}
InsertRecords(sc);
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Vincent Maverick Durano8y ago
Here's an example for you:
private void InsertRecords(StringCollection sc){
    const string sqlStatement = "INSERT INTO Employee (Name) VALUES (@param1)";

    foreach (string item in sc){
            using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
                using(SqlCommand cmd = new SqlCommand(sqlStatement ,connection)){
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("@param1", item)
                        cmd.ExecuteNonQuery();
                }
        }
        }

    //bind GridView after
    BindGrid(sc);

}

private void BindGrid(StringCollection sc){
    string[] strArray = new string[sc.Count];
    sc.CopyTo(strArray,0);

    var parms = strArray.Select((s, i) => "@param1" + i.ToString()).ToArray();
    var inclause = string.Join(",", parms);

    string sqlStatement = "SELECT * FROM Employee WHERE Name IN ({0})";

        using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
            using(SqlCommand cmd = new SqlCommand(string.Format(sqlStatement, inclause),connection)){

                for (var i = 0; i < valuearray.Length; i++)
                {
                    cmd.Parameters.AddWithValue(parms[i], strArray[i]);
                }
                    DataTable dt = new DataTable();
                    SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    ad.Fill(dt);

                    if (dt.Rows.Count > 0) { //check if the query returns any data
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                    }
                    else
                    {
                        //No records found
                    }
            }

        }
}

protected void Button1_Click(object sender, EventArgs e)
{
        StringCollection sc = new StringCollection();
    foreach (ListItem item in ListBox1.Items)
    {
        if (item.Selected){
            sc.Add(item.Text);
        }
    }

       InsertRecords(sc);
}