views:

22

answers:

2

The Problem:

NpgsqlCommand.Text: INSERT INTO mytable (id, name) VALUES (:id, :name)
Parameter 1: name=id, value=42
Parameter 2: name=name, value="whatever"

command.ExecuteScalar() does not fail, but there is no new row. No duplicate id either.

The Code:

using (var command = connection.CreateCommand()) {
    command.Transaction = transaction;
    command.CommandText = "INSERT INTO mytable (id, name) VALUES (:id, :name)";       

    var idParameter = command.CreateParameter();
    idParameter.Direction = ParameterDirection.Input;
    idParameter.DbType = DbType.Int32;
    idParameter.ParameterName = "id";
    idParameter.Value = 42;
    command.Parameters.Add( idParameter );

    var nameParameter = command.CreateParameter();
    nameParameter.Direction = ParameterDirection.Input;
    nameParameter.DbType = DbType.String;
    nameParameter.ParameterName = "name";
    nameParameter.Value = "whatever";
    command.Parameters.Add( nameParameter );

    command.ExecuteScalar();
}

The code above is not the real code. I had to collect it from several DAL classes to linearize it...
The transaction is of course created before this code and is commited afterwards.

The Table Definition:

CREATE TABLE mytable
(
    "name" character varying NOT NULL,
    id integer NOT NULL,
    CONSTRAINT mytable_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
);
ALTER TABLE mytable OWNER TO myuser;

The Question(s):

  • Hu?
  • Why?
  • What am I doing wrong?

The Result:

Always check if your transactions are commited and double check if the Commit() is not commented out...

+1  A: 

Try to use

command.ExecuteNonQuery();

insetad of command.ExecuteScalar()

despart
*slapsforehead*You are right, but it still does not work (ExecuteNonQuery() returns 1).
EricSchaefer
weird, since returning 1 it ment that one row was affected (inserted)
despart
I commented out the commit. I was really sure it was there. Well it was there, kinda...
EricSchaefer
you should take a break ;)
despart
+1  A: 

Don't forget the Commit since you are using a transaction

jgauffin
I double checked it. The Commit() was commented... Thanks.
EricSchaefer