none
Loop Recordset in VB.NET

    Question

  • Hi,

     

    In MS Access I use the following code to loop through a recordset. Can anyone please provide an example or point me in the direction of how to open and loop through a recordset in VB.NET? Thanks

     

    Sample>>

     

    Dim cnndb As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    strSQL = "Select * From MyTable Where City = 'Chicago' Order By City"
    rs.Open strSQL, cnndb, adOpenKeyset, adLockOptimistic

     

    Do Until rs.EOF
     MyString = rs.fields("State")

      rs.fields("Country") = "USA"

     rs.Update

    Loop

    rs.close

     

    rs.Open "AnotherTable", cnndb, adOpenKeyset, adLockOptimistic

     

    For X = 1 to 10

     rs.AddNew

      rs.fields("ThisField") = X

     rs.Update

    Next X

    rs.close

    cnndb.close

     

    Wednesday, August 15, 2007 4:20 AM

All replies

  • Code Snippet

    Public Class Form1

    'Add a reference to adodb

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim cnndb As New ADODB.Connection

    Dim rs As New ADODB.Recordset

    Dim strSQL As String = "Select * From MyTable Where City = 'Chicago' Order By City"

    rs.Open(strSQL, cnndb, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

    Do Until rs.EOF

    Dim MyString As String = CStr(rs.Fields("State"))

    rs.Fields("Country").Value = "USA"

    rs.Update()

    Loop

    rs.close()

    rs.Open("AnotherTable", cnndb, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

    For X As Integer = 1 To 10

    rs.AddNew()

    rs.Fields("ThisField").Value = X

    rs.Update()

    Next X

    rs.close()

    cnndb.close()

    End Sub

    End Class

     

     

    Wednesday, August 15, 2007 4:43 AM
  • Hi - Thanks for the code sample.

     

    How do you set the value of cnndb to point to the access database?

     

    In Access you simply point to the "CurrentProject.Connection"

     

    Thanks

     

     

    Wednesday, August 15, 2007 5:47 AM
  • Actually I figured it out..

     

    Thanks for your help...

     

    Wednesday, August 15, 2007 6:15 AM