Executing PostgreSQL update in c#

Question

im currently working with my teammates on a project concerning a connection between c# and a PostgreSQL database. We've had no problem on reading the database through the program , but when it came to Updating it we've had an issue. In our situation there is a table with 2 columns, the table is named acted and the columns are named actor_id and movie_id. We're using the Npgsql method to connect the database to our program and even though there is no error, every time we try to update a value on a column , nothing actually happens and the value remains the same.

This i the method that we are using

        public List<string> PostgreSQLtest5()   //endolse pros tin postgresql meros 1
    {

        String actcolumn = ??????;
        String actnewvalue =  ??????;
        String actoldvalue =  ??????;

        try
        {
            string connstring = "Server=127.0.0.1; Port=5432; User Id=postgres; Password=72677267; Database=imdb;";
            NpgsqlConnection connection = new NpgsqlConnection(connstring);
            connection.Open();
            NpgsqlCommand command = new NpgsqlCommand("UPDATE acted SET '"+actcolumn+"' = '"+actnewvalue+"' WHERE '"+actoldvalue+"'", connection);   
            NpgsqlDataReader dataReader = command.ExecuteReader();
            connection.Close();
            return dataItems;
        }
        catch (Exception msg)
        {
            MessageBox.Show(msg.ToString());
            throw;
        }
    }

We have 3 textboxes in another form named acted. What do we have to write in the question marks in these 3 variables to get the input of the textboxes in here, and make it work ?

Can you please provide the following: the schema of the tables, Sample data for these tables and finally the expected output? wf89n — Mark Kram, Sep 19 '18 at 23:02
@Mark Here's a screenshot of the requested data https://ibb.co/nMqX1e. The final purpose is to be able to update and read the content of the database with a program written in C#. We've managed to complete the segment that does read the database and it works , but we have a problem on the update function. Since we can't move the requested data that the user asked to update from the Input form to the form that u see above in my post. — wf89n, Sep 20 '18 at 10:18
Can you post the data from your link in your original question? — Mark Kram, Sep 20 '18 at 12:26

score 0 · Answer 1 · answered Sep 21 '18 at 19:03

If you look at your SQL:

"UPDATE acted SET '"+actcolumn+"' = '"+actnewvalue+"' WHERE '"+actoldvalue+"'"

This renders to:

UPDATE acted SET 'some_column' = 'some_value' WHERE 'some_condition'

Which, with the single quotes means you are using literal values. 'some_column' is not a column -- it's a static value, and in this context it's meaningless and should throw an error.

What you probably meant is this:

UPDATE acted SET some_column = 'some_value' WHERE <some_condition>

Which in C# terms would be:

NpgsqlCommand command = new NpgsqlCommand("UPDATE acted SET "+actcolumn+" = '"+
    actnewvalue+"' WHERE "+actoldvalue+"", connection);

Or perhaps better written as:

NpgsqlCommand command = new NpgsqlCommand(string.Format(
    "UPDATE acted SET {0} = '{1}' WHERE {2}",
    actcolumn, actnewvalue, actoldvalue), connection);

Or (speculating a little here):

NpgsqlCommand command = new NpgsqlCommand(string.Format(
    "UPDATE acted SET {0} = '{1}' WHERE {0} = '{2}'",
    actcolumn, actnewvalue, actoldvalue), connection);

And even better re-written to use parameters.

Check out this post for an example of how to bulletproof this a little:

https://stackoverflow.com/a/52449390/1278553

Mark Kram · Answer 2 · 2018-09-19T23:04:06.947

-1

Try this instead:

NpgsqlCommand command = new NpgsqlCommand("UPDATE acted SET "+actcolumn+" = '"+actnewvalue+"' WHERE "+actoldvalue+", connection);

edited Sep 19 '18 at 23:04

answered Sep 19 '18 at 14:32

Mark Kram

5,672
7
51
70

It doesn't, but our current problem is that we cannot retrieve the data from the other form (named acted). What do we have to write in the question marks ? – wf89n Sep 19 '18 at 14:37

Executing PostgreSQL update in c#

2 Answers2