Jangomez74
asked on
Deleting Records using an ado.recordset object
Hi guys,
I'm a newbie so please just bear with me. I'm trying to delete all records using an ado.recordset object. i tried using the ff. code:
With adotable.recordset
If .recordcount > 0 then
.movefirst
do while not .eof
.delete
.movenext
loop
End if
End with
this fails and give me an error that sez "Key column information is insufficient or incorrect, too many rows were affected by update". i'm using a datagrid to display the records. Hope you cud help thanks.
I'm a newbie so please just bear with me. I'm trying to delete all records using an ado.recordset object. i tried using the ff. code:
With adotable.recordset
If .recordcount > 0 then
.movefirst
do while not .eof
.delete
.movenext
loop
End if
End with
this fails and give me an error that sez "Key column information is insufficient or incorrect, too many rows were affected by update". i'm using a datagrid to display the records. Hope you cud help thanks.
Hi,
As LowFatSpread said, most likley you have a recordset object with more then one table in it. ADO doesn't know what table or record to delete, since it cascade's down the query.
For the most effiecient way to empty a table, as I read you are trying to do, is to create an sql query that does that. For example, we have a table named "Customers".
Now, to create a SQL query that empty's that table, simple use the following:
DELETE
FROM Customers
Execute that on either a command object, or on your connection object. Delete query's can be given a where clause, that causes a certain amount of records to be deleted where the field matches the criteria you give it. Just like a select does.
Hope this helps you somewhat.
Grtz.©
D.
As LowFatSpread said, most likley you have a recordset object with more then one table in it. ADO doesn't know what table or record to delete, since it cascade's down the query.
For the most effiecient way to empty a table, as I read you are trying to do, is to create an sql query that does that. For example, we have a table named "Customers".
Now, to create a SQL query that empty's that table, simple use the following:
DELETE
FROM Customers
Execute that on either a command object, or on your connection object. Delete query's can be given a where clause, that causes a certain amount of records to be deleted where the field matches the criteria you give it. Just like a select does.
Hope this helps you somewhat.
Grtz.©
D.
one simple example.
dim conn as adodb.connection
dim strsql as string
set conn = new adodb.connection
conn.open "DSN=mydsn" ' your connection string here
strsql = "delete from mytable where field1 = 1"
conn.execute strsql
conn.close
set conn = nothing
For deletion of records, make sure you have a WHERE clause. If you do not include a criteria, all records will be deleted from the table.
hongjun
dim conn as adodb.connection
dim strsql as string
set conn = new adodb.connection
conn.open "DSN=mydsn" ' your connection string here
strsql = "delete from mytable where field1 = 1"
conn.execute strsql
conn.close
set conn = nothing
For deletion of records, make sure you have a WHERE clause. If you do not include a criteria, all records will be deleted from the table.
hongjun
ASKER
Hi lowfatspread, the recordsource for this adodc is "SELECT * FROM transaction_log_basket". Only one table for this ado object.
Guys, I've also tried the SQL-Query that deletes records from a table and it works but another problem arises. When i issue the delete it should finish the delete first before performing the next statement. But what happens is that it performs the next statement even without finishing the delete-SQL. I've used the connection object to execute the query.
I've done some experiment and it seems that if you try to delete more than 3 records using an ado.recordset object, this error occurs.
Guys, thanks for the help. hope you could give me some advice on this.
Guys, I've also tried the SQL-Query that deletes records from a table and it works but another problem arises. When i issue the delete it should finish the delete first before performing the next statement. But what happens is that it performs the next statement even without finishing the delete-SQL. I've used the connection object to execute the query.
I've done some experiment and it seems that if you try to delete more than 3 records using an ado.recordset object, this error occurs.
Guys, thanks for the help. hope you could give me some advice on this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey emoreau,
thanks, it works...
thanks, it works...
amazing Eric...
whats your recordset based on?
how many actual tables are involved...
Cheers!