Delete data from sql database not working

Question

when you select items from the listbox, you want to delete selecteditems. Why doesnt it work when selected data removed from database? I must have missed something. I got error message No mapping exists from object type.

This is a method parameter:

  IsDelete = _dinnerRemover.RemoveDinners(lstDinner.SelectedItems);

This class is to delete data from database

public bool RemoveDinners(dynamic dinnerItems)
{
    Dinners = new List<FoodInformation>();
    using (var sqlConn = new SqlConnection(_sqlConnectionString))
    {

        const string sqlQuery = "delete from DinnerTemplates where Dinner = @dinner";

        using (var command = new SqlCommand(sqlQuery, sqlConn))
        {
            try
            {
                //command.CommandType = CommandType.StoredProcedure;
                //command.CommandText = "sp_dinner";
                foreach (var item in dinnerItems)
                {
                    command.CommandType = CommandType.Text;
                    command.Parameters.AddWithValue("@dinner", item);
                    command.ExecuteNonQuery();

                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                sqlConn.Close();
            }
        }
    }

    return Dinners;
}

no it is not string. parameter is dynamic type. what is data type parameter for listbox.selectedItems? — user1358072
– user1358072, Commented Jun 19, 2013 at 12:04
The error message tells you exactly what the problem is and it doesn't appear to have anything to do with the code you posted. — Dan Bracuk
– Dan Bracuk, Commented Jun 19, 2013 at 12:04
see if inserting the exact string to delete works. Or adding .ToString() — Jonesopolis
– Jonesopolis, Commented Jun 19, 2013 at 12:08
i just edited my post. Look at the parameter. I have tried List<string> for the parameter. it doesnt work — user1358072
– user1358072, Commented Jun 19, 2013 at 12:11

Samuel Neff · Accepted Answer · 2013-06-19 12:27:52Z

2

If dinnerItems is a list of strings then say that, don't use dynamic unless you absolutely have to.

To delete a bunch of items, issue one sql query with an IN clause. Don't issue lots of individual queries.

Try this:

    public int RemoveDinners(List<string> dinnerItems)
    {
        using (var sqlConn = new SqlConnection(_sqlConnectionString))
        {

            const string sqlQuery = "delete from DinnerTemplates where Dinner in ({0})";

            using (var command = new SqlCommand())
            {

                var paramNames = new string[dinnerItems.Count];

                int i = 0;
                foreach (string item in dinnerItems)
                {
                    string paramName = "@Dinner" + i;
                    command.Parameters.AddWithValue(paramName, item);
                    paramNames[i] = paramName;
                    i += 1;
                }

                command.CommandText = String.Format(sqlQuery, String.Join(",", paramNames));
                command.Connection = sqlConn;
                command.CommandType = CommandType.Text;

                sqlConn.Open();

                return command.ExecuteNonQuery();
            }
        }
    }

answered Jun 19, 2013 at 12:27

Samuel Neff

75.3k18 gold badges144 silver badges186 bronze badges

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

1 Comment

user1358072 Over a year ago

Wow it is working like a bomb. Thank you very very much! I am gonna mark answer :)

Mike Perrenoud · Accepted Answer · 2013-06-19 12:10:27Z

0

You have to bear in mind that you kind of left out some really relevant code, like what is a DinnerItem, since you're getting the error on a line related to its type.

However, the reason you're getting that error is because item can't be marshaled to a type of something like string or int.

That's probably because item is likely a custom class. One option would be to override the ToString method of the class:

public override string ToString() {
    // return some property value, or set of property values
    // strung together here.
}

another option would be to send in the actual Property you want off of item when issuing AddWithValue.

edited Jun 19, 2013 at 12:10

answered Jun 19, 2013 at 12:04

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Comments

Rohit Singh · Accepted Answer · 2013-06-19 12:11:06Z

0

You need to define SqlDbType for command's parameter.

answered Jun 19, 2013 at 12:11

Rohit Singh

1062 bronze badges

Comments

Brian lee · Accepted Answer · 2013-06-19 13:22:09Z

0

don't use dynamic type,use string.. if i were you,i would rather

IsDelete = _dinnerRemover.RemoveDinners(lstDinner.SelectedItems.ToString());

change the parameter to :

public bool RemoveDinners(string dinnerItems)

and the query to :

const string sqlQuery = "delete from DinnerTemplates where Dinner = dinnerItems";

answered Jun 19, 2013 at 13:22

Brian lee

3561 gold badge4 silver badges12 bronze badges

Collectives™ on Stack Overflow

Delete data from sql database not working

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related