Avoid Inserting duplicate Records on DBISAM

General
1

Has anyone here worked with DBISAM?

I have a DBISAM database that I’m working with it,

It has a column called “RFC”, I can’t make it UNIQUE or primary key because this is a shared database with another program.

The issue is that; I want to insert Records to this database, but when I insert it, I insert duplicate records.
I use this:

'Dim InsertaRegistro As String= "INSERT INTO RFC (RFC, ApPaterno , ApMaterno , Nombre , Calle , NoExt , NoInt , Codigo, Estado, CURP, Colonia, Delegacion, Localidad, Telefono, Pais, Nacionalidad, IDFiscal) VALUES ('"+ RFC+"','"+ Paterno +"','"+ Materno +"','"+ Nombre +"','"+ Calle+"','"+ NumExt +"', '"+NumInt +"', '"+ CP +"', "+ Estado+", '"+ CURP+"', '"+ Colonia +"','"+ Delegacion+"', '"+ Localidad +"', '"+ Telefono+"', '"+ Pais +"', '"+ Nacionalidad +"', '" + IDFiscal +"')"

But that I want to do is If Exists a record that has one “RFC” value, then jump it and don’t write that record and only write records that not exists on database.

In mySQL uses “INSERT IGNORE INTO” but in this language I don’t know how to do that.

Any suggestions?
Thanks
:smiley: :smiley:

2

“INSERT INTO RFC (RFC, ApPaterno , ApMaterno , Nombre , Calle , NoExt , NoInt , Codigo, Estado, CURP, Colonia, Delegacion, Localidad, Telefono, Pais, Nacionalidad, IDFiscal) VALUES (’”+ RFC+"’,’"+ Paterno +"’,’"+ Materno +"’,’"+ Nombre +"’,’"+ Calle+"’,’"+ NumExt +"’, ‘"+NumInt +"’, ‘"+ CP +"’, “+ Estado+”, ‘"+ CURP+"’, ‘"+ Colonia +"’,’"+ Delegacion+"’, ‘"+ Localidad +"’, ‘"+ Telefono+"’, ‘"+ Pais +"’, ‘"+ Nacionalidad +"’, ‘" + IDFiscal +"’)"

  • " where ‘" + RFC + "’ not in (select RFC from RFC) "

…having a field with the same name as the table ,and the same name as a local variable in your code is a recipe for confusion :slight_smile:

3

You should consider using a prepared statement instead. It’s both safer to use and easier on the eyes :wink:
(If it can be done for this db type.)

4

[quote=263793:@Jeff Tullin]“INSERT INTO RFC (RFC, ApPaterno , ApMaterno , Nombre , Calle , NoExt , NoInt , Codigo, Estado, CURP, Colonia, Delegacion, Localidad, Telefono, Pais, Nacionalidad, IDFiscal) VALUES (’”+ RFC+"’,’"+ Paterno +"’,’"+ Materno +"’,’"+ Nombre +"’,’"+ Calle+"’,’"+ NumExt +"’, ‘"+NumInt +"’, ‘"+ CP +"’, “+ Estado+”, ‘"+ CURP+"’, ‘"+ Colonia +"’,’"+ Delegacion+"’, ‘"+ Localidad +"’, ‘"+ Telefono+"’, ‘"+ Pais +"’, ‘"+ Nacionalidad +"’, ‘" + IDFiscal +"’)"

  • " where ‘" + RFC + "’ not in (select RFC from RFC) "

…having a field with the same name as the table ,and the same name as a local variable in your code is a recipe for confusion :)[/quote]
It gives me error in Where:

Expected end of Statement but instead found WHERE in INSERT SQL statement

5

perhaps look into

WHERE NOT EXISTS ( )
instead?

(I’m afraid I dont have a DBSIM database to test the code against for you).

6

Work out your query first without variables in a SQL Editor. Only then try to replace the values with variables.