delete records in adodb record set

[ravindarjobs]

Hi all, i am using ms access 2003,

i have a form,
i connect to the mysql database using adodb connection.
this works fine.

but when i delete a record, it is deleted but still the record set shows that record.
when i close the application and again run it, then the record set dosnt show the delted record.

what could be the problem


my code goes like this
rsInfotable is a adodb record set

i opened the record set with the code

 str = "select * from infotable;"
rsInfotable.Open str, objConn, adOpenKeyset, adLockOptimistic
 

 
If txtAutonumber.Visible Then
 
 
 
            rsInfotable.Delete
 
 
            MsgBox ("Record has been successfully Deleted")
            rsInfotable.MoveNext
 
 
        If rsInfotable.EOF And rsInfotable.BOF Then
            MsgBox ("No more records")
            Call DiableButtons
            Exit Sub
 
        ElseIf rsInfotable.EOF Then
            rsInfotable.MoveLast
             Call DisplayData
 
 
        End If
    End If
 

though i do it with rsInfotable.delete it still shows the record in the record set

[MikeTheBike]

Hi

I am not sure how this deleted record shows its self in the procedure (ie. what is the problem?), but just perhaps you could change this

If rsInfotable.EOF And rsInfotable.BOF Then

to this

If rsInfotable.RecordCount = 0 Then

????


MTB

[ravindarjobs]

my application target is very simple that
1. connect to the database
2. add records,
3. edit records,
4. navigate through records
5. and delete records.

i am doing all these fine except that deleting record.

when i delete with my code, the records are deleted in the database, but the record set still contains that record.

suppose say, i have 20 records, and now i am at say 8th record, now i pressed delete button and i perform a movenext action. The record is deleted in database, but when i press previous button or like that, i am still able to see that record in my recordset. I.e i am able to navigate through the deleted record which is wrong.


do i need to requery the record set?
if i requery the recordset, then the current position will be lost which is not a correct approach.

what is happening with my code?

thank you

[MikeTheBike]

Hi again

Well, I cannot fine any think wrong with your code (except for to suggestion in my last post as an error occures when there are not records left!). Other than that I cannot see where this is a problem for you (ie where does this error/probelm occure?). Perhaps I am missing something simple.

Just one thought, deleted records do remain in a FORM until it is Requeried?


MTB

[ravindarjobs]

Hi, i attached my application.

indeed there is no error coming.But functionality is wrong in my code.

have a look at the attachment.

Open the database,
1.Run the form
2. Click open connection button
3. navigate to some record, or go to final record
4. Now press delete. there comes confirmation message that record has been deleted.
5. now click previous button, or some how navigate through the records.
the deleted record still appears.

what should i do to delete from the record set



if you want to add records, click create record, enter the fields and click save button

Note:Please do not enter last field(long) while creating record. it wil not be saved, and gives error. there is some problem.


thank you

[MikeTheBike]

Hi

Unfortunately IT policy does not permit me to down load files, so I cannot look at this, but hopefully someone else will. Paticularly as I cannot simulate your problem, the deleted record just disapear when the recordset is moved.

Sorry cannot be of more help.


MTB

[Stewart Ross]

Hi ravindarjobs. The form's code module in the example database does not compile - there is a type mismatch in the line

RecordNavigationControl6.ControlSource = rsInfotable

of your OpenConnection_Click sub. The controlsource cannot be a recordset type - it should be a tablename, query name or SQL statement of some kind. Anyway, it has nothing to do with the error being investigated.

Commenting that line out leads to another error - as we have no access to the data source that is still specified for your connection object. For test purposes you need some local data source that we can access too - such as a table within the current database.

You should make sure that any database you attach is free from errors before you attach it - encountering avoidable mistakes like this is not helpful to anyone who is spending time looking at your application...

Like MTB, I can see no obvious error in your delete routine. I wonder if you have the relevant permissions on the underlying table, however. Don't be fooled by the message saying the record is deleted - at least if what you are referring to is your own messagebox call saying the record is deleted - does not mean the system did delete the record.

I would suggest that you set up a test table within your application itself, open that table in the ADO recordset instead of the remote target, and verify as MTB has done that the code deletes a target record. If so, it is clearly a permissions issue that is preventing you from deleting the record in the remotely-connected data source.

-Stewart

[NeoPa]

I know MTB mentioned it earlier, but I'm wondering if you caught it, as I've seen no further mention of it. After deleting a record, the recordset keeps the record holder until it is requeried.

Either requery the recordset when necessary, or recode to handle this and skip past such records where found.