SQL Recordset delete problem

[Marc Vanzegbroeck]

Hi,

I'm testing the delete-methode of a recordset and always get an error.
If testing it with oRs:delete(1)

The oRs:addnew methode is working fine.
Is there a permition for deleting in a recordset that I must set.

Normally I delete records with the 'DELETE FROM' command in SQL, but in this case I want to use the oRs:delete methode.

Does anyone know how to use it.

[Enrico Maria Giordano]

What is the error you are getting?

EMG

[Marc Vanzegbroeck]

This is the error

Application
===========
Path and name: c:\FWH\Projects\EasyFact\EASYFACT.exe (32 bits)
Size: 3,548,160 bytes
Time from start: 0 hours 0 mins 14 secs
Error occurred at: 09/11/12, 11:29:37
Error description: Error ADODB.Recordset/16389 E_FAIL: DELETE
Args:

[Marc Vanzegbroeck]

I just found out that the cursortype change after open a recordset

Code: Select all  Expand view

oRs = CREATEOBJECT( "ADODB.Recordset" )
oRs:cursortype :=1
oRs:cursorlocation :=3
oRs :locktype := 3
Msginfo(oRs:cursortype)  -> result 1
otmp:open(vCommando,cConnectionstring)
Msginfo(oRs:cursortype)  -> result 3
 

[Enrico Maria Giordano]

What is otmp?

EMG

[Rick Lipkin]

Marc

You have to be mindful of using oRs:Delete() based on how you created your query. If you are using a straight up "Select * from TABLE", oRs:Delete() will work just fine with your current cursor.

However, if you have a complex join of multiple tables to create your recordset .. you will probably get a 'fail' due to permissions.

Rick Lipkin

[Marc Vanzegbroeck]

Marc

You have to be mindful of using oRs:Delete() based on how you created your query. If you are using a straight up "Select * from TABLE", oRs:Delete() will work just fine with your current cursor.

However, if you have a complex join of multiple tables to create your recordset .. you will probably get a 'fail' due to permissions.

Rick Lipkin

Rick,

That can be the problem, because it's a RecordSet with a JOIN. Thanks for the info.

[Rick Lipkin]

Marc

What you have to do in a situation where you have a complex join .. if you delete the row, you will also delete the row in the joined table as well and realize the un-intended mistake.

For Example .. if you have a recordset joined between a customer and ( lets say ) an Invoice and you want to delete the Invoice .. you will ( if ADO will let you ) also delete the Customer record .. a BIG oops.

What you will need to do is create a connection and "DELETE from Invoice where ..... " then execute the connection to delete the Invoice... and close the connection. Once the Invoice is deleted you will need to re-query the recordset oRs:ReQuery() to realize the deleted row.

Hope that helps.

Rick Lipkin

[Marc Vanzegbroeck]

Rick,

Normaly I do the DELETE FROM on the 2 tables and do a requery, but I was thinking if do it with this command, ADO automaticly delete the rows in both tables.

[Rick Lipkin]

Marc

YES .. you are correct and I think the limit on oRs:delete() on a ( simple ) table join is two tables ( i could be wrong ). I have noticed that if you have right and left joins or unions you will not be able to use oRs:delete().

Rick

[Armando]

Rick, Friends:

Perhaps this can help

Code: Select all  Expand view

http://msdn.microsoft.com/en-us/library/windows/desktop/ms680834(v=vs.85).aspx
 

I had not try it yet, Regards