PgSqlException: "Parameter 'Name' is missing."

Discussion of open issues, suggestions and bugs regarding database management and development tools for PostgreSQL
Post Reply
Welton3
Posts: 23
Joined: Thu 04 Sep 2014 15:36

PgSqlException: "Parameter 'Name' is missing."

Post by Welton3 » Thu 10 May 2018 18:53

I have PostGreSQL 10.3 installed, and I am running dotConnect for PostgreSQL 7.10.1134.0 in Visual Studio 2017, Version 15.7.1, and .NET Framework 4.5.1.

I am attempting to get the primary key value for a newly inserted row using the returning into SQL syntax.

My table is defined as follows:

Code: Select all

-- Table: public.table_alias

-- DROP TABLE public.table_alias;

CREATE TABLE public.table_alias
(
  table_alias_id integer NOT NULL DEFAULT nextval('table_alias_table_alias_id_seq'::regclass),
  table_name character varying(30) NOT NULL,
  human_readable_name character varying(100) NOT NULL,
  table_alias character varying(10) NOT NULL,
  CONSTRAINT pk_table_alias PRIMARY KEY (table_alias_id)
  USING INDEX TABLESPACE indx,
  CONSTRAINT uk_tba_table_alias UNIQUE (table_alias)
  USING INDEX TABLESPACE indx,
  CONSTRAINT uk_tba_table_name UNIQUE (table_name)
  USING INDEX TABLESPACE indx,
  CONSTRAINT chk_tba_table_alias CHECK (lower(btrim(table_alias::text)) = table_alias::text AND btrim(table_alias::text) IS NOT NULL),
  CONSTRAINT chk_tba_table_name CHECK (upper(btrim(table_name::text)) = table_name::text AND btrim(table_name::text) IS NOT NULL)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.table_alias
  OWNER TO postgres;

-- Index: public.i_tba_table_alias

-- DROP INDEX public.i_tba_table_alias;

CREATE INDEX i_tba_table_alias
  ON public.table_alias
  USING btree
  (table_alias COLLATE pg_catalog."default")
TABLESPACE indx;

-- Index: public.i_tba_table_name

-- DROP INDEX public.i_tba_table_name;

CREATE INDEX i_tba_table_name
  ON public.table_alias
  USING btree
  (table_name COLLATE pg_catalog."default")
TABLESPACE indx;
I am using the following C# code to insert a row and return the primary key value:

Code: Select all

        private static void InsertTableAlias(PgSqlConnection connection)
        {
            const string TABLE_ALIAS_ID = "TABLE_ALIAS_ID";

            var command = new PgSqlCommand(string.Format(@"
                insert into table_alias (table_name, human_readable_name, table_alias) 
                                 values ('table_alias', 'Table Alias', 'tba')
                              returning table_alias_id 
                                   into :{0}", TABLE_ALIAS_ID), connection);

            var parameter = command.Parameters.Add(TABLE_ALIAS_ID, PgSqlType.Int);

            parameter.Direction = ParameterDirection.ReturnValue;

            var count = command.ExecuteNonQuery();

            var command2 = new PgSqlCommand("select * from table_alias");
            var reader = command2.ExecuteReader();
            var table = new PgSqlDataTable();

            table.Load(reader);
        }
However, when ExecuteNonQuery is run, it causes a PgSqlException with an ErrorCode of -2147467259, and a Message of "Parameter 'TABLE_ALIAS_ID' is missing".

I had initially tried setting the parameter direction to "Output", but received the same error.

Please advise.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlException: "Parameter 'Name' is missing."

Post by Pinturiccio » Fri 11 May 2018 15:06

PostgreSQL allows returning data to a variable only in blocks, for example inside a function. A separate insert query cannot return data into a variable. However, this is not required for your scenario. You just need to make the following changes in your code:
Delete "into :{0}" from your query. Your query must end with: "returning table_alias_id".

Your code will look like the following:

Code: Select all

private static void InsertTableAlias(PgSqlConnection connection)
{
    var command = new PgSqlCommand(string.Format(@"
        insert into table_alias (table_name, human_readable_name, table_alias) 
                            values ('table_alias', 'Table Alias', 'tba')
                        returning table_alias_id"), connection);

    var parameter = command.Parameters.Add(TABLE_ALIAS_ID, PgSqlType.Int);

    parameter.Direction = ParameterDirection.ReturnValue;

    var count = command.ExecuteNonQuery();

    Console.WriteLine(parameter.Value);

    var command2 = new PgSqlCommand("select * from table_alias", connection);
    var reader = command2.ExecuteReader();
    var table = new PgSqlDataTable();

    table.Load(reader);
}

Welton3
Posts: 23
Joined: Thu 04 Sep 2014 15:36

Re: PgSqlException: "Parameter 'Name' is missing."

Post by Welton3 » Tue 15 May 2018 14:49

Thank you very much. That is the answer that I needed.

Post Reply