.Delete Deletes From BOTH Joind Tables - Help!

.Delete Deletes From BOTH Joind Tables - Help!

Post by Tom Jastrzebsk » Thu, 02 Mar 2000 04:00:00



I do know why ADO deletes records from both joined table
despite setting "Unique Table" property.
(I use ADO2.5 with Jet4.0 provider)

Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT Table1.KeyColumn1, Table2.KeyColumn1
FROM Table2 RIGHT JOIN Table1 ON Table2.Column2 = Table1.KeyColumn1"
rec.CursorLocation = adUseClient
rec.Properties("Unique Table") = "Table1"
rec.Open cmd, , adOpenKeyset, adLockBatchOptimistic

After selecting record and executing .Delete then .UpdateBatch
records from both tables get deleted.

What's wrong with this picture ?

--
Tom Jastrzebski

Please remove 'spam' on reply.

 
 
 

.Delete Deletes From BOTH Joind Tables - Help!

Post by DRS » Fri, 03 Mar 2000 04:00:00



: I do know why ADO deletes records from both joined table
: despite setting "Unique Table" property.
: (I use ADO2.5 with Jet4.0 provider)
:
: Set cmd.ActiveConnection = cnn
: cmd.CommandType = adCmdText
: cmd.CommandText = "SELECT Table1.KeyColumn1, Table2.KeyColumn1
: FROM Table2 RIGHT JOIN Table1 ON Table2.Column2 = Table1.KeyColumn1"
: rec.CursorLocation = adUseClient
: rec.Properties("Unique Table") = "Table1"
: rec.Open cmd, , adOpenKeyset, adLockBatchOptimistic
:
: After selecting record and executing .Delete then .UpdateBatch
: records from both tables get deleted.
:
: What's wrong with this picture ?

Does your database have Cascade Delete on related fields set?

--

Hostes defutantur mei

 
 
 

.Delete Deletes From BOTH Joind Tables - Help!

Post by Tom Jastrzebsk » Fri, 03 Mar 2000 04:00:00



[...]

Quote:> Does your database have Cascade Delete on related fields set?

Nope ...
 
 
 

.Delete Deletes From BOTH Joind Tables - Help!

Post by Tom Jastrzebsk » Fri, 03 Mar 2000 04:00:00


I did not mention that my oryginal query is parametrized
and before deleting a record i usually call .Requery method
It looks like .Requerey is what causes the problem.

Just I still do know why.

Any way around it?
Thanks in advance.

Tom Jastrzebski

sample code:

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
DBFile
cnn.Open
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT Table2.KeyColumn1, Table1.KeyColumn1  FROM Table1
LEFT JOIN Table2 ON  Table1.KeyColumn1 = Table2.Column2 WHERE Table1.Column2
= [id]"
cmd.Parameters.Append cmd.CreateParameter("id", adInteger, adParamInput)
cmd.Parameters("id").Value = Null
rec.CursorLocation = adUseClient
rec.Properties("Unique Table") = "Table1"
rec.Open cmd, , adOpenKeyset, adLockBatchOptimistic

Debug.Print rec.RecordCount
cmd.Parameters("id").Value = 1
rec.Requery
Debug.Print rec.RecordCount
rec.MoveFirst
rec.Delete
rec.UpdateBatch

 
 
 

.Delete Deletes From BOTH Joind Tables - Help!

Post by Russ E. R » Sat, 04 Mar 2000 04:00:00


I Downloaded a "HotFix" from the http://www.microsoft.com/data site (look a
little in there) that said it fixed KeySet cursor errors after doing a
delete. Apparently the recordset would return errors on Moving() the record
pointer after a delete. The HotFix, if I remember correctly was to ADO 2.1
Service Pack 2.

I'd use ADO 2.1 SP2, and the hot fix and try that.

Russ R.


Quote:> I did not mention that my oryginal query is parametrized
> and before deleting a record i usually call .Requery method
> It looks like .Requerey is what causes the problem.

> Just I still do know why.

> Any way around it?
> Thanks in advance.

> Tom Jastrzebski

> sample code:

> cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> DBFile
> cnn.Open
> Set cmd.ActiveConnection = cnn
> cmd.CommandType = adCmdText
> cmd.CommandText = "SELECT Table2.KeyColumn1, Table1.KeyColumn1  FROM
Table1
> LEFT JOIN Table2 ON  Table1.KeyColumn1 = Table2.Column2 WHERE
Table1.Column2
> = [id]"
> cmd.Parameters.Append cmd.CreateParameter("id", adInteger, adParamInput)
> cmd.Parameters("id").Value = Null
> rec.CursorLocation = adUseClient
> rec.Properties("Unique Table") = "Table1"
> rec.Open cmd, , adOpenKeyset, adLockBatchOptimistic

> Debug.Print rec.RecordCount
> cmd.Parameters("id").Value = 1
> rec.Requery
> Debug.Print rec.RecordCount
> rec.MoveFirst
> rec.Delete
> rec.UpdateBatch

 
 
 

1. .Delete Deletes From BOTH Joind Tables - Help!

I do know why ADO deletes records from both joined table
despite setting "Unique Table" property.
(I use ADO2.5 with Jet4.0 provider)

Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT Table1.KeyColumn1, Table2.KeyColumn1
FROM Table2 RIGHT JOIN Table1 ON Table2.Column2 = Table1.KeyColumn1"
rec.CursorLocation = adUseClient
rec.Properties("Unique Table") = "Table1"
rec.Open cmd, , adOpenKeyset, adLockBatchOptimistic

After selecting record and executing .Delete then .UpdateBatch
records from both tables get deleted.

What's wrong with this picture ?

--
Tom Jastrzebski

Please remove 'spam' on reply.

2. Security login and Database users

3. instead of delete trigger delete data from table using execute and temporary table for deleted

4. US-TX-FUNCTIONAL MANUFACTURING LEAD

5. SQL delete query deletes but does not delete

6. How to implement xp_sendmail

7. ado .delete deletes joined table records?

8. Newbie - Can webpage be stored in SQL7.0?

9. No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

10. Can't delete table using Utility|delete

11. Deleted records when text file is linked as table (#deleted)

12. Delete trigger shows nothing in Deleted table

13. HELP! Creating delete trigger for cascade delete