PostgreSQL Function doesn't Exist in C# (Error: 42883)

Question

I am creating a winforms interface in which I am connecting my PostgreSQL database, in order to select, update and delete my data. So far, I've created the "Insert" & "Select" functions and I am proceeding with the "Update" one. I've created the following function on PostgreSQL,

PostgreSQL Update Function

create function ka_update
(
    _cus_name varchar,
    _cus_phone varchar,
    _cus_vat varchar,
    _cus_street varchar,
    _cus_street_num varchar,
    _cus_city varchar,
    _cus_zip varchar,
    _cus_email varchar,
    _cus_id integer
)returns int as
$$
begin
    update customer
    set
        cus_name = _cus_name,
        cus_phone = _cus_phone,
        cus_vat = _cus_vat,
        cus_street = _cus_street,
        cus_street_num = _cus_street_num,
        cus_city = _cus_city,
        cus_zip = _cus_zip,
        cus_email = _cus_email
    where cus_id=_cus_id;
    if found then
        return 1;
    else
        return 0;
    end if;
end
$$
language plpgsql

and then I am proceeding with my C# code,

C# Script to Use the Update Function from PostgreSQL

private NpgsqlConnection conn;
private DataTable dt;
private NpgsqlCommand cmd;
private string sql = null;
private DataGridViewRow r;


private void button2_Click(object sender, EventArgs e)
        {
            if (r == null)
            {
                MessageBox.Show("Please choose a customer to update information", "Oops", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            try
            {
                conn.Open();
                sql = @"select * from ka_update(:_cus_name,:_cus_phone,:_cus_vat,:_cus_street,:_cus_street_num,:_cus_city,:_cus_zip,:_cus_email,:_cus_id)";
                cmd = new NpgsqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("_cus_name", textBox1.Text);
                cmd.Parameters.AddWithValue("_cus_phone", textBox2.Text);
                cmd.Parameters.AddWithValue("_cus_vat", textBox3.Text);
                cmd.Parameters.AddWithValue("_cus_street", textBox4.Text);
                cmd.Parameters.AddWithValue("_cus_street_num", textBox5.Text);
                cmd.Parameters.AddWithValue("_cus_city", textBox6.Text);
                cmd.Parameters.AddWithValue("_cus_zip", textBox7.Text);
                cmd.Parameters.AddWithValue("_cus_email", textBox8.Text);
                cmd.Parameters.AddWithValue("_cus_id", r.Cells["_cus_id"].Value.ToString());
                
                if ((int)cmd.ExecuteScalar() == 1)
                {
                    conn.Close();
                    MessageBox.Show("Updated a new customer successfully", "Well Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    button4.PerformClick(); //If updated successfully then load all customers again
                    //reset textbox components
                    textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = textBox5.Text = textBox6.Text = textBox7.Text = textBox8.Text = null;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message, "UPDATE FAIL!!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
            }



private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                r = dataGridView1.Rows[e.RowIndex];
                textBox1.Text = r.Cells["_cus_name"].Value.ToString();
                textBox2.Text = r.Cells["_cus_phone"].Value.ToString();
                textBox3.Text = r.Cells["_cus_vat"].Value.ToString();
                textBox4.Text = r.Cells["_cus_street"].Value.ToString();
                textBox5.Text = r.Cells["_cus_street_num"].Value.ToString();
                textBox6.Text = r.Cells["_cus_city"].Value.ToString();
                textBox7.Text = r.Cells["_cus_zip"].Value.ToString();
                textBox8.Text = r.Cells["_cus_email"].Value.ToString();

            }
        }

I am receiving the following error

I am trying to figure out, what I am doing wrong with the writing of the function but I've zero clue so far. Any ideas???

Why are you converting the last argument to a string? It is expected to be an integer according the the function declaration. — jjanes
– jjanes, Commented Aug 17, 2021 at 18:21
Because I am receiving an CLR type error in which DataGridViewTextBox isn't natively supported by Ngsql. — Vel_More
– Vel_More, Commented Aug 17, 2021 at 18:43
If you can't convert directly to an int, try converting to a string first, then that to an int. (I'm not a CLR programmer, so I won't propose the code to do that). — jjanes
– jjanes, Commented Aug 17, 2021 at 20:25
Thank you very much, as well as, DanielZhang-MSFT for his answer below. — Vel_More
– Vel_More, Commented Aug 18, 2021 at 5:44

DanielZhang-MSFT · Accepted Answer · 2021-08-18 01:37:23Z

1

The error indicates that the parameter types do not match. jjanes has also pointed out that the last parameter is int

If your _cus_id column is of type int, you can use the Convert.ToInt32 method to directly convert it to type int.

cmd.Parameters.AddWithValue("_cus_id", Convert.ToInt32(r.Cells["_cus_id"].Value));

answered Aug 18, 2021 at 1:37

DanielZhang-MSFT

1744 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL Function doesn't Exist in C# (Error: 42883)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related