Deleting Problems?

[jeffro]

I'm working with access 97 and vb6. In my vb6 program I have a commandbutton called cmddelete. The button was working fine until I imported data into the database which I'm working with. I click on the delete button and the information clears out in vb6, but not with in my database. Here's the code I'm using, maybe you guys can see what I'm doing wrong.

Set rsMachineInfo = New ADODB.Recordset
rsMachineInfo.Open "Select * From MachineInfo", cnn, adOpenDynamic, adLockOptimistic

With rsMachineInfo
.Delete
.MoveNext
If .BOF Then .MoveLast
End With

txtMachineno = ""
txtMachineDesc = ""
cmbDivision = ""

Thanks!

You need to do a recordset.UpdateBatch to commit your deletes (otherwise they're just flagged for deletion in your recordset). Another way to do deletes (with less code) is with SQL on an Execute, like:

conYourConnection.Execute "DELETE FROM TableName"

or

conYourConnection.Execute "DELETE FROM TableName WHERE FieldName=Value"


Paul


[Edited by PWNettle on 11-22-2000 at 11:02 AM]

[Corby]

First Test The Connection You Opened

If Not rsMachineInfo.Eof Then
rsMachineInfo.Delete
End If

Corby Nichols

[jeffro]

PWNettle

Ok I did try using the SQL on an excute. This is what I have but I'm getting an error saying I'm missing operator 'Machineno = 0 MachineDesc=0 Division=0'. Am I doing somethingwrong in the statement?

cnn.Execute "DELETE FROM MachineInfo WHERE Machineno = 0 MachineDesc=0 Division=0"

Thanks jeffro

[sebs]

You're missing AND :
cnn.Execute "DELETE FROM MachineInfo WHERE Machineno = 0 AND MachineDesc=0 AND Division=0"

[marex]

Hi

syntax error

cnn.Execute "DELETE FROM MachineInfo WHERE Machineno = 0 AND MachineDesc=0 AND Division=0"


cheers
Ray

[jeffro]

Now I'm getting a data type mismatch error on the SQl statement.

jeffro

[sebs]

Are you sure that MachineInfo,Machineno and MachineDesc
are all number type in your database, if there text:

MachineInfo WHERE Machineno = '0' AND MachineDesc='0' AND Division='0'"

Sorry, our email server is being worked on so I'm not receiving email notifications on posts! Grrr.

What sebs and marex said!

Paul :P

[jeffro]

Ok I did try what sebs said because the fields are text..sorry! But the record is still not deleting. Any other ideas guys?

jeffro

[sebs]

Make sure that in your database your 3 field are 0,
because it will never delete.

Do you have relationship in your dbase??

[jeffro]

sebs,

I do have relationships between my tables (one to many). I did check the default values for the fields in question(if that is what you meant) and I set them to zero. The data is still not deleting from the database.

jeffro

What sebs is saying is that you must have a record that matches your DELETE conditions...you must have a record where Machineno=0, MachineDesc=0, and Division=0 or nothing will be deleted. It doesn't matter what the default values for these fields are. What matters is that you have a record in your table that matches the conditions in your SQL statement.

Hope this helps,
Paul

[jeffro]

But I don't want to delete a specific record. I just want to be able to delete a record the user does not want in the database table anymore. Is there a way to just delete record the user picks randomly?

jeffro

[sebs]

Well you should delete it with the primary key.

I agree with sebs (again). Hopefully your table has a primary key. No matter how your user selects a record (to delete) you must have some way of identifying the record (usually with its PK). You'd just need to delete the record using it's PK like:

conWhatever.Execute "DELETE FROM TableName WHERE PK_Field=" & PKValue  '  For numeric PKs.

or

conWhatever.Execute "DELETE FROM TableName WHERE PK_Field='" & PKValue & "'"  '  For text PKs.

Paul

[jmcswain]

If your form has a data control on it:

Me.datacontrol.Recordset.Delete
Me.datacontrol.Recordset.MoveFirst

If you do not have a data control, follow PWNettle's advice. After deleting the record, refresh your form by calling the function or sub you use to populate it.

[jeffro]

Hey thanks everyone for helping me and putting up with all the questions. I guess I'm not very good with sql statements. I didn't need to use the sql statement either. the datacontrol code that jmcswain provided worked great!

Thanks again everyone!!!

jeffro