Adding & deleting Recordsets-HELP!

I am trying to save some data in a table, but I keep getting this error:
"Run-time error '3251': Object provider is not capable of performing requested operation"

VB debug stops at the line that says "rs.AddNew"

Is there something I need to add at the code below that would solve this problem? Thanks a lot. Also, I need to find out how to delete a recordset.

######################### CODE ###########################
Private Sub SaveData() 'this procedure saves the data.
Dim response As Integer
Dim rs As ADODB.Recordset
Dim cm As ADODB.Command
Dim p As ADODB.Parameter

Set cm = New ADODB.Command
Set cm.ActiveConnection = cn
Set rs = New ADODB.Recordset
rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset
cm.CommandType = adCmdStoredProc
cm.CommandText = "sp_get_atm_info"
Set p = cm.CreateParameter("@AtmId", adChar, _ adParamInput, 10, cbAtmId.Text)
cm.Parameters.Append p
Set rs = cm.Execute

If rs.EOF Then
response = MsgBox("adding new", vbOKOnly)
rs.AddNew
End If

With rs
.Fields("AtmId") = cbAtmId.Text
.Fields("Name") = txtCompany.Text
.Fields("Address") = txtAddress.Text
End With

rs.Update
rs.Close
Set rs = Nothing
End Sub

Ok here is what I did. I think you were forgetting to Open your recordset. Even though you might of opened in your form load or another sub you have to open it in the sub you want add, delete, or update. When you add, delete, or update the database should automatically save the data in the table.

Private Sub cmdAdd_Click()
On Error GoTo ErrorAdd
Set rsMachineOperator = New ADODB.Recordset
rsMachineOperator.Open "Select * From MachineOperator", cnn, adOpenDynamic, adLockOptimistic

num_recs = rsMachineOperator.RecordCount

If num_recs > 1 Then
rsMachineOperator.MoveFirst
rsMachineOperator.MoveLast
Else
If num_recs < 1 Then
rsMachineOperator.AddNew
rsMachineOperator!OperName = txtOpername
rsMachineOperator!Shift = txtShift
rsMachineOperator!Date = txtDate
rsMachineOperator.Update
End If
End If

txtOpername = ""
txtShift = ""
txtDate = ""
txtOpername.SetFocus

ExitAdd:
Exit Sub

ErrorAdd:
MsgBox Err.Number & vbCrLf & Err.Description, , "Error"
Resume ExitAdd

This is how you can do the delete for records

Private Sub cmdDelete_Click()
On Error GoTo ErrorDelete
Set rsMachineOperator = New ADODB.Recordset
rsMachineOperator.Open "Select * From MachineOperator", cnn, adOpenDynamic, adLockOptimistic

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

txtOpername = ""
txtShift = ""
txtDate = ""

ExitDelete:
Exit Sub

ErrorDelete:
MsgBox Err.Number & vbCrLf & Err.Description, , "Error"
Resume ExitDelete
End Sub

Hope this helps you out!!

Good Luck
aja
End Sub

It seems to not work. It keeps giving me the same error message. Any more ideas? By the way, I'm using VB6 SP4 and SQL Server 7.0.

I got this far then information stopped, ....IS ANY BODY OUT THERE... its only good manners to say "I'm sorry, i can no longer help.."

hi all ( sorry for the earlier thread gsc1ugs, I forgot all about it and haven't checked my mail)

what Database are you using, becuase All I can find on the msdn site relating your problem is with Oracle. If you are using oracle reply back and I'll tell you what I found

all the best

Ian

Hello again..

still got this annoying problem

db = access 2000
front end asp
script = vbscript (obviously)

here's a low-down.

set rst = cn.Execute (strQuery)
set rst = createObject("ADODB.Recordset")
rst.ActiveConnection = cn
rst.AddNew
FOR i = 1 to rst.Fields.Count -1
rst.Fields(i).Value = rst.Fields(i).Name
NEXT
rst.update

db name = contact

I open on the fly so i want to add on the fly.

<form name="aspweb/MLAdmin.asp?pageid=view" action="/mlintranetsite/MLAdmin.asp?pageid=view" method="POST">
<table border=1 bordercolor="#e70f7f" cellspacing=1 cellpadding=1 rules=box>
<tr>
<%
FOR i = 1 to rst.Fields.Count -1
Response.Write "<td><B><font size=1>" & rst(i).Name & "</font></B></td>"
NEXT
%>
</tr>
<tr>
<%
FOR i = 1 to rst.Fields.Count -1
Response.Write "<td><input type=text name=" & rst(i).Name & " value=" & rst(i).Name & "></B></td>"
NEXT
%>
</tr>
</table>
<br><br>
</form>

Hi gsc1ugs an all

The only thing I could as I said was with Oracle, but give it a go any way

try opening the recordset clientside using adUseClient(or 3)
as the cursor.

If this doesn't work then I'm out of ideas

all the best

Ian

How does one do this?

I get this

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/mlintranetsite/MLAdmin.asp, line 126

how can i make sure the object is open.

SQL = "SELECT * FROM tblItem WHERE ItemName LIKE '%" & Replace(Keyword, "'", "''") & "%'"

Where tblItem is declared, is it possible to use a wildcard to list all tables within the database?

Gary

The way you need to set out you connection is as follows


Set cn = createObject("ADODB.Connection")
Set rst = createObject("ADODB.Recordset")

cn.Open "your connection string"
rst.Open strQuery,cn,3 'open as clientside cursor


rst.AddNew
FOR i = 1 to rst.Fields.Count -1
rst.Fields(i).Value = rst.Fields(i).Name
NEXT
rst.update


Ian

from th4e famous words
" i dont believe it..!"

ADODB.Recordset error '800a0cb3'
Object or provider is not capable of performing requested operation.

/mlintranetsite/MLAdmin.asp, line 146

rst.addnew this error is pointing to

I'm buggered..

I'm buggered too, that's me out of ideas. Only one very stupid question left (forgive me for asking) The database isn't set to read only ?

Ian

...just kiddin, database is fine, i have a search facility
using the RECORDSET THING.. sorry... and that works fine its adding the stuff that does'nt work.

Gaz.