End of records in a database

DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: End of records in a database

  1. #1
    Join Date
    Nov 2004

    End of records in a database

    I have a VB program that is oping a recordset and looping until I run out. My problem is I do not know how to tell I am at the end of the records. Right now I just get an error and jump out. Here is the code:

    Dim cn As New ADODB.Connection
    Dim recSet As New ADODB.Recordset
    Dim ConnectionString As String
    Dim strSQL1 As String, strSQL2 As String
    Dim i As Integer
    Dim lvItem As ListItem
    Dim sa As String
    Dim sAssetFound As String
    Dim j As Integer

    On Error GoTo ErrorHandler2

    Screen.MousePointer = vbHourglass

    'Define connection string to connect to the database
    ConnectionString = "PROVIDER=MSDASQL;dsn=Calibration Data;uid=LogInOut;pwd=loginout;"
    cn.ConnectionString = ConnectionString

    j = 1
    sAssetFound = "1" 'just something to start
    If cn.State = adStateOpen Then

    If cn.State = adStateOpen Then
    Do Until sAllTheModels(j) = ""
    Do Until sAssetFound = ""
    On Error GoTo GetNextModel
    strSQL1 = "SELECT * FROM mt.Inventory A " & _
    "WHERE A.I4203='" + sAllTheModels(j) + "'" & _
    "AND A.I4230=0"
    recSet.Open strSQL1, cn, adOpenDynamic, adLockOptimistic 'open for read / write using SQL string # 3
    sAssetFound = CStr(recSet.Fields("I4201"))

    recSet.Fields("I4254") = sAllTheData(j) 'change the Cal lab Std cal time
    recSet.Fields("I4230") = 1 'change the integer
    recSet.Update 'now update the database
    recSet.Close 'close
    GetNextModel: DoEvents
    recSet.Close 'close ?
    j = j + 1
    MsgBox "The connection could not be made!", vbCritical, "Error"
    GoTo ExitHandler2
    End If

    My problem is on the line: recSet.Open...... If I am at the end of the records I just get an error and jump out. What I need is a way to tell if I am at the end before this so that I can continue the first do loop.

  2. #2
    Join Date
    Nov 2003
    Portland, OR
    I'm not sure I follow what your code is doing, but I've usually used the .EOF property to test whether I'm at the end of the recordset:
    Do Until rs.EOF
        ' process current record
    Phil Weber

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Nov 2004
    That fixed it. Thanks for the input.

Similar Threads

  1. Replies: 8
    Last Post: 03-08-2003, 06:25 PM
  2. Re: Could Someone help reply to JIM
    By DVanRoekel in forum VB Classic
    Replies: 1
    Last Post: 08-11-2002, 02:48 AM
  3. Replies: 0
    Last Post: 07-12-2002, 08:36 AM
  4. How do I detect an FTP timeout?
    By Julian Milano in forum VB Classic
    Replies: 2
    Last Post: 08-11-2000, 12:11 PM
  5. How do I detect an FTP timeout?
    By Julian Milano in forum VB Classic
    Replies: 0
    Last Post: 08-10-2000, 09:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center