Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C68952E012C for ; Mon, 11 Feb 2008 09:14:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 09700-05 for ; Mon, 11 Feb 2008 09:13:54 -0400 (AST) Received: from smtpout25.fbc.local (smtpout25.attiva.biz [85.37.16.26]) by postgresql.org (Postfix) with ESMTP id 18EEF2E0129 for ; Mon, 11 Feb 2008 09:14:00 -0400 (AST) Received: from FBCMFE02B19.fbc.local ([192.168.31.237]) by smtpout25.fbc.local with Microsoft SMTPSVC(6.0.3790.1830); Mon, 11 Feb 2008 14:13:56 +0100 Received: from dream ([88.34.161.130]) by FBCMFE02B19.fbc.local with Microsoft SMTPSVC(6.0.3790.1830); Mon, 11 Feb 2008 14:13:54 +0100 Message-ID: <014301c86caf$f3f4ea50$3102a8c0@dream> Reply-To: "Shavonne Marietta Wijesinghe" From: "Shavonne Marietta Wijesinghe" To: "Kopljan Michael" Cc: References: <000301c86cab$f529e4b0$1100a8c0@discovery> Subject: Re: Check before INSERT INTO Date: Mon, 11 Feb 2008 14:13:52 +0100 Organization: Studio FORM MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0140_01C86CB8.5485B450" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3138 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 X-OriginalArrivalTime: 11 Feb 2008 13:13:54.0746 (UTC) FILETIME=[F3EBE9A0:01C86CAF] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/77 X-Sequence-Number: 30168 This is a multi-part message in MIME format. ------=_NextPart_000_0140_01C86CB8.5485B450 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ehmm and where is it said that N_GEN should add 1 before inserting the = record?? sorry i don't understand the help. Shavonne ----- Original Message -----=20 From: Kopljan Michael=20 To: 'Shavonne Marietta Wijesinghe'=20 Sent: Monday, February 11, 2008 1:45 PM Subject: RE: [SQL] Check before INSERT INTO Try fierst to add Transaction issolation level to Connection object : Conn.IsolationLevel =3D 1048576 && adXactIsolated =20 Then when user submit form try something like this : strSQL =3D 'SELECT * FROM my_shevi ORDER BY n_gen DESC, n_sheet DESC, = tot_sheet DESC LIMIT 1' Set SQLN_GEN =3D oConn.Execute(strSQL)=20 SQLN_GEN.AddNew SQLN_GEN('id').Value =3D 'a' Conn.BeginTrans() SQLN_GEN.Update SQLN_GEN.Requery() Conn.CommitTrans() =20 Bye Michael =20 -------------------------------------------------------------------------= ----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Shavonne Marietta = Wijesinghe Sent: Monday, February 11, 2008 1:04 PM To: pgsql-sql@postgresql.org Subject: [SQL] Check before INSERT INTO =20 My table structure =20 CREATE TABLE my_shevi=20 ( id text, n_gen serial NOT NULL, n_sheet serial NOT NULL, tot_sheet serial NOT NULL, CONSTRAINT my_shevi_pkey PRIMARY KEY (n_gen, n_sheet, tot_sheet) )=20 WITH OIDS; ALTER TABLE my_shevi OWNER TO postgres; =20 The user updates the DB via ASP. When 2 users click on the submit = button at the same time, only 1 record is inserted. (ERROR: duplicate = key violates unique constraint "my_shevi_pkey") =20 For example they both send a string like below. strSQL =3D INSERT INTO my_shevi VALUES ('a', 4, 1, 1); =20 I thought of adding a test before executing the insert into.=20 =20 Set SQLN_GEN =3D oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), = upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN=3D'" & n_gen & "' = AND N_SHEET=3D'" & n_sheet & "' AND TOT_N_SHEET=3D'" & tot_n_sheet & = "'") =20 if (not SQLN_GEN.eof) then ***** set n_gen + 1 =20 else ***** leave n_gen the way it is end if =20 conn.Execute strSQL But how can i ask it to change the n_gen value??? (The part with the = *****)=20 =20 Thanks =20 Shavonne Wijesinghe __________ Information from ESET Smart Security, version of virus = signature database 2863 (20080211) __________ The message was checked by ESET Smart Security. http://www.eset.com ------=_NextPart_000_0140_01C86CB8.5485B450 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
ehmm and where is it said that N_GEN = should add 1=20 before inserting the record?? sorry i don't understand the = help.
 
Shavonne
----- Original Message -----
From:=20 Kopljan=20 Michael
To: 'Shavonne Marietta=20 Wijesinghe'
Sent: Monday, February 11, 2008 = 1:45=20 PM
Subject: RE: [SQL] Check before = INSERT=20 INTO

Try fierst = to add=20 Transaction issolation level to Connection object=20 :

Conn.IsolationLevel  =20 =3D 1048576 =20 &&=20 adXactIsolated

 

Then when = user=20 submit form try something like this :

strSQL =3D = =92SELECT * FROM=20 my_shevi ORDER BY n_gen DESC, n_sheet DESC, tot_sheet DESC LIMIT=20 1=92

Set SQLN_GEN =3D = oConn.Execute(strSQL)

SQLN_GEN.AddNew

SQLN_GEN(=92id=92).Value =3D=20 =92a=92

Conn.BeginTrans()

SQLN_GEN.Update

SQLN_GEN.Requery()

Conn.CommitTrans()

 

Bye=20 Michael

 

From: = pgsql-sql-owner@postgresql.org=20 [mailto:pgsql-sql-owner@postgresql.org] On=20 Behalf Of Shavonne Marietta Wijesinghe
Sent: Monday, February 11, 2008 = 1:04=20 PM
To:=20 pgsql-sql@postgresql.org
Subject: [SQL] Check before = INSERT=20 INTO

 

My table=20 structure

 

CREATE TABLE = my_shevi =
(
  id=20 text,
  n_gen serial NOT NULL,
  n_sheet serial NOT=20 NULL,
  tot_sheet serial NOT NULL,
  CONSTRAINT = my_shevi_pkey=20 PRIMARY KEY (n_gen, n_sheet, tot_sheet)
)
WITH OIDS;
ALTER = TABLE=20 my_shevi OWNER TO postgres;

 

The user updates the DB = via ASP.=20 When 2 users click on the submit button at the same time, only 1 = record is=20 inserted. (ERROR:  duplicate key violates unique constraint=20 "my_shevi_pkey")

 

For example they both = send a=20 string like below.

strSQL =3D = INSERT INTO=20 my_shevi VALUES ('a', 4, 1, 1);

 

I thought of adding a = test before=20 executing the insert = into. 

 

Set SQLN_GEN =3D = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), upper(TOT_N_SHEET) = FROM "=20 & TableName & " WHERE N_GEN=3D'" & n_gen & "' AND = N_SHEET=3D'"=20 & n_sheet & "' AND TOT_N_SHEET=3D'" & tot_n_sheet &=20 "'")

 

if (not = SQLN_GEN.eof)=20 then
     *****=20 set n_gen + 1  
else
     *****=20 leave n_gen the way it is
end=20 if

 

conn.Execute=20 strSQL


But how can i ask it = to change=20 the n_gen value??? (The part with the *****)=20

 

Thanks

 

Shavonne=20 Wijesinghe



__________ Information from ESET = Smart=20 Security, version of virus signature database 2863 (20080211)=20 __________

The message was checked by ESET Smart = Security.

http://www.eset.com

------=_NextPart_000_0140_01C86CB8.5485B450--