Phil795 Phil795 - 6 days ago 5
C# Question

ODBC postgres error with not-null constraint

I have a simple user table

CREATE TABLE benutzer
(
user_id character(38) NOT NULL,
user_name character varying(50) NOT NULL,
user_fullname character varying(50),
user_password character varying(50),
user_email character varying(50),
user_locked boolean,
CONSTRAINT pk_benutzer PRIMARY KEY (user_id)
)
WITH (
OIDS=FALSE
);


And a Odbc Connection in my C# programm.
But I get null-value error on
ExecuteNonQuery()


try
{
if(con.State != System.Data.ConnectionState.Open)
con.Open();

System.Console.WriteLine("State: " + con.State.ToString());

OdbcCommand cmd = new OdbcCommand("INSERT INTO benutzer (user_id, user_name, user_fullname, user_password, user_email, user_locked) VALUES (?,?,?,?,?,?)", con);
cmd.Parameters.Add(username, OdbcType.Char, 38);
cmd.Parameters.Add(username, OdbcType.VarChar, 50);
cmd.Parameters.Add(name, OdbcType.VarChar, 50);
cmd.Parameters.Add(username, OdbcType.VarChar, 50);
cmd.Parameters.Add(mail, OdbcType.VarChar, 50);
cmd.Parameters.Add("1", OdbcType.Char, 1);

cmd.ExecuteNonQuery();

con.Close();
}
catch (OdbcException e)
{
System.Console.Write(e.ToString());
}


The values
username, name, mail
are filled with data.

Does somebody know a solution?

Answer

The first parameter in the method Add should be the parameter name, not its value. You are essentially creating 6 parameters without giving them any value.
You don't receive any error because ODBC doesn't care about how do you name the parameters.

You can simply modify your code adding the value to each parameter according to your inputs

OdbcCommand cmd = new OdbcCommand(@"INSERT INTO benutzer 
 (user_id, user_name, user_fullname, user_password, user_email, user_locked) 
 VALUES (?,?,?,?,?,?)", con);
cmd.Parameters.Add("p1",OdbcType.Char, 38).Value = user_id;
cmd.Parameters.Add("p2", OdbcType.VarChar, 50).Value = user_name;
cmd.Parameters.Add("p3", OdbcType.VarChar, 50).Value = user_full_name;
cmd.Parameters.Add("p4", OdbcType.VarChar, 50).Value = user_password;
cmd.Parameters.Add("p5", OdbcType.VarChar, 50).Value = mail;
cmd.Parameters.Add("p6", OdbcType.Char, 1) = "1";

(The values should be variables you have collected from your user inputs)