Weird behavior with SQL UPDATE statement on DBF

i'm really stuck on a apparently simple statement.

I'm working on a DBF table. When I open the file in a program called "DBF Viewer 2000", it states that the file is a "FoxBase+/dBASE III PLUSE, no memo".

If i run this VB.NET code

        Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & opt.path & "\" & ";Extended Properties=dBase III;"
        Dim oledbAdapterIns As New OleDbDataAdapter()
        Dim InsConnection As New OleDbConnection(ConnectionString)
        oledbAdapterIns = New OleDbDataAdapter()
        InsConnection = New OleDbConnection(ConnectionString)
        Try
            InsConnection.Open()
            Dim s 开发者_JS百科As String
            s = "UPDATE LIBRIAZ SET LIBRIAZ.ULTNUM=6 WHERE LIBRIAZ.REGISTRO='CW' AND LIBRIAZ.ANNO='2011' AND LIBRIAZ.CHIAVE='ORD_REG'"
            oledbAdapterIns.InsertCommand = New OleDbCommand(s, InsConnection)
            Dim rows As Integer = oledbAdapterIns.InsertCommand.ExecuteNonQuery()
        Catch ex As Exception
            Debug.Writeline(ex.Message)
        End Try
        oledbAdapterIns.Dispose()
        InsConnection.Dispose()

it runs without error BUT not updating the table.

Now comes the weird: if I remove one of the three conditions

LIBRIAZ.REGISTRO='CW' AND LIBRIAZ.ANNO='2011' AND LIBRIAZ.CHIAVE='ORD_REG'

for example

LIBRIAZ.ANNO='2011' AND LIBRIAZ.CHIAVE='ORD_REG'

the UPDATE works, setting LIBRIAZ.ULTNUM to 6

It's not a matter of which condition i remove, because it's enough to remove one of them to get the UPDATE working.

It's days I'm trying to figure why this behavior... now i'm pretty desperate.

Anyone have a clue?

---

The first thing that hits me is your first comment about "no memo". When a dbf file has a memo field (like a blob in other SQL to store larger non-standard data elements like embedded pictures, Excel files, XML, or whatever). The files "sets" would be such as

YourTable.dbf  (main table)
YourTable.cdx  (index file associated with just this table)
YourTable.fpt  (memo file associated with just this table)

The main .dbf has a file pointer system that is an offset in bytes in the .FPT file where it's contents are stored (if applicable). So, I would see about finding that missing / corrupted .fpt file first.

Next, in your update statement, since you are only doing a direct update and not a correlated update based on another table, you don't need to ALIAS every field... Try stripping down to...

s = "UPDATE LIBRIAZ SET ULTNUM = 6 WHERE REGISTRO='CW' AND ANNO='2011' AND CHIAVE='ORD_REG'"