Problem updating record with Ado using VB6.0 & Informix 1

[Longia]

Hi everybody,
Here is my problem, I am using Visual Basic 6.0 and Informix 7.3 as backend for development.When I use the following code to update a specific record it updates all the records with null field.

'Insert telephone number into caa43640 (Attorney table)

Set rcdsetTwo = New ADODB.Recordset
strSql = "select caa43640008 from caa43640 where trim(caa43640001) = '0011234'"
rcdsetTwo.ActiveConnection = conn
rcdsetTwo.LockType = adLockOptimistic
rcdsetTwo.Source = strSql
rcdsetTwpen
With rcdsetTwo
If Not .EOF Then
!caa43640008 = Trim(txtPhone.Text)
.Update
End If
End With
rcdsetTwo.Close

(Note: caa43640001 is the only primary key in the table.)

Is this a Informix driver driver problem or what? I tried with some other drivers too but it doesn't work.
Can anybody help me with this.

 

[MarkSweetland]

It doesn't seem possible that all the records would be updated with a NULL field from your posted code, but stranger things have happened. I have not used Informix but just as a suggestion, since you have only one primary key, try using the ADODB Command instead of the recordset and see if there is a difference:

Dim cmd As New ADODB.Command
    Dim strSQL As String
    strSQL = "UPDATE caa43640 " & _
             "set caa43640008 ='" & Trim(txtPhone.Text) & "' " & _
             "where trim(caa43640001) = '0011234'"
    With cmd
        .CommandType = adCmdText
        .CommandText = strSQL
        .ActiveConnection = conn
        .Execute
    End With
    Set cmd = Nothing

Mark

 

[Longia]

Yes, that is the only option I have but when I tried the same code (i.e. with recordset) with all columns in my sql statement it worked fine.

Anyway thanks for help Mark