How to Update or Replace records all DBF in one folder based on fields in all table with SQL via ODBC in VB.NET

useruc781 0
Viewed 58

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

TABEL DBF Error code

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
dbf odbc visual-foxpro vb.net sql
1 Answers

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
Cetin Basoz0
Related