validating data to be inserted into database

[hmurti17]

I want to insert data into the database(MS Access). While inserting I want to check whether the data to be inserted is already there in the database. If it is there then the data should not be inserted. I tried doing if rs.eof= true then rs.addnew. I am getting error in rs.addnew. The error is the operation requested cannot be performed. I think it is because the record pointer is pointing to the EOF. If I give rs.movelast I get the error recordset cannot move backwards.

I would like to know how this problem can be solved.

Thanks in anticipation,

[Andrew_Fryer]

You could try setting a primary key to the field where you wish to check for duplicates. If a duplicate is added then a trappable error will be produced.

One point to note though - you cannot set a primary key to a memo field, (> 255 chars)

Andrew

[Iouri]

First check what kind of cursor did you defined on your recordset. You might get this error if you have Forward only cursor. Define it as Dynamic cursor.

In order to check if this record exists in the database, execute SQL before inserting the record.
sSQL = "select * from Table where YourCondition"
Your condition must define uniquuness of this record

Open the recordset with this SQl and check if rs.RecordCount = 0 then insert your record

Iouri Boutchkine
iouri@hotsheet.com

[Cimperiali]

rs.eof= true then
rs.edit
rs.addnew
...

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.