Adding records with ADO

Dear Friends,

How can I look at the previous records if there is any same record as the record that will be added by using ADO

Thanks

You could do a couple of things:

1) create a primary key on your table. If you have one, you've defined a uniqueness that can't be overridden. If you attempt to add a record that has the same value as an existing one, you will get an error message.

2) run a command to check before attempting the update. The following sql would work:

"select count(*) from table where key=keyindatatoinsert"

if the return value is 0 then the record you're attmpting to add doesn't exist.

or if you are using Access, you could set the Indexed property of the field that you do not want to duplicate to Yes - Without Duplicates (my version of Access is in Spanish and that is how it translates. I can't remember exactly how it reads in the English version).

Example: if you have a table with a list of cities and you do not want to duplicate the city name, set the Indexed property of the field CITY to Yes - Without Duplicates. Then if the user tries to add a city that already exists, an error will occur.

Thanks for your replies but I'm using dataenvironment object, so when I use the sql statement to open a recordset I must close the recordset object, set the data source property to sql statement and than reopen it after checking the procedure above must be redone so I think all of these stuff decreases the performance

PS: I am using a key field, but I want to check it from code

Thanks

You can open more than one recrodset object in the database. Open/close a New recordset to run your query.

Quote:

---

Originally posted by JHausmann
You can open more than one recrodset object in the database. Open/close a New recordset to run your query.

---

Thanks very much, but I wondered actually if there is an other method. I think multiple recordsets run well...

Here is some code that I used to check whether a record in a table already existed. I am using ADO also... have a DataEnvironment, Connection, & Commands and resulting recordsets setup using VB6.

Dim strSql As String
Dim rstTemp As ADODB.Recordset

strSql = "SELECT FieldName" _
& " FROM Table" _
& " WHERE Table.FieldName = '" & TestCriteria & "';"

Set rstTemp = DataEnvironment1.Connection1.Execute(strSql, , adCmdText)

If rstTemp.RecordCount > 0 Then
Record Exists
Else
Record Doesnt Exist
End If

rstTemp.Close
Set rstTemp = Nothing

Hope this helps...Cheers Jonny

Thank for the code. I think this is the easiest and fastest way. I have done this stuff by adding extra query command. Have anybody has an idea which is faster adding a query command to dataenvironment or doing it with ado commands...

Thanks,