Link to home
Start Free TrialLog in
Avatar of Olukayode Oluwole
Olukayode OluwoleFlag for Canada

asked on

How do i Save a windows form data into postgresql database using a stored procedure

I have a C# application  and i need to save data  from a
windows form screen to a postgresql database

The connection string and the stored procedure  (spabsecode_Insert)  for inserting into the database  
have been tested  and are working fine.

Below  is my Save Method

public void CreateAbseCode(AbseCodeModel model)
        {
            NpgsqlConnection conn = new NpgsqlConnection("Host = 127.0.0.1; Database = Tournaments; Port = 5432; Username = postgres; Password = manager");
            conn.Open();
            {
                NpgsqlCommand cmd = new NpgsqlCommand("spabsecode_Insert", conn);

                var p = new DynamicParameters();

                cmd.Parameters.Add(new NpgsqlParameter("absecode", model.Absecode));
                cmd.Parameters.Add(new NpgsqlParameter("absedesc", model.Absedesc));

                cmd.ExecuteNonQuery();

                model.Id = p.Get<int>("Id");

            }
        }

When i click  the save button and in the debugger  i get the line  cmd.ExecuteQuery() and then run into a syntax error .



I attached 2 screen shots

1. A screen of the record I am trying to save

2. A screen of the debugger screen showing that execution stopped  on cmd.ExecuteQuery  line

I will be grateful to have an expert look at the syntax of this line and the next line which is trying

to retrieve the model  ID  (An auto increament number)

Thanks

Olukay
Save-Screen021218.PNG
SaveDebugged021218.PNG
Avatar of arnold
arnold
Flag of United States of America image

Double check whether the sp with the parameters you are passing works when executed directly on the postgresql

Then double check what data is being sent through to make sure it is not getting blank input, typos of variables you would not be the first with a slight typo..
The parameters may have to be enclosed in single quotes.

You may have to add exec since you are trying to execute a stored procedure.
Avatar of Olukayode Oluwole

ASKER

Thanks for your  input.

I have  double checked  that the stored procedure works (see attached)

I observed  that i used  absecodex and absedescx  as the parameters in the stored procedure

I have changed the one in the application to be the same

I tried " public.spabsecode_insert" ,  "exec spabsecode_insert"  and  "select spabsecode_insert"  as the stored procedure string

The first 2  gave the same error i had before  which is attached  in the word document.

The select also stopped  on the line but said it was expecting  from in the statement.  I guess  it was

thinking that i wanted to put a select statement there  instead of a stored procedure.

You requested that I " double check what data is being sent through"  . How   do i go about this ??

Attached is word file confirming that the stored procedure works and the error I am having.

I am suspecting that my syntax  for that line is wrong . Can you please help double check this syntax.

What do you suggest i try next

Regards

Olukay
TestANDerrorScreen-021218.docx
When you directly interact with PostgreSQL through its builtin query,

The c# command, should match

The way you would use a. Comment output
<!-- here you would output the data being passed. ->>
Another option, in debugging you would output the data in the browser

https://www.postgresql.org/message-id/41F006E2.5010002%40commandprompt.com

What does the stored procedure return number of rows created?
Your current code is missing the select directive
Change
NpgsqlCommand cmd = new NpgsqlCommand("spabsecode_Insert", conn);

Open in new window


To
NpgsqlCommand cmd = new NpgsqlCommand("select spabsecode_Insert", conn);

Open in new window

I have been to postgresql site and i am trying to join a list

Its difficlut  joining one. I just havent been able to figure it out

I have created an account for myself

Can you please point in the direction of how to join a mailing list  group.

Thanks  Olukay
i do not know first hand.

https://www.postgresql.org/list/

Following the link to the list....
It gives you an option manage subscriptions or unsubscribe
You can use the local or one of the shared ...
Try like this:
public void CreateAbseCode(AbseCodeModel model)
{
	using (NpgsqlConnection conn = new NpgsqlConnection("Host = 127.0.0.1; Database = Tournaments; Port = 5432; Username = postgres; Password = manager"))
	{
		conn.Open();
		using (NpgsqlCommand cmd = new NpgsqlCommand("spabsecode_Insert(:absecode, :absedesc)", conn))
		{
			cmd.Parameters.Add(new NpgsqlParameter("absecode", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Absecode });
			cmd.Parameters.Add(new NpgsqlParameter("absedesc", NpgsqlTypes.NpgsqlDbType.Varchar) { Direction = ParameterDirection.Input, Value = model.Absedesc });

			int newId = Convert.ToInt32(cmd.ExecuteScalar());

			model.Id = newId;
		}
	}
}

Open in new window

-saige-
Thanks for your Input  it,

I just tried it and got an  error  on the int newid line

Please  see attached  screen print and advise

Olukay
PostgreInsertError031218.docx
I have being trying  to resolve the syntax error

the error is  Npgsql.PostgresException: '42601: syntax error


I looked  in pg_log  directory and obtained the following  log

2018-12-03 16:34:04 PST ERROR:  syntax error at or near "spabsecode_Insert" at character 1
2018-12-03 16:34:04 PST STATEMENT:  spabsecode_Insert($1, $2)
2018-12-03 16:34:11 PST LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

Its occurring on the line below:

using (NpgsqlCommand cmd = new NpgsqlCommand("spabsecode_Insert(:absecode, :absedesc)", conn))

What i do not understand is why the connection is being closed.

I also look at the postgresql.conf file   and listening address =  '*'

So why is postgres  not allowing input

Thanks

Olukay
ASKER CERTIFIED SOLUTION
Avatar of it_saige
it_saige
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
you have to use the same syntax for the command in your c# query as you have in the direct query.

add select

then see what error if any.
the error seems to be sequential. when it received the spabsecode_Insert 'parameter1', 'parameter2' it could not execute, you are not checking for output/transaction error such that your flow presumed the execution of the command was successful, so you closed the connection.
This has being a very good experience
Npgsql site experts proposed a solution that would not use a stored
procedure but a sql statement  in the application  even though they claim it would prevent
sql injection

Saige and Arnold  achieved exactly what i wanted which was to use a stored  procedure for the
reason that it is executed 100%  on the server side, faster at execution and more secured

Thanks you guys for hands holding. You are both awesome.

Olukay
I am trying  to get  to score my experts but some how can not
reach the screen

I also tried  to reach Admin but my screen keeps going blank may be because
i said the problem is solved

My score would be  Saige  600 and Arnold 400

If  Admin can reflect this that would be good  BUT if i have to do it then
please tell me how to get to scoring pages

Once again  Thank you Very much

Olukay
You can request attention to have them adjust, or just leave it as is.

not quite clear which of the posts was the solution that worked, i.e. the comment from http:#a42747479
I will rather leave as it is.

Both solution appear  workable though i am currently implementing Saige
solution because it uses  stored procedure  100%

The other solution uses sql within the script though Npgsql claims is also
guides against sql injection

Thanks