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
Advertisement
Answer
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