Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D33489FB69E for ; Wed, 7 Mar 2007 08:25:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 05352-01 for ; Wed, 7 Mar 2007 08:25:34 -0400 (AST) X-Greylist: delayed 00:10:11.77276 by SQLgrey-1.7.4 Received: from customer-domains.icp-qv1-irony15.iinet.net.au (customer-domains.icp-qv1-irony15.iinet.net.au [203.59.1.175]) by postgresql.org (Postfix) with ESMTP id 192329FB2EA for ; Wed, 7 Mar 2007 08:25:37 -0400 (AST) Received: from 203-214-112-197.dyn.iinet.net.au (HELO [192.168.1.253]) ([203.214.112.197]) by iinet-mail.icp-qv1-irony15.iinet.net.au with ESMTP; 07 Mar 2007 20:15:23 +0800 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AgAAACA77kXL1nDFdGdsb2JhbAANgmM2jAoB X-IronPort-AV: i="4.14,258,1170604800"; d="scan'208,217"; a="79579651:sNHT17334090" Subject: [Re: PRIMARY KEY] From: Phillip Smith To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary="=-9pxjTbk9mno1YT1ietRw" Date: Wed, 07 Mar 2007 23:20:12 +1100 Message-Id: <1173270012.4925.66.camel@phillip-desktop> Mime-Version: 1.0 X-Mailer: Evolution 2.8.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.376 tagged_above=0 required=5 tests=BAYES_50, HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200703/90 X-Sequence-Number: 27728 --=-9pxjTbk9mno1YT1ietRw Content-Type: text/plain Content-Transfer-Encoding: 7bit If you actually need to know the value of N_GEN in your ASP application, you will need to query the database first and select the NEXTVAL from the sequence that the "serial" data type will create, then use that returned value in your insert - ie, DON'T exclude it from the insert, otherwise it will default to NEXTVAL again and return a different value. The only catch with this is that you most likely won't end up with contiguous values in this column - ie, if a user cancels after you seect nextval, but before you insert - the value of the sequence has already increased, and will be increased again before returning a value when you next select nextval Cheers, ~p On Wed, 2007-03-07 at 12:57 +0100, M.P.Dankoor wrote: > Hello, > > Is it possible to redesign your table as follows: > > create table Mod48_00_2007 ( > ID text, > N_GEN serial not null, > FORMSTORE text, > COD_NOTATIO text, > PA_COGNOME text, > constraint pk_Mod48_00_2007 primary key (N_GEN) > ); > > Your insert simply becomes: > INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME) > VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS') > > Do note that you do not refer to the N_GEN column, it will use the > next value, please refer to > http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL > for more information. > > Mario > > Shavonne Marietta Wijesinghe wrote: > > > Hello > > > > I have created a table > > CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, > > FORMSTORE text, COD_NOTAIO text, PA_COGNOME text); > > > > And i insert the rows via a form in ASP. When the form loads i have > > a functin that goes and gets the value of the field N_GEN adds 1 to > > it and shows it to the user. > > The problem is when i have 2 users working at the same time. > > > > For example the last value in my field N_GEN is 2 > > When both the users A and B loads the form (ASP page) it sees N_GEN > > = 3 :) > > > > So they fill in the form and user A clicks on the button OK and the > > record has been inserted with N_GEN = 3. But when the user B clicks > > on the button the record is not inserted because it has the same key > > "3" > > > > INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO, > > PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE > > MARTINIS') > > > > Is there any way i can do this automatically? i mean maybe i have to > > use someother property instead of "Primary Key" ?? > > > > Thanks > > > > Shavonne Wijesinghe > > > > --=-9pxjTbk9mno1YT1ietRw Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit If you actually need to know the value of N_GEN in your ASP application, you will need to query the database first and select the NEXTVAL from the sequence that the "serial" data type will create, then use that returned value in your insert - ie, DON'T exclude it from the insert, otherwise it will default to NEXTVAL again and return a different value.

The only catch with this is that you most likely won't end up with contiguous values in this column - ie, if a user cancels after you seect nextval, but before you insert - the value of the sequence has already increased, and will be increased again before returning a value when you next select nextval

Cheers,
~p

On Wed, 2007-03-07 at 12:57 +0100, M.P.Dankoor wrote:
Hello,

Is it possible to redesign your table as follows:

create table Mod48_00_2007 (
ID            text,
N_GEN         serial     not null,
FORMSTORE     text,
COD_NOTATIO   text,
PA_COGNOME    text,
constraint pk_Mod48_00_2007 primary key (N_GEN)
);

Your insert simply becomes:
INSERT INTO MOD48_00_2007 (ID, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES ('192168217200737122012', '', '00128', 'DE MARTINIS')

Do note that you do not refer to the N_GEN column, it will use the next value, please refer to http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL
for more information.

Mario

Shavonne Marietta Wijesinghe wrote:
Hello
 
I have created a table
CREATE TABLE MOD48_00_2007 ( ID text, N_GEN int PRIMARY KEY, FORMSTORE text, COD_NOTAIO text, PA_COGNOME text);
 
And i insert the rows via a form in ASP. When the form loads i have a functin that goes and gets the value of the field N_GEN adds 1 to it and shows it to the user.
The problem is when i have 2 users working at the same time.
 
For example the last value in my field N_GEN is 2
When both the users A and B loads the form (ASP page) it sees N_GEN = 3 :)
 
So they fill in the form and user A clicks on the button OK and the record has been inserted with N_GEN = 3. But when the user B clicks on the button the record is not inserted because it has the same key "3"
 
INSERT INTO MOD48_00_2007 (ID, N_GEN, FORMSTORE, COD_NOTAIO, PA_COGNOME) VALUES ('192168217200737122012', '3', '', '00128', 'DE MARTINIS')
 
Is there any way i can do this automatically? i mean maybe i have to use someother property instead of "Primary Key" ??
 
Thanks
 
Shavonne Wijesinghe


--=-9pxjTbk9mno1YT1ietRw--