Re: Check before INSERT INTO

"Shavonne Marietta Wijesinghe" <shavonne(dot)marietta(at)studioform(dot)it> 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!