Click here to Skip to main content
14,691,039 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I am trying to delete records from a table, but after implementing the following code, the records are not deleted.
If I add an item to the list and delete it in the same session, everything is fine. The problem arises when I add items to the list, close the program and after restarting I want to remove the item. Then it is impossible.
MessageBox.Show(item2); - Shows the item I want to delete

Please help me how to do this.



private void button1_Click(object sender, EventArgs e) //Aktualizuj
       {

             foreach (var item in checkedListBox1.CheckedItems)
            {
                //Dodanie do archiwalnych zadań
                var cs2 = "Host=localhost;Username=postgres;Password=root;Database=Dziennik";

                var con2 = new NpgsqlConnection(cs2);
                con2.Open();
                var sql = "INSERT INTO zadaniaarchiwalne(zadanie_archiwalne) VALUES(@zadanie)";
                var cmd = new NpgsqlCommand(sql, con2);
                cmd.Parameters.AddWithValue("zadanie", item);
                cmd.Prepare();
                cmd.ExecuteNonQuery();

            }




            foreach (var item2 in checkedListBox1.CheckedItems.OfType<string>().ToArray())
            {
                var cs = "Host=localhost;Username=postgres;Password=root;Database=Dziennik";
                var con = new NpgsqlConnection(cs);
                con.Open();
                MessageBox.Show(item2);
                var sql2 = "DELETE FROM zadania WHERE zadanie=(@zaadanie)";
                var cmd2 = new NpgsqlCommand(sql2, con);
                cmd2.Parameters.AddWithValue("zaadanie", item2);
                cmd2.Prepare();
                cmd2.ExecuteNonQuery();
                con.Close();
                checkedListBox1.Items.Remove(item2);
            }


What I have tried:

I am trying to write a task list application that connects to a PostgreSQL database
Posted
Updated 11-Aug-20 2:26am
v2
Comments
Richard MacCutchan 11-Aug-20 6:41am
   
You have two calls to ExecuteNonQuery, but in both cases you do not check the return value to see if the command succeeded.
Garth J Lancaster 11-Aug-20 6:44am
   
Not quite sure what "Values ​​added to the list before closing the program are removed, while those that were added earlier and read from the database are not deleted." means. I see you have
MessageBox.Show(item2);
.. does this show the item2's you intend to delete - maybe use Improve question and add a bit more detail
Member 14911825 11-Aug-20 6:59am
   
If I add an item to the list and delete it in the same session, everything is fine. The problem arises when I add items to the list, close the program and after restarting I want to remove the item. Then it is impossible.
MessageBox.Show(item2); - Shows the item I want to delete
Luc Pattyn 11-Aug-20 8:05am
   
Your formulation of the problem is quite unclear.

1. You display a number of items, please explain what they are. You work with checked items, what are those? and while removing, you remove the items form the display. What does that mean?

2. It is unclear which items get shown, and which gets checked. Especially how that differs between your first and second run of the program.

3. It would help if you described an actual scenario, such as: initial list contains A,B,C,D with B and C checked, then I ... etc.

4. You show a single method, which first adds items to the database and then removes the same items. What purpose would that serve? and how would that support your statment about deletion in a second run, as there is no code shown that only tries to delete...

5. It would help if you were to show real code.

6. As Garth already mentioned, you are ignoring the return value of ExecuteNonQuery. I would suggest you:
- count and display the number of checked items
- accumulate the return values of ExecuteNonQuery while adding, and display that
- accumulate the return values of ExecuteNonQuery while attempting to delete, and display that number.
- and NOT display every single item you try to delete.
That way you should see only a few numbers, and they should all match; when they don't, you get a first hint as to what is wrong.

7. If adding such observability isn't sufficient, now is the time to start using the debugger.

8. Finally I have several comments on your coding; these probably won't affect the program behavior but improvement is welcome:
- there is no need to open and close the connection for each iteration in a foreach loop;
- code looks much nicer if you apply a using statement for objects that need being disposed of when done, such as your connection.
- I don't like your OfType<string>().ToArray() trick allowing you to remove items while still enumerating the checked items. A simple .ToList() should be sufficient; personally I do this with an extra foreach and an intermediate list, think things-to-remove-after-foreach-is-done. That takes a bit more code but is clearer.
Member 14911825 11-Aug-20 8:50am
   
Thanks for comments on my code. I corrected what you mentioned.
The problem is solved, an error occurred while reading data from the database.

1 solution

As Richard MacCutchan mentioned, you can check the value returned from the ExecuteNonQuery method to see how many rows were affected by your query. You should see a value of greater than zero in both cases if the queries were executed successfully.

Postgres can sometimes be strict on transaction control, and I can see you're not using transactions. Have you tried calling BeginTransaction()[^] to see whether this applies the changes to the database?

I don't see you closing the first connection either, so could there be a deadlock on the database caused by one connection remaining open with an uncommitted query?
   
Comments
Member 14911825 11-Aug-20 7:38am
   
Thank you for your help, the error was in the place where I was reading the data from the database.
It fetched erroneous data, namely several spaces behind the value.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900