Thanks for the reply Grogory. I am trying to do a INSERT INTO.
Here is my table. n_gen, n_sheet, tot_n_sheet are defined as Primary Key
(Serial not null)
id | n_gen | n_sheet | tot_n_sheet
----------+-----------+-----------+-------------
a | 1 | 1 | 1
b | 2 | 1 | 2
x | 2 | 2 | 2
u | 3 | 1 | 1
r | 4 | 1 | 3
a | 4 | 2 | 3
s | 4 | 3 | 3
So there are 2 users inserting in to the db. In my ASP page i have a field
that shows the value of n_gen +1. So when the 2 users both login at the same
time, with different sessions, they both see "7" in the n_gen field. But
when they click on the sumbit button only one record is inserted and the
other is lost.
I though it was possible to change the SQL string before it does the
update.. But i can't seem to find a solution for it.. Any idea ??
Thanks,
Shavonne
----- Original Message -----
From: "Gregory Stark" <[EMAIL PROTECTED]>
To: "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, February 11, 2008 5:03 PM
Subject: Re: Check before INSERT INTO
"Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> writes:
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.
It's not clear to me what you're trying to do. If you're trying to update
an
existing record then you might want something like example 37-1 on this
page:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html
If you want to pick the first available n_gen then you're going to have to
repeatedly try inserting until you don't get that error. That will perform
quite poorly when you get to large values. You could do a "SELECT
max(n_gen)
WHERE..." first but even that will be quite a lot of work for your
database.
Perhaps you should rethink n_gen and use a serial column to generate your
primary key instead.
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 & "'")
For what it's worth your script is a security hole. Look into using query
parameters which in ASP will probably be represented by "?". The method
above
will allow hackers to get direct access to your database and do nasty
things.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
