Olukayode Oluwole
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(AbseCodeMod el 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
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(AbseCodeMod
{
NpgsqlConnection conn = new NpgsqlConnection("Host = 127.0.0.1; Database = Tournaments; Port = 5432; Username = postgres; Password = manager");
conn.Open();
{
NpgsqlCommand cmd = new NpgsqlCommand("spabsecode_
var p = new DynamicParameters();
cmd.Parameters.Add(new NpgsqlParameter("absecode"
cmd.Parameters.Add(new NpgsqlParameter("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
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
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?
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
To
Change
NpgsqlCommand cmd = new NpgsqlCommand("spabsecode_Insert", conn);
To
NpgsqlCommand cmd = new NpgsqlCommand("select spabsecode_Insert", conn);
ASKER
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
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 ...
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;
}
}
}
-saige-
ASKER
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 just tried it and got an error on the int newid line
Please see attached screen print and advise
Olukay
PostgreInsertError031218.docx
ASKER
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(:ab secode, :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
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_
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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
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
ASKER
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
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
not quite clear which of the posts was the solution that worked, i.e. the comment from http:#a42747479
ASKER
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
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
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.