locked
Delete using ArrayList with IN clause RRS feed

  • Question

  • User-718146471 posted

    Hi guys, I'm kind of stuck here and could use a hand. My query is kind of like this:

                    string Remove = "DELETE FROM TABLE where [Id] IN ('@Id')";
                    SqlCommand cmd3 = new SqlCommand(Remove, conn);
                    conn.Open();
                    cmd3.Parameters.AddWithValue("Id", Exceptions.ToArray());
                    cmd3.ExecuteNonQuery();
                    conn.Close();
    


    I think that this query should work but it gives me a quirky error with regard to my ArrayList:

    No mapping exists from object type to a known managed provider native type
    


    I created the value like this near the top of the class:

            public ArrayList Exceptions = new ArrayList();
    

    so what gives? Why can't I use the value of each arrayitem for my IN clause? I tested it using a set of fields and that worked but arraylist won't for some reason? Do I need to cast it differently?

    Monday, April 15, 2013 12:07 PM

Answers

  • User-718146471 posted

    I ended up doing it this way since ArrayList just doesn't seem to work:

                    string query3 = "SELECT Exception from Exceptions;";
                    SqlCommand cmd3 = new SqlCommand(query3, conn);
                    conn.Open();
                    SqlDataReader rdr3 = cmd3.ExecuteReader();
    
                    while (rdr3.Read())
                    {
                        string parameter = rdr3.GetValue(0).ToString();
                        try
                        {
                            string Exceptions = "DELETE FROM Table where [Id] = @Id";
                            SqlCommand cmd4 = new SqlCommand(Exceptions, conn2);
                            conn2.Open();
                            cmd4.Parameters.AddWithValue("Id", parameter);
                            cmd4.ExecuteNonQuery();
                            conn2.Close();
                            parameter = "";
                        }
                        catch (Exception ex)
                        {
    
                        }
                    }
                    conn.Close();
    

    I'm open to trying the ArrayList concept again if someone can figure it out. :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 15, 2013 1:16 PM

All replies

  • User-1388383071 posted

    Need to convert this array

    Monday, April 15, 2013 12:26 PM
  • User-718146471 posted

    Convert the array to what?  How?

    Monday, April 15, 2013 12:34 PM
  • User-718146471 posted

    I think I got it now:

    String[] myArr = (String[])Exceptions.ToArray(typeof(string));

    Monday, April 15, 2013 12:39 PM
  • User-718146471 posted

    Arghh, this is so frustrating. I didn't get an error but it didn't remove anything either...

    Monday, April 15, 2013 12:42 PM
  • User-1388383071 posted

    It seems you got another error ?

    Monday, April 15, 2013 12:46 PM
  • User-1388383071 posted

    The problem is Sql desnt have Array concept you trying to pass an array. thats the issue

    Monday, April 15, 2013 12:50 PM
  • User-718146471 posted

    I'm going to try using string.Join and see where that leads.

    Monday, April 15, 2013 12:54 PM
  • User-1388383071 posted

    I treid that but another error

    Monday, April 15, 2013 12:55 PM
  • User-1388383071 posted

     string parameters;

                parameters = string.Join(",", list.ToArray());
               
    I treid that//

    but error
                SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Dummy;Integrated Security=True");
               string Remove = "DELETE FROM Demo where [Id] IN (@Id)";
                SqlCommand cmd3 = new SqlCommand(Remove, conn);
                conn.Open();

                cmd3.Parameters.AddWithValue("@Id", parameters);
                cmd3.ExecuteNonQuery();
                conn.Close();

    Monday, April 15, 2013 12:56 PM
  • User-718146471 posted

    I ended up doing it this way since ArrayList just doesn't seem to work:

                    string query3 = "SELECT Exception from Exceptions;";
                    SqlCommand cmd3 = new SqlCommand(query3, conn);
                    conn.Open();
                    SqlDataReader rdr3 = cmd3.ExecuteReader();
    
                    while (rdr3.Read())
                    {
                        string parameter = rdr3.GetValue(0).ToString();
                        try
                        {
                            string Exceptions = "DELETE FROM Table where [Id] = @Id";
                            SqlCommand cmd4 = new SqlCommand(Exceptions, conn2);
                            conn2.Open();
                            cmd4.Parameters.AddWithValue("Id", parameter);
                            cmd4.ExecuteNonQuery();
                            conn2.Close();
                            parameter = "";
                        }
                        catch (Exception ex)
                        {
    
                        }
                    }
                    conn.Close();
    

    I'm open to trying the ArrayList concept again if someone can figure it out. :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 15, 2013 1:16 PM