Member Avatar for dr.maqbulur.rahman

Hi,
In a VB.net programme I have OLEDB table1 (loaded from Access DB). In a loop I am changing the values in some columns (say Col5). Depending upon the new values I need to make and run a query table say (col5=xx) and replace the this query table by the table1 and continue the loop. Could you please write few lines of codes for me?

Thanks,

Maqbul

  • 2 Contributors
  • 1 Reply
  • 325 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by rproffitt

Dani AI

Generated

As suggested, here is a concise, practical pattern that fits ’s description: modify Col5 in an in-memory DataTable loaded from Access, build a filtered "query table" where Col5 = 'xx', then continue processing using that filtered set. The key ideas shown below: (1) perform modifications on the underlying DataTable so changes can be persisted, (2) get a stable selection (DataRow array or DataView) before modifying, and (3) replace the working set by the filtered set for the next iteration.

Imports System.Data.OleDb

Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\MyDb.accdb;"
Using cn As New OleDbConnection(connStr)
    Dim sql As String = "SELECT * FROM Table1" ' include the primary key column
    Dim da As New OleDbDataAdapter(sql, cn)
    Dim cb As New OleDbCommandBuilder(da)     ' needs PK for auto-generated UPDATE
    Dim dt As New DataTable()
    da.Fill(dt)

    Dim current() As DataRow = dt.Select() ' start with all rows (or an initial selection)

    While current.Length > 0
        ' Modify Col5 on the selected rows (replace the example logic with your business rules)
        For Each r As DataRow In current
            If CStr(r("Col5")) = "trigger" Then
                r("Col5") = "xx"
            Else
                r("Col5") = "other"
            End If
        Next

        ' Replace working set with the rows that now match Col5 = 'xx'
        current = dt.Select("Col5 = 'xx'")  ' strings in single quotes; numeric no quotes; dates use #2017-07-13#
    End While

    da.Update(dt) ' persist changes back to Access if required
End Using

Notes and troubleshooting tips:

  • Use DataView.RowFilter or DataView.ToTable() if you prefer a view-based approach and want to avoid copies. CopyToDataTable() throws if there are zero rows, so check rows.Length first.
  • Filter syntax matters: 'value' for strings, no quotes for numbers, #date# for dates.
  • Never modify a DataRowCollection while enumerating it directly; select into a DataRow array (as above) or iterate by index.
  • For persisting with OleDbCommandBuilder, the SELECT must include a primary key; otherwise updates will fail. For per-iteration DB queries prefer a parameterized SELECT ... WHERE Col5 = ? to fetch only the rows you need.
  • If performance is a concern on large sets, push the filter to the database (SQL) instead of repeatedly copying large tables in memory.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.