How do I resolve an error writing into a postgresql database through a stored procedure

Error can be due to data type issue. Check if you are passing characters into an integer column.

I have checked   and cant find any data type issue

What do you think i should review next

Olukay

i Attach the debugger screen showing the values of each
of the 7  parameters  and their datatypes

After  review what do you think is the issues
ParameterValues15-01-2019.PNG

Check the same for Grade amount.

The values I am passing  and the datatype as shown in the debugger are:
Companycode "NNPC"  string
Ent_code    "LEV14" string
Ent_type    "A"     string
Grade_amt   875.45  decimal
Id          0       int
Mnthyear    "012019" string
Staff_no    "N001"   string
Subrunno    0       int  

Please check my last attchment

Thanks

Olukay

In event MonthlyVariationsModel, did you assign integer to ID and runno?

NO   !!

How do I go about  this  ??

Thanks

Olukay

Can you show me, in event MonthlyVariationsModel, how did you assign values to ID and runno?

Attached the word file

Assigned values to variables, the validation script  and the postgre parameter script  with
debugged values  showing that  the values  and Datatypes are  correct

See  attached word file

Olukay
Model-Definition-and-Assigned-Value.docx

Check LoginDeetails in your codes.

I have checked all over again and as you see from the debugger values all the parameters types are correct. May be we should ask that another expert also review what we have done to date. What do you think

Yes, OK to have more comments.

Try removing the Convert.ToInt32 around the command.ExecuteScalar.

I just tried your suggestion  and i got an error before running.

I tried to do a screen print but found it difficult

I have however copied the message to a word file and attached it.

What do you advise i try next ?

Thanks
NewErrorLine22012019.docx

By any chance do you have teamviewer  or any other remote support
software available.

I have teamviewer and if we schedule a session may be you can help
resolve the issue online.

You can download a copy of teamviewer from their site if you so wish

Thanks

Olukay

Sorry but I'm not able to do live support.  I'm also not a Postgres Expert.

You might check with the top .Net and Postgres Experts to see which ones participate in EEs Live:
https://www.experts-exchange.com/live/?headerLink=products_live

There should be an icon in their profiles saying if they participate in Live.


The error shows line 122 which from the original screenshots is the ExecuteScalar call.

Not being a Postgres Expert but going from other database products, stored procedures don't normally return value that way.  Most use output parameters.

If you look in the docs, I don't see a return value as part of the procedure syntax:
https://www.postgresql.org/docs/11/sql-createprocedure.html

Functions return a value:
https://www.postgresql.org/docs/11/sql-createfunction.html

Confirm you are calling a stored procedure and not a function.

If it is a stored procedure, try removing the "int newid =" and just execute it or change the postgres procedure to a function.

In the same project, i already executed a postgre stored procedure (see script below) and it worked returning an Id  (autonumber)

 public void CreateAbseCode(AbseCodeModel model)
        {
            using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
            {
 
                using (var command = new NpgsqlCommand("public.spabsecode_insert", conn))
                {
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new NpgsqlParameter("absecodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Absecode });
                    command.Parameters.Add(new NpgsqlParameter("absedescx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Absedesc });
                    command.Parameters.Add(new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticcompany });
                    int newId = Convert.ToInt32(command.ExecuteScalar());

                    model.Id = newId;
                }
            }


        }

The above worked fine . The only difference now is that this new table also a serial no ( int  NOT auto ) in the definition
(see script below which is giving the problem)

public void CreateMonthlyVariation(MonthlyVariationsModel model)
        {
            //Write monthly Variation Here  14-01-2019  O.A. Oluwole
            using (NpgsqlConnection conn = new NpgsqlConnection(pgrstring))
            {

                using (var command = new NpgsqlCommand("public.spmonthlyvariation_insert", conn))
                {
                    
                    conn.Open();
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new NpgsqlParameter("staffnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Staff_no});
                    command.Parameters.Add(new NpgsqlParameter("enttypex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Ent_type });
                    command.Parameters.Add(new NpgsqlParameter("entcodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Ent_code });
                    command.Parameters.Add(new NpgsqlParameter("mnthyearx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Mnthyear });
                    command.Parameters.Add(new NpgsqlParameter("subrunnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Subrunno });
                    command.Parameters.Add(new NpgsqlParameter("gradeamountx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Grade_amt });
                    command.Parameters.Add(new NpgsqlParameter("companycodex", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = LoginDetails.staticcompany });
                    //command.Parameters.Add(new NpgsqlParameter("Idx", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Output, Value = model.Id });
                    
                   // int newId = Convert.ToInt32(command.ExecuteScalar());
                    int newId = command.ExecuteScalar();
                    model.Id = newId;
                }
            }

Select allOpen in new window

       I have no doubt  iam running a stored procedure which i have even tried  using the Script/Select   facility in postgre and it worked.

Given the above facts what do you suggest i try next


Olukay

If you do not believe the problem is with the parameters and data types then there must be differences in the stored procedures themselves.  Can you get access to the Postgres code and see what differences there might be?

Same as what Netminder proposed.  All thing's being equal, the next step is to compare the stored procedures themselves.

-saige-

Thanks for ALL the inputs

Finally i  saw the issue  (see below)

command.Parameters.Add(new NpgsqlParameter("subrunnox", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Subrunno });

The NpgsqlDbType.Varchar should be NpgsqlDbType.Integer

Thanks

Olukay

Good catch...  ;)

Let us know if you need anything else.

-saige-

Just  did not know i needed to set the Datatype parameter  to integer within Npgsql.

The insistence of 3 experts confirming that the same direction actually pushed me into

looking for the issue

Thanks ALL for the BIG PUSH

Olukay

Hi Olukay,

Let's connect for this

Regards,
Pratik

Thanks Pratik. We just resolved this. Will give you a shout when i run
into my next issues

Thanks

Olukay

That's great it is resolved.

Can you please post the solution here.

Regards,
Pratik

@Pratik, Olukay posted his solution just above: https:/Q_29132324.html#a42781682

-saige-

Ohh yes,

Thanks saige