0

I am trying to add code that will delete from 2 tables in access db file. Sometimes one of them will work and the other wont then when I try it another way it will do the opposite. So in the end only 1 of the 2 works.

Here is my code I hope someone can spot something I did wrong.

try
{
    Conn.Open();
    OleDbCommand command = new OleDbCommand();
    command.Connection = Conn;
    command.CommandText = "DELETE FROM TBLNAME WHERE name =@name";
    command.Parameters.AddWithValue("@name", lvlist.SelectedItems[0].Text);
    command.ExecuteNonQuery();

    command.CommandText = "DELETE from TBLNAME WHERE cb_listName =@listname";
    command.Parameters.AddWithValue("@listname", lvlist.SelectedItems[0].Text);
    command.ExecuteNonQuery();
    Conn.Close();
}
catch (Exception ex)
{
    MessageBox.Show("Error  " + ex);
}
Improve this question
3
  • 1
    Don't you need to clear the parameters before executing the next command? Using "command.Parameters.Clear();" Commented Jun 28, 2017 at 18:33
  • Best to create a second, fresh OleDbCommand instance. Commented Jun 28, 2017 at 18:35
  • Don't know if this is a good practice but you seem to be using the same value for each statement, so you could just change the parameter to @name in the second delete statement and remove the second Parameter.AddwithValue so that each command text uses the same parameter and value against the two different commandtext's Commented Jun 28, 2017 at 18:46

2 Answers 2

Reset to default
3

You should use different Command instances, one for each command you want to execute. If you do not do that then you need to clear the parameters. This is because parameters in OleDb queries are positional and not named. This means that when you add the 2nd parameter in the 2nd query the first parameter is used because it is first in the list.

using(var connection = new OleDbConnection("connection string here"))
{
    connection.Open();
    using(var command = new OleDbCommand("DELETE FROM TBLNAME WHERE name = @name", connection))
    {
        cmd.Parameters.Add(new OleDbParameter("@name", OleDbType.VarChar, 50)).Value = lvlist.SelectedItems[0].Text;
        command.ExecuteNonQuery();
    }

    using(var command = new OleDbCommand("DELETE from TBLNAME WHERE cb_listName = @listname", connection))
    {
        cmd.Parameters.Add(new OleDbParameter("@listname", OleDbType.VarChar, 50)).Value = lvlist.SelectedItems[0].Text;
        command.ExecuteNonQuery();
    }
}

Also you should:

  1. Use using blocks to ensure connections are closed after use. Do not try to create class scoped, or even worse global, connection instances.
  2. You should also specify the db type for your parameters and do not use AddwithValue.
  3. When possible also specify the length for your db types, in the above this is possible if you have a varchar type. note I toke a guess at your schema length for these columns

Finally, just a note on general best practices, do not add catch blocks that do nothing useful with the exception. At least log the type, message, and the stack trace and then repeat this recursively for each inner exception found in property InnerException. This useful information can help you figure out exactly why an exception occurred.

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

11 Comments

Thanks. for helping I am trying it now. I have the connection string predefined at top of form so should I define it each time? or is that ok?
I'm getting a error saying there needs to be a available and open connection and that the state is closed.
@Don - If you look at the code I provided the 1st line creates the connection and the 2nd line opens it. This connection instance is then passed to the constructor of each OleDbCommand instance.
I almost have it I am getting a error on the second executenonquery line saying no name given for one or more parameters. I am going to recopy your code although there is no cmd anywhere.
it would not let me paste what I have
|
1

Use two different OleDbCommand objects.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.