EMERGENCY!-Maybe General forum can answer this!!!

I am having a problem regarding recordsets and it's properties. I am trying to save an edited recordset(not a new one). For some reason, when the rs.open command line is executed it changes the setting of rs.CursorType from adOpenKeyset to adOpenStatic and when I try to assign the edited values to the recordset, I get a BOF/EOF error(run-time error '3021'). Below is code, and any suggestions would be greatly appreciated.

Code:

Private Sub UpdateData() ' This procedure updates data of current customers
Dim response As Integer
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = "Exec sp_get_atm_info " & cbAtmId.Text
Set rs = New ADODB.Recordset
rs.Source = strSQL
Set rs.ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.CursorType = adOpenKeyset

rs.Open , , , , adCmdText
response = MsgBox("saving fields", vbOKOnly)
rs.Fields("AtmId") = cbAtmId.Text
rs.Fields("Name") = txtCompany.Text
rs.Fields("Address") = txtAddress.Text
rs.Update
rs.Close

Set rs = Nothing
End Sub

I think the error is because you don't execute the rs.AddNew method. See below. Also I changed some of the syntax.

Code:

---

Private Sub UpdateData() ' This procedure updates data of current customers
Dim response As Integer
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "Exec sp_get_atm_info " & cbAtmId.Text

rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText 'I don't know that the adCmdText part is necessary

response = MsgBox("saving fields", vbOKOnly)

With rs
    .AddNew 'you do not have the AddNew command in your code
    .Fields("AtmId") = cbAtmId.Text
    .Fields("Name") = txtCompany.Text
    .Fields("Address") = txtAddress.Text
    .Update
    .Close
End With

Set rs = Nothing
End Sub

---

actually now that I think about it, you can do the same thing with less code, using the ADO connection .Execute command, like this:

Code:

---

Private Sub UpdateData() ' This procedure updates data of current customers

cn.Execute "INSERT INTO TableName (AtmID, Name, Address) " & _
  "Values (" & cbAtmID.Text & ", '" & txtCompany.Text & ", '" & _
  txtAddress.Text & "')"

End Sub

---

I don't know if recordsets from stored procedures are updatable, but you could try this:

Code:

---

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set cmd = New ADODB.Command
'Use a previously created connection
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_get_atm_info " & cbAtmId.Text
Set rs = cmd.Execute
response = MsgBox("saving fields", vbOKOnly)

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

Set rs = Nothing
set cmd = Nothing

---

Sorry, Frans but if I use command, I cannot update a recordset because using cm.Execute always returns adForwardOnly and AdReadOnly.

Drew_Dog, do I really have to add rs.AddNew to a recordset that is already in the table. I just want to edit it not make a copy of it or add a new record. I hope this helps you to better understand.

Thank you for all your suggestions and hope to hear from you soon.

Okay, sorry, I misunderstood. There are several ways you can do that.

1) Have you considered using the ADODC control and binding the fields to it? Then you make modifications directly and don't need any code.

2) if you do not want to do #1, you can execute an SQL command to update the table. In my opinion, this is the best way to proceed because it is very fast and uses very few resources. I assume that you have an autonumber column as the primary key, e.g. IdCompany. Store that value in a varible and then execute the command:

Code:

---

cn.Execute "UPDATE MyTable SET AtmID = " & cbAtmId.Text & _
  "And NAME = '" & txtCompany.text & "' And ADDRESS = '" & _
  txtAddress.Text & "' WHERE IdCOMPANY = " & myIdValue

---

3) Or if you have to use an ADO recordset, select only the necessary record, execute a .MoveFirst command, and then modify it.

The reason that you got Error 3021 is that a recordset has a "pointer" that indicates the location. The pointer moves as you navigate through the recordset, and whenever you instruct the recordset to modify a field, it modifies only the current field. When you open a recordset, even if it has records the pointer will be at position 0. That means that it is not pointing to any record and will not do so until you tell it to.

Using the ADO recordset, and assuming that you use an Autonumber value and can assign it to a variable, the code will look like this:

Code:

---

Private Sub UpdateData() ' This procedure updates data of current customers
Dim rs As New ADODB.Recordset
Dim strSQL As String

strSQL = "Select * From MyTable Where IdCOMPANY = " & myIdValue
'strSQL = "Exec sp_get_atm_info " & cbAtmId.Text

rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText 'I don't know that the adCmdText part is necessary

If rs.RecordCount = 0 Then
    MsgBox "Error! Cannot find record.", vbCritical
Else
    With rs
        .MoveFirst
        .Fields("AtmId") = cbAtmId.Text
        .Fields("Name") = txtCompany.Text
        .Fields("Address") = txtAddress.Text
        .Update
    End With
   
    MsgBox "Record succesfully update.", vbInformation

End If

rs.Close
Set rs = Nothing
End Sub

---

Thank you Drew, I used option #2 and it worked. Now, I need something that will distinguish between a new record and an edited/existing record. Basically, I would like something like this:

if (statement here) then
AddData (sub)
else
UpdateData (Sub)
end if

Any suggestions out there?


YOU KNOW WHAT? NEVER MIND. I HAVE JUST FIGURED IT OUT.

THANK YOU EVERYONE FOR ALL YOUR HELP.


[Edited by vbuser1976 on 10-30-2000 at 12:53 PM]