adding/deleting records in vb

[jeffro]

I keep getting an Run time error '3021':
Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record.

The error occurs on rs.delete, does anyone have any ideas why this is happening?

Thanks aja

[damonous]

You are not pointed to a record in the recordset. If you are deleting more than one record, then delete one and then move to the next record to delete. Otherwise, you are returning and empty recordset and do not have a record to delete.

[jeffro]

This is the code I have:

Private Sub DeleteData() 'This procedure deletes the data.
Dim response As Integer
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "sp_get_atm_info 'cbAtmId.Text'", cn, adOpenDynamic,
adLockOptimistic, adCmdText

response = MsgBox("Are you sure you want to delete?", vbYesNo + vbInformation, _Title:="Delete?")

If response = vbYes Then
rs.Delete
cbAtmId.Text = ""
txtCompany.Text = ""
txtAddress.Text = ""
End If

rs.Update
rs.Close
Set rs = Nothing

End Sub

Any suggestions?

[Toot]

I think it's to do with what you're actually doing...
Is this "sp_get_atm_info" a stored procedure? If so I presume it's a stored proc that returns a recordset?
I'm not sure that you can do it like that with ADO, try using an ADO.Command object with an ADO.Parameter object, then "Set" your recordset to the Command.Execute result rather then creating a new RS and opening it.

If that sounds like a load of rubbish let me know and I'll post a quick extract of some of my code that does a similar thing - I'm in a bit of a rush just at the sec...

Toot

[jeffro]

Could you please send me an example of your code Toot.
Thanks!

[Toot]

' assumes there's an open connection called cn_INOUT
' and an argument called sFeedName_IN which is the stored proc parameter

Dim cm As ADODB.Command, rscm As ADODB.Recordset
Dim iResult As Integer

Set cm = New ADODB.Command

With cm
     .ActiveConnection = cn_INOUT
     .CommandType = adCmdStoredProc
     .CommandText = "SP_GetFeedID"
     .Parameters.Append .CreateParameter("FeedName", adVarChar, adParamInput, Len(sFeedName_IN), sFeedName_IN)
     Set rscm = .Execute
     ' now look at the resulting recordset
     If Not (rscm.BOF And rscm.EOF) Then
          ' look at the "FormatID" field
          iResult = ACInt(rscm.Fields("ID"))
     Else
          iResult = -1
     End If
     CloseRelease rscm
End With

Set cm = Nothing

CloseRelease() is a simple func that closes and releases an object:

Public Sub CloseRelease(ByRef obj_INOUT As Object)

obj_INOUT.Close
Set obj_INOUT = Nothing

End Sub

Hope that helps - ask if it's still not clear!

Toot