How to Update or Replace records all DBF in one folder based on fields in all table with SQL via ODB-CodePudding

How to Update or Replace records all DBFs in one folder based on fields across tables with SQL via ODBC in VB.NET?.

If there is another best solution. I mean the fields in all database tables in one folder named "ITM" So I want to change or update e.g. "TEST R 1000" to TEST RC 1000 LTP" If there are other alternatives through other database providers please recommend. Example screenshot I attach below so example I have DBF table A,B,C,D,E I want to change "TEST R 1000" to TEST RC 1000 LTP" or that I marking yellow

Imports System.Data.Odbc

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
    
    Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click
        Dim connection As New OdbcConnection
        Dim strConnection As String
        Dim pathDBF As String
        Dim strSQL As String
        pathDBF = "D:\DBF"
        strConnection = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & pathDBF
        connection.Open()
    End Sub
End Class

CodePudding user response：

When it is VFP, there is no official ODBC driver after version 6.x. There are ODBC drivers for Sybase (ADS) but as far as I know they are not for free unless it is local data (it has been over 10 years I have last tried them).

Instead you should use VFP OLEDB driver - VFPOLEDB

Next, tablenames A,B,C,...E is not good because of historical reasons, let's instead assume tableNames are A_,B_, ...E_ then you could do what you are asking for easily:

import System.Data.OleDb
Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click

    Dim pathDBF = "d:\DBF"

    Using cn = New System.Data.OleDb.OleDbConnection($"Provider=VFPOLEDB;Data Source={pathDBF}")
        Dim tableNames = "A_, B_, C_, D_, E_"
        cn.Open()

        For Each tableName In tableNames.Split(","c)
            New OleDbCommand($"update {tableName.Trim()} set Item=""TEST RC 1000 LTP"" where Item==""TEST R 1000""", cn).ExecuteNonQuery()
        Next

        cn.Close()
    End Using

End Sub

EDIT: Updated version for old VB versions:

import System.Data.OleDb
Private Sub Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Update.Click

Dim pathDBF = "d:\DBF"

Using cn = New System.Data.OleDb.OleDbConnection($"Provider=VFPOLEDB;Data Source={pathDBF}")
    Dim tableNames = "A_, B_, C_, D_, E_"
    cn.Open()

    For Each tableName In tableNames.Split(","c)
            Using cmd = New OleDbCommand(String.Format("update {0} set Item=""TEST RC 1000 LTP"" where Item==""TEST R 1000""", tableName.Trim()), cn)
                cmd.ExecuteNonQuery()
            end using
        Next
        cn.Close()
    End Using
End Sub