Check for Existing Records Before doing Insert

[leftee]

I am using a script of Ken Puls to insert data from my Excel sheet into a table in my pervasive database. It works great , but i have come to realize that i need to check and make sure
that there are no existing records in my databse table that matches the data that i am about to insert. Long story short i do not need duplicate records in my database. I'll do my best to provide enough info.


Info:

my Name Range tblHeadings in Excel will have a column named Router_Number. This is the only column i need to check against the field Router_Number in my databse table. There will be a Field in my database table named Router_Number. To sum it up the code works it's way down the Router_number column in my Excel Sheet and looks for duplicates or same numbers in the Router_Number field in my database table. I've tried doing a select query but just can't seem to get it to work. can someone help? I attached a link to the existing code

Thanks,

 

[mirtheil]

What do you want to do if you find these "duplicate" values? There's no "INSERT or UPDATE" type command in PSQL. You could run a select on some unique value within the record to make sure it doesn't already exist and either skip the record or issue an UPDATE statement instead of an INSERT. Are you sure you'll get duplicate records if you run the process? If there is a unique index on any of the fields, you'll get an error (Status 5, duplicate key value) when trying to insert a duplicate value.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[leftee]

If it will give me an error when it find a duplicate that will be OK. I'll check into it. thanks for the help.

Side Note: This pervasive databse works with Globalshop ERP
Do you have any experience working with Globalshop by any chance ?

Thanks

 

[mirtheil]

It should give an error if there is some field that requires a unique value.
You can look at the properties of the table in the Pervasive COntrol Center (right click the table name and select Properties) to see the fields, the field types, and the indexes. You can look at the indexes and see if any are unique. If they are and you are inserting a value that already exists, it will fail with a status 5.

I have heard of Globalshop ERP but have not worked with it specifically.



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com