How to insert a row without testing for existence

[Daniel Anderson]

Hi!

I have a database and in one of the table I need to insert some data. This
data has a primary key. often in my input I receive the same data twice, the
insertion fail as it violate my constraint. This all work ok but I have two
problems:
- I need to handle the error when the insertion fail, so that I can ignore
the error
- The odbc driver is outputing to the error to the debug stream output
(outputDebugString)

In MS-SQL I could do:
IF (SELECT count(*) where key = '1') = 0 INSERT into mytable (col1, col2)
values(key, data).
this work fine with MS-SQL, but I cannot find how to do it with the JetDB.

for now I do:
a select with the proper where clause,
If no row returns
Insert row

But I'd like to do it with a SQL command instead of programmaticly

Thanks for your Help

Daniel

 

[MGFoster]

Hi!

I have a database and in one of the table I need to insert some data. This
data has a primary key. often in my input I receive the same data twice, the
insertion fail as it violate my constraint. This all work ok but I have two
problems:
- I need to handle the error when the insertion fail, so that I can ignore
the error
- The odbc driver is outputing to the error to the debug stream output
(outputDebugString)

In MS-SQL I could do:
IF (SELECT count(*) where key = '1') = 0 INSERT into mytable (col1, col2)
values(key, data).
this work fine with MS-SQL, but I cannot find how to do it with the JetDB.

for now I do:
a select with the proper where clause,
If no row returns
Insert row

But I'd like to do it with a SQL command instead of programmaticly

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use error traps.

Pseudo code:

on error goto err_

currentdb.execute "insert into mytable(col1, col2) values(key, data)"

exit_:
exit sub

err_:
If err = duplicate the resume exit_
' handle other errors here.

If you are using ODBC use the DBEngine.Errors collection:

for each err in DBEngine.Errors
strErr = strErr & err.Description & vbcr
next
if instr(strErr,"Duplicate")>0 then resume exit_

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi51GYechKqOuFEgEQLmNQCeNklUDDdr/k53siKUwDvFoYyXH+sAn35Z
c7zhb0bOIG2+MQaDnAnPepLi
=RTX4
-----END PGP SIGNATURE-----

 

[Daniel Anderson]

Use error traps.

thanks for your answer, but as I stated in my message I already tried that
and I do not like it for two reasons:
1- Everywhere I insert records I must do the trapping (I have many tables)
and most important:
2- the Ms driver output an error to the debug console, when you insert a lot
of records this tend to make a lot of noise in the log output which I'd like
to keep for real message.
Right now when I use the approach of trapping the error I can have thousands
of line in the debug windows stating that I tried to insert a duplicate key.