CODEWITHSUNDEEP
c#postgresql-9.1
Suri
Suri

Reputation: 518

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

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

Upvotes: 2

Views: 3383

Answers (2)

user1914530
user1914530

Reputation:

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

Upvotes: 3

Geek Stocks
Geek Stocks

Reputation: 2030

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.

Upvotes: 1

Related Questions