Skip site navigation (1) Skip section navigation (2)

Re: Check before INSERT INTO

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 (view raw or flat)
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.

  http://www.eset.com

pgsql-sql by date

Next:From: Gregory StarkDate: 2008-02-11 16:03:49
Subject: Re: Check before INSERT INTO
Previous:From: Shavonne Marietta WijesingheDate: 2008-02-11 12:04:18
Subject: Check before INSERT INTO

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group