How to delete records from a table, postgresql, C #

Question

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.

C#

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 11-Aug-20 0:26am

Member 14911825

Updated 11-Aug-20 1:26am

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Chris Copeland · Answer 1 · 2020-08-11T01:25:00

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?