Adding records with ADO

[leontro]

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

[JHausmann]

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.

[DrewDog_21]

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.

[leontro]

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

[JHausmann]

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

[leontro]

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...

[JonnyCab]

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

[leontro]

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,