Re: Check before INSERT INTO - Mailing list pgsql-sql

----- 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@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

 

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.

http://www.eset.com