How to return auto generated id in insert query in postgres sql

Question

I have a table.this is the script for my table:-

CREATE TABLE news
(
  news_id bigint NOT NULL DEFAULT nextval('news_seq'::regclass),
  title character varying(1024),
  description character varying(2024),
  CONSTRAINT pk_news_newsid PRIMARY KEY (news_id)  
)
WITH (
  OIDS=FALSE
);
ALTER TABLE news OWNER TO viewer;

Now I want to get the auto generated news_id on insert the new record in the table.

This is C# function for insert the news:-

 public Int64 AddNews(News newNews)
    {
        string query = string.Empty;
        try
        {
            string dateFormat = ConfigurationManager.AppSettings["DateFormat"];
            NpgsqlParameter[] parm = new NpgsqlParameter[2];
            parm[0] = new NpgsqlParameter("title", newNews.NewsTitle);
            parm[1] = new NpgsqlParameter("des", newNews.NewsDescription);

            query = @" INSERT INTO   news(title, description)
                            VALUES   (:title, :des) 
                         Returning   news_id";

            int id=NpgSqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, query, parm);

            return id;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

On executing this function I always get the -1 value

Thanks in advance

While executing the following query on pgAdmin gives correct result:

INSERT INTO news(title, description)
    VALUES ('title','description')
    Returning news_id

score 3 · Accepted Answer · 2013-01-25 12:26:52Z

3

Have you tried NpgsqlCommand.ExecuteScalar or the equivalent for the API you are using?

edited Jan 25, 2013 at 12:26

answered Jan 25, 2013 at 12:04

user1914530

Sign up to request clarification or add additional context in comments.

2 Comments

user1914530 Over a year ago

Because all I see is NpgSqlHelper.ExecuteNonQuery.

user1914530 Over a year ago

@facebook-100001745831878 well that is probably your problem. ExecuteNonQuery returns The number of rows affected if known; -1 otherwise. Read the link that I posted for ExecuteScalar.

Geek Stocks · Accepted Answer · 2013-01-25 12:10:47Z

1

Two possibilities I see:

1) the -1 value indicates you are hitting a rollback situation. When you execute the function, check the table: did the record successfully insert or did some other situation cause a rollback? If so, find what is causing the rollback (see #2).

2) the -1 value also can be returned if you are running a non-insert statement. I realize you ARE running an insert, but what about any TRIGGERS on this table? Are you doing any Select statements in the trigger?

Hope this helps.

answered Jan 25, 2013 at 12:10

Geek Stocks

2,0503 gold badges29 silver badges43 bronze badges

1 Comment

Geek Stocks Over a year ago

The fact that the query works in stand-alone fashion indicates the C# is the culprit. I would check parms and again, do the test in #1 above. Are you getting a successful record insertion?

Collectives™ on Stack Overflow

How to return auto generated id in insert query in postgres sql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related