From: | "Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)it> |
---|---|
To: | "Kopljan Michael" <kopljan(at)beotel(dot)net> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Check before INSERT INTO |
Date: | 2008-02-11 13:13:52 |
Message-ID: | 014301c86caf$f3f4ea50$3102a8c0@dream |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 -----
From: Kopljan Michael
To: 'Shavonne Marietta Wijesinghe'
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 = 1048576 && adXactIsolated
Then when user submit form try something like this :
strSQL = 'SELECT * FROM my_shevi ORDER BY n_gen DESC, n_sheet DESC, tot_sheet DESC LIMIT 1'
Set SQLN_GEN = oConn.Execute(strSQL)
SQLN_GEN.AddNew
SQLN_GEN('id').Value = 'a'
Conn.BeginTrans()
SQLN_GEN.Update
SQLN_GEN.Requery()
Conn.CommitTrans()
Bye Michael
------------------------------------------------------------------------------
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Shavonne Marietta Wijesinghe
Sent: Monday, February 11, 2008 1:04 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] Check before INSERT INTO
My table structure
CREATE TABLE my_shevi
(
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)
)
WITH OIDS;
ALTER TABLE my_shevi OWNER TO postgres;
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")
For example they both send a string like below.
strSQL = INSERT INTO my_shevi VALUES ('a', 4, 1, 1);
I thought of adding a test before executing the insert into.
Set SQLN_GEN = oConn.Execute("SELECT upper(N_GEN), upper(N_SHEET), upper(TOT_N_SHEET) FROM " & TableName & " WHERE N_GEN='" & n_gen & "' AND N_SHEET='" & n_sheet & "' AND TOT_N_SHEET='" & tot_n_sheet & "'")
if (not SQLN_GEN.eof) then
***** set n_gen + 1
else
***** leave n_gen the way it is
end if
conn.Execute strSQL
But how can i ask it to change the n_gen value??? (The part with the *****)
Thanks
Shavonne Wijesinghe
__________ Information from ESET Smart Security, version of virus signature database 2863 (20080211) __________
The message was checked by ESET Smart Security.
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2008-02-11 16:03:49 | Re: Check before INSERT INTO |
Previous Message | Shavonne Marietta Wijesinghe | 2008-02-11 12:04:18 | Check before INSERT INTO |