First, why are you creating the connection, executing one SQL, and then dropping the connection? Normally, you’d open the connection once, and leave it open for the duration of the application.
Next, make a stored procedure (see CREATE PROCEDURE) which does the following:
UPDATE the existing record
If the number of affected rows is zero
INSERT the data
The number of affected rows is gotten with GET DIAGNOSTICS in DB2, SQL%ROWCOUNT in Oracle, or @@ROWCOUNT in MS SQL Server.
Call the stored procedure from your code. Rather than using DIM to set up single-use string variables, you can just pull from the form directly, since you’ll only need each item once.
The form of the call is:
DB2: CALL procedureName(arg1, arg2, … argn)
Oracle: BEGIN procedureName(arg1, arg2, … argn); END;
MS SQL Server: EXEC procedureName arg1, arg2, … argn
Sheldon Linker (email@example.com)
Linker Systems, Inc. (www.linker.com)