How do i cast Data explicitly to enable a write into a postgresql database

It seems to me that you are trying to pass 8 parameters to a function that only accepts 7. Payrateid is apparently not defined in the function.

public.sppayrates_updateinsert(
          'TR3C',
          'A',
          'BASIC',
          50000,
          'P',
          'NNPC',
          'Y'
      );
Where is the integer column?.I think a column is missing.

Your test insert is actually all text.  Your 'int32' values are, I believe, binary values that won't pass thru the database connection and driver as you want.  Every other database connection I know of accepts text representations of the values, not binary.

I use the same script for update and inserts.

The primary key payrateid need to have a non zero value  when a record is being updated

However for an insert it is not required as the system returns a new  ID

That was the reason I was able to send in the the 7 parameters  (through query analyser) without the ID  and the first record was written with an Id  (ie payrateid) of 1

My test insert includes 5000  which is not a text.  and the Int32 (payrateid)   goes in with zero value.
My script test for an incoming payrateid and does an update if it exist else it does an insert

below  please find the stored procedure script if this will help

CREATE OR REPLACE FUNCTION public.sppayrates_updateinsert(
    IN curgrdx text,
    IN enttypex text,
    IN entcodex text,
    IN gradeamtx numeric,
    IN filtertypex text,
    IN companycodex text,
    IN validrecordynx text,
    INOUT payrateidx integer)
  RETURNS SETOF integer AS
$BODY$
BEGIN
UPDATE payrates
SET
grade_amt = gradeamtx
where payrateid = payrateidx;
IF NOT FOUND THEN
 RETURN QUERY INSERT INTO payrates(cur_grd,ent_type,ent_code,grade_amt,filtertype,companycode,validrecordyn)
VALUES                            (curgrdx,enttypex,entcodex,gradeamtx,filtertypex,companycodex,validrecordynx) Returning payrateid;
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.sppayrates_updateinsert(text, text, text, numeric, text, text, text, integer)
  OWNER TO postgres;


Grateful for more inputs on the resolution of this problem

Olukay

Please find the complete script where the parameters are defined


     

using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
            {
                using (var command = new NpgsqlCommand("public.sppayrates_updateinsert", conn))
                {

                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;

                    NpgsqlParameter cur_grd = new NpgsqlParameter("curgrdx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(cur_grd);
                    NpgsqlParameter ent_type = new NpgsqlParameter("enttypex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(ent_type);
                    NpgsqlParameter ent_code = new NpgsqlParameter("entcodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(ent_code);
                    NpgsqlParameter grade_amt = new NpgsqlParameter("gradeamtx", NpgsqlTypes.NpgsqlDbType.Numeric) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(grade_amt);
                    NpgsqlParameter filterfield = new NpgsqlParameter("filterfieldx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(filterfield);
                    NpgsqlParameter companycode = new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(companycode);
                    NpgsqlParameter validrecordyn = new NpgsqlParameter("validrecordynx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(validrecordyn);
                    NpgsqlParameter payrateid = new NpgsqlParameter("payrateidx", NpgsqlTypes.NpgsqlDbType.Integer) { Direction = ParameterDirection.Input };
                    command.Parameters.Add(payrateid);

                    
                    companycode.Value = GridVariables.GridColumn10;
                   
                    foreach (DataRow row in dataSource.Rows)
                    {
                        if ((GridVariables.GridColumn01 != "") && (GridVariables.GridColumn02 != "") && (GridVariables.GridColumn03 != ""))
                        {
                            // insert into payrates VALUES ('TR3N','A','BASIC',50000,'P','NNPC','Y')

                            cur_grd.Value = GridVariables.GridColumn01;
                            ent_type.Value = GridVariables.GridColumn02;
                            ent_code.Value = GridVariables.GridColumn03;
                            grade_amt.Value = Convert.ToDecimal(GridVariables.GridColumn04);
                            filterfield.Value = GridVariables.GridColumn05;
                            companycode.Value = GridVariables.GridColumn10;
                            validrecordyn.Value = GridVariables.GridColumn06;
                            payrateid.Value = Convert.ToInt32(GridVariables.GridColumn07);

                            int newvar = 0;
                            int newid = Convert.ToInt32(command.ExecuteScalar());
                            newvar = newid;
                            
                        }
                    }
                    return;
                }
            }

Select allOpen in new window

With the database script earlier sent i guess you now have everything  to craete and test the procedure

Thanks

Olukay

I mistakenly had my parameter name as filtertypex  instead of filterfieldx. So there was
conflict between how the DB was created and what i had in my program

Thanks