[megglz]

I want to add multiple selected items into an array and then insert each item separately into the database.. Would the following be almost correct?

using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand sqlCommand1 = conn.CreateCommand())
    {
object[] o = new object[listBox1.Items.Count];
foreach(object o in listBox1.Items)
 string insertActivities = string.Format("INSERT into DATA (ID, ActivityName) VALUES ({0}, o)", DropDownList_EmployID.SelectedValue,);
   sqlCommand1.CommandText = insertActivities;
   sqlCommand1.CommandType = CommandType.Text;
   conn.Open();
   sqlCommand1.ExecuteNonQuery();
    conn.Close();
                }

            }

[Curtis Rutland]

First, I would use parameters rather than string building. String building leaves you open to SQL Injection attacks. Parameters escape input.

Second, what does your object array here do? You declare it but never use it. And then you enter the foreach loop, and declare another variable, this time just a plain object, with the same name. That shouldn't even work.

Third, what does your foreach loop do? You never use the object o variable. You use the string "o", but that's always going to be "o" no matter what

This post has been edited by insertAlias: 01 July 2010 - 07:07 AM

[eclipsed4utoo]

insertAlias, on 01 July 2010 - 09:06 AM, said:

First, I would use parameters rather than string building. String building leaves you open to SQL Injection attacks. Parameters escape input.

Second, what does your object array here do? You declare it but never use it. And then you enter the foreach loop, and declare another variable, this time just a plain object, with the same name. That shouldn't even work.

Third, what does your foreach loop do? You never use the object o variable. You use the string "o", but that's always going to be "o" no matter what

I think she is using the "object o" in the foreach simply as a way to loop through the items in the ListBox. She doesn't have to worry about invalid casting since it will cast to an "object".

IrishGirl, are you running into an issue? The code would seem to work.

[FlashM]

Something like that:

public partial class Form1 : Form
{

    IList<User> users = new List<User>();

    public Form1()
    {
        InitializeComponent();
        PrepareTestData();
    }

    private void PrepareTestData()
    {
        User u1 = new User { Id = 1, ActivityName = "Activity 1" };
        User u2 = new User { Id = 2, ActivityName = "Activity 2" };
        User u3 = new User { Id = 3, ActivityName = "Activity 3" };

        users.Add(u1);
        users.Add(u2);
        users.Add(u3);

        usersListBox.DataSource = users;
        usersListBox.DisplayMember = "ActivityName";
        usersListBox.ValueMember = "Id";
    }

    private void btnInsertSelected_Click(object sender, EventArgs e)
    {
        ListBox.SelectedObjectCollection selection = usersListBox.SelectedItems;

        foreach (User user in selection)
        {
            //Database insert logic here...
        }
    }
}

class User
{
    public int Id { get; set; }
    public string ActivityName { get; set; }
}

[Curtis Rutland]

eclipsed4utoo, on 01 July 2010 - 06:16 AM, said:

insertAlias, on 01 July 2010 - 09:06 AM, said:

First, I would use parameters rather than string building. String building leaves you open to SQL Injection attacks. Parameters escape input.

Second, what does your object array here do? You declare it but never use it. And then you enter the foreach loop, and declare another variable, this time just a plain object, with the same name. That shouldn't even work.

Third, what does your foreach loop do? You never use the object o variable. You use the string "o", but that's always going to be "o" no matter what

I think she is using the "object o" in the foreach simply as a way to loop through the items in the ListBox. She doesn't have to worry about invalid casting since it will cast to an "object".

IrishGirl, are you running into an issue? The code would seem to work.

See, I don't think it will. Look at this line closely:

string insertActivities = string.Format("INSERT into DATA (ID, ActivityName) VALUES ({0}, o)", DropDownList_EmployID.SelectedValue,);

It's not using the value of object o, it's using the literal string "o". So every insert would be "o". The first parameter is coming from a different control altogether, what I'm assuming is an employee id selector.

OP would need to rewrite it more like this:

string insertActivities = "INSERT into DATA (ID, ActivityName) VALUES (@ID, @ActivityName)";
sqlCommand1.CommandText = insertActivities;
sqlCommand1.Parameters.AddWithValue("@ID", DropDownList_EmployID.SelectedValue);
sqlCommand1.Parameters.AddWithValue("@ActivityName", o);

Also, to irishgirl:
Some database advice. If those are all the fields in your table, I would suggest adding a row id field. Something like an auto-increment integer field to be your primary key. It is very much worth doing.

This post has been edited by insertAlias: 01 July 2010 - 07:31 AM

[megglz]

Thanks all, sorry I never had time to run the code yet as I had to leave desk unexpectedly. Will try now.

[megglz]

Quote

A local variable named 'o' cannot be declared in this scope because it would give a different meaning to 'o', which is already used in a 'parent or current' scope to denote something else

[megglz]

Hi, this code compiles fine now but there is a problem with the SQL issue maybe as it doesnt bind to the gridview (its blank) and doesnt insert into the db. I wont be able to check this again until Monday as I will be off until then. Thanks again for all your help, I would truly be lost without this place

using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand sqlCommand1 = conn.CreateCommand())
    {
        // object[] o = new object[ListBox_Activities.Items.Count];
        foreach (object o in ListBox_Activities.SelectedValue)
        {
            string insertActivities = string.Format("INSERT into Data (ID, ActivityName) VALUES (@ID, @ActivityName)");
            sqlCommand1.CommandText = insertActivities;
            sqlCommand1.Parameters.AddWithValue("@ID", DropDownList_Employees.SelectedValue);
            sqlCommand1.Parameters.AddWithValue("@ActivityName", o);
            sqlCommand1.CommandType = CommandType.Text;
            conn.Open();
            sqlCommand1.ExecuteNonQuery();
            conn.Close();
        }
    }
}

GridView_ABC.DataBind();
GridView_Team.Visible = false;

This post has been edited by eclipsed4utoo: 01 July 2010 - 11:48 AM
Reason for edit:: fixed code formatting

[eclipsed4utoo]

You are not setting the DataSource of the GridView. You are calling DataBind but nothing is there to databind. No datasource means no data.

[megglz]

I thought it was that too, but when I'm debugging it steps on to this line

 foreach(object o in ListBox_Activities.SelectedValue) {

and then completely skips the block of code there, this is why it's showing blank and not inserting hmmm.

[Curtis Rutland]

irishgirl, on 05 July 2010 - 06:42 AM, said:

I thought it was that too, but when I'm debugging it steps on to this line

 foreach(object o in ListBox_Activities.SelectedValue) {

and then completely skips the block of code there, this is why it's showing blank and not inserting hmmm.

SelectedValue isn't a collection. It's a single object.

[megglz]

Yes, I understand this now. I got the code working with

foreach (ListItem li in ListBox_Activities.Items) 

but this obviously entered every entry into the db, not just the several selected ones. How can I do it for the several selected? If I try as

 foreach (object o in ListBox_Activities.SelectedValue)

It passes through blanks.

This post has been edited by irishgirl: 05 July 2010 - 01:08 AM

[megglz]

I found that i could do an 'if statement' to see if the value has been selected. However, it does not pick up on any selected values :s

     foreach (ListItem li in ListBox_Activities.Items) {
            if (li.Selected)
            {
//
            }
      }

[Curtis Rutland]

Try using .SelectedItems. You might have to cast them.

This post has been edited by insertAlias: 05 July 2010 - 01:25 AM

[megglz]

That property does not work for me, no matter what i try. i can only do SelectedItem. Do you have an example?