Using for loop to delete data in database

Question

I want to delete every item in the array from the database.

string[] ids = dt.AsEnumerable()
    .Select(row => row["ProductID"].ToString())
    .ToArray();

for (int i = 0; i <= ids.Length; i++) {
    string val = ids[i];
    MySqlCommand cmd1 = new MySqlCommand();
    cmd1.CommandText = "Delete from tblindividualproduct where ProductID = @p1";
    cmd1.Parameters.AddWithValue("@p1", val);
}

When I run this the line

string val = ids[i];

gives me an error which says:

Index was outside the bounds of the array.

What's wrong with this?

This is my whole code UPDATED

string connString = "Server=192.168.1.100;Database=product;Uid=newuser;Pwd=password";
        MySqlConnection conn = new MySqlConnection(connString);

        string[] ids = dt.AsEnumerable()
                                .Select(row => row["ProductID"].ToString())
                                .ToArray();
        try
        {
            MySqlCommand cmd1 = new MySqlCommand();
            conn.Open();
            cmd1.CommandText = "Delete from tblindividualproduct where ProductID = @p1";
            cmd1.Parameters.AddWithValue("@p1", "");
            for (int i = 0; i < ids.Length; i++)
            {
                string val = ids[i];
                cmd1.Parameters[0].Value = val;
                cmd1.ExecuteNonQuery();
            }
            MessageBox.Show("Checkout Successful");
        }

Steve · Accepted Answer · 2014-03-17 20:14:52Z

2

Arrays in .NET are zero based and thus the valid indexes go from zero to length - 1.
You should change your code to

   for (int i = 0; i < ids.Length; i++)

As pointed by other answer you loop also fails to call cmd1.ExecuteNonQuery and it seems that you don't have associated a connection to the MySqlCommand (thus it will not work at all).

An interesting variation on your code could be to create a single string with all of your commands and submit the command just one time.

Beware that this is not recommended unless you are absolutely sure that your ID are just numbers and not coming from user input

StringBuilder sb = new StringBuilder();
for (int i = 0; i <= ids.Length; i++) 
   sb.AppendFormat("Delete from tblindividualproduct where ProductID = {0};", ids[i]);

MySqlCommand cmd1 = new MySqlCommand();
cmd1.CommandText = sb.ToString();
cmd1.Connection = connection;
cmd1.ExecuteNonQuery();

edited Mar 17, 2014 at 20:14

answered Mar 17, 2014 at 19:58

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Comments

p.s.w.g · Accepted Answer · 2014-03-17 20:53:30Z

1

Array indexes go from 0 up to Length - 1, so you need to stop the loop before i == ids.Length. Try replacing the <= with <. Also, don't forget to call ExecuteNonQuery to execute your command.

for (int i = 0; i < ids.Length; i++) {
    string val = ids[i];
    MySqlCommand cmd1 = new MySqlCommand(conn);
    cmd1.CommandText = "Delete from tblindividualproduct where ProductID = @p1";
    cmd1.Connection = conn;
    cmd1.Parameters.AddWithValue("@p1", val);
    cmd1.ExecuteNonQuery();
}

You can also set up the command outside of the loop and only set the parameter and execute the command inside the loop:

MySqlCommand cmd1 = new MySqlCommand(conn);
cmd1.CommandText = "Delete from tblindividualproduct where ProductID = @p1";
cmd1.Connection = conn;
cmd1.Parameters.AddWithValue("@p1", "");
for (int i = 0; i < ids.Length; i++) {
    string val = ids[i];
    cmd1.Parameters[0].Value = val;
    cmd1.ExecuteNonQuery();
}

edited Mar 17, 2014 at 20:53

answered Mar 17, 2014 at 19:59

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

4 Comments

Mix Austria Over a year ago

i got error on cmd1.ExecuteNonQuery(); it says Connection must be valid and open but i just put conn.Open() right before the for loop

p.s.w.g Over a year ago

@Austria make sure you assign a valid connection to the command, usually done through the constructor of the command.

Mix Austria Over a year ago

i just updated my whole code written above. Can you spot the wrong?

p.s.w.g Over a year ago

@MixAustria I had to step away. You've already accepted Steve's answer, but I've updated my for completeness.

score 1 · Accepted Answer · 2014-03-17 21:13:37Z

1

This will be much more efficient as there's only one call to the DB, plus there's no need anymore to iterate through yours ids collection.

string[] ids = dt.AsEnumerable()
    .Select(row => row["ProductID"].ToString())
    .ToArray();

MySqlCommand cmd1 = new MySqlCommand();
cmd1.CommandText = "Delete from tblindividualproduct where ProductID IN (" + String.Join(",", ids) + ")";

edited Mar 17, 2014 at 21:13

answered Mar 17, 2014 at 20:17

user1413338

2 Comments

Steve Over a year ago

Wrong, the parameter @p1 is considered a string and your command will be equal to IN('1,2,3,4') and no record will be deleted

Steve Over a year ago

Yes, better now, however I prefer to avoid IN because I am always not sure if MySql uses indexes in this scenario.

user2579857 · Accepted Answer · 2014-03-17 20:00:23Z

0

Your index goes from 0 to your length -1 like this:

tring[] ids = dt.AsEnumerable()
                            .Select(row => row["ProductID"].ToString())
                            .ToArray();

    for (int i = 0; i < ids.Length; i++) {
        string val = ids[i];
        MySqlCommand cmd1 = new MySqlCommand();
        cmd1.CommandText = "Delete from tblindividualproduct where ProductID = @p1";
        cmd1.Parameters.AddWithValue("@p1", val);
    }

And Index was outside the bounds of the array. means that your accessed an element that does not exist with that index.

answered Mar 17, 2014 at 20:00

user2579857

Collectives™ on Stack Overflow

Using for loop to delete data in database

4 Answers 4

Comments

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related