Error on updating recordset

[Casiopea]

Hi!
I'm trying to update a row with a recorset but I get the error -2147217864(80040e38) error on updating based on queries. The row to be updated couldn't be found.
However the row is there because I can read the values of the row.
rsmod.ActiveConnection = Inicio.conexion
rsmod.CursorLocation = adUseClient
rsmod.LockType = adLockPessimistic
rsmod.CursorType = adOpenDynamic
rsmod.Source = "SELECT * FROM judprue WHERE " & _
"codigo_p = '" & codigo_ant & "' AND " & _
"fecha_jud = '" & Format(fecha_ant, "dd/mm/yyyy") & "' AND " & _
"juzgado = " & juzgado_ant & " AND " & _
"causa_prod = '" & prod_ant & "' AND " & _
"causa_num = " & num_ant & " AND " & _
"causa_agno = " & año_ant

If juznum_ant <> 0 Then
rsmod.Source = rsmod.Source & " AND juz_num = " & _
juznum_ant
End If
On Error GoTo err_modificar
rsmod.Open
rsmod.Fields("codigo_p") = codigo_key
rsmod.Fields("fecha_jud") = Format(fecha_key, "dd/mm/yyyy")
rsmod.Fields("juzgado") = juzgado_key
If Not IsNull(rsmod.Fields("juz_num")) And (juznum_key <> 0) Then
rsmod.Fields("juz_num") = juznum_key
End If
rsmod.Fields("causa_prod") = Me.prod_key
rsmod.Fields("causa_num") = Me.num_key
rsmod.Fields("causa_agno") = Me.año_key
If mskhora.ClipText <> "" Then
rsmod.Fields("hora") = hora
Else
rsmod.Fields("hora") = Null
End If
If optjuisi.Value Then
rsmod.Fields("juicio") = "S"
Else
rsmod.Fields("juicio") = "N"
End If
If optdijuisi.Value Then
rsmod.Fields("dietas_jud") = "S"
Else
rsmod.Fields("dietas_jud") = "N"
End If
If optdidesi.Value Then
rsmod.Fields("dietas_desp") = "S"
If km <> 0 Then
rsmod.Fields("km") = km
End If
Else
rsmod.Fields("dietas_desp") = "N"
End If
If optsuspsi.Value Then
rsmod.Fields("suspendido") = "S"
Else
rsmod.Fields("suspendido") = "N"
End If
If optdilsi.Value Then
rsmod.Fields("diligencias") = "S"
Else
rsmod.Fields("diligencias") = "N"
End If
If optjorsi.Value Then
rsmod.Fields("en_jornada") = "S"
Else
rsmod.Fields("en_jornada") = "N"
End If
If Trim(cmbobs.Text) <> "" Then
rsmod.Fields("observ") = UCase(Trim(cmbobs.Text))
Else
rsmod.Fields("observ") = Null
End If
respuesta = MsgBox("¿Desea modificar el registro?", vbYesNo)
If respuesta = vbYes Then
'On Error GoTo err_modificar
Call rsmod.Update
Call MsgBox("REGISTRO MODIFICADO", vbInformation)
Else
Call MsgBox("MODIFICACIÓN CANCELADA", vbInformation)
rsmod.CancelUpdate
End If

If I make try to update just one field Call rsmod.Update ("juicio","S") it works. But I do not want to make it this way because there are a lot of fields to be modified.
On the other hand, I have made this operation (select, open recordset, update) with other tables an it worked.
Thank you

 

[HughLerwill]

cas,

I'm not an ado expert (at all) and the following may not help but these lines bothered me;

rsmod.CursorLocation = adUseClient
rsmod.LockType = adLockPessimistic
rsmod.CursorType = adOpenDynamic

In SQL Server 2000 I'm pretty sure Dynamic cursors and Pessimistic Locks are only available when the cursor is located on the server(adUseServer). Ado will not return errors for illegal combinations but sets what it considers the most suitable legal combination. Check the values of these properties after you have set them all to see if you are actually getting what you are setting.

regards Hugh

 

[tsdragon]

try adding

rsmode.Update

before you start updating fields


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[Casiopea]

I have checked the fields one by one, and have discovered that I can update the record without problems, until I try to include in the update the field "hora". The variable hora is type Variant, but I force it to have the format "hh:mm", then "hh:nn", then directily with a "09:30" before I assigned it to the field, and it did not work. The table format for that field is "Datetime hour to minute", defined on the database engine of informix.

In other forms, if I make the update directly with sql "UPDATE..." it works, but in this case is not efficient due to the amount of fields of the table.
I'm a deseparated.....programmer