Page 1 of 1
SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 10:41 am
by 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.
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 12:00 pm
by Enrico Maria Giordano
What is the error you are getting?
EMG
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 12:22 pm
by 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:
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 1:10 pm
by 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
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 1:31 pm
by Enrico Maria Giordano
What is otmp?
EMG
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 1:39 pm
by 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
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 1:42 pm
by Marc Vanzegbroeck
Rick Lipkin wrote: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.
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 1:50 pm
by 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
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 2:11 pm
by 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.
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 2:41 pm
by 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
Re: SQL Recordset delete problem
Posted:
Fri Nov 09, 2012 3:26 pm
by 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