Stepping through a record set

[BumbelingIntern]

Hi,
I'm working on an applet that allows a user to step through the results of a query and edit the information.

I have it so that you can click one button to get the image and another to update the record, however, I cannot figure out how to keep the recordset open in a way so that when I click the update button it moves to the next entry.

Any help or insight would be greatly appreciated
Thanks,
Steve

The current code looks like this:
Private Sub GetImageButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetImageButton.Click

FileOpen(1, "C:\Program Files\ADImage\ADImageSetup.txt", OpenMode.Input)
Do Until LineInput(1) = "**ADImageLocation**"
Loop
Dim ADImageLocation As String = LineInput(1)
FileClose(1)

FileOpen(1, "C:\Program Files\ADImage\ADImageSetup.txt", OpenMode.Input)
Do Until LineInput(1) = "**ADImageConnection**"
Loop
Dim ADImageConnection As String = LineInput(1)
FileClose(1)
Dim Area As String
Dim Controller As String
Controller = "No"
Area = AreaBox.Text
CN = New ADODB.Connection
CN.Open(ADImageConnection)
RS = New Global.ADODB.Recordset

Try
RS.Open("SELECT * FROM ImageData WHERE Area = '" & Area & "' and Control = '" & Controller & "' ", CN)

RS.MoveNext()

DateBox.Text = RS.Fields("Date").Value
InitialsBox.Text = RS.Fields("Initials").Value
PartIDBox.Text = RS.Fields("PartID").Value
LotNumberBox.Text = RS.Fields("LotNumber").Value
ProcessBox.Text = RS.Fields("Process").Value
AreaBox.Text = RS.Fields("Area").Value
StageBox.Text = RS.Fields("Stage").Value
UniqueIDBox.Text = RS.Fields("DateTimeIdentifier_UniqueSystemID").Val ue
CommentsBox.Text = RS.Fields("Comments").Value
If RS.Fields("Control").Value = "YES" Then
Thecontrolbox.Text = "Controlled"
Else
Thecontrolbox.Text = "Uncontrolled"
End If

Catch
MsgBox("Record Not Found")

End Try

'Get Next rs

UniqueID = UniqueIDBox.Text
Try
ImageDisplay.Image = System.Drawing.Image.FromFile(ADImageLocation & UniqueID & ".jpeg")
ImageDisplay.Visible = True
ImageDisplay.SizeMode = PictureBoxSizeMode.StretchImage
Catch
MsgBox("Image Not Found")
End Try

End Sub


Private Sub UpdateRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateRecordButton.Click
Dim Area As String
Dim Controller As String
Controller = "No"
Area = AreaBox.Text
'get connection strings from setup file
FileOpen(1, "C:\Program Files\ADImage\ADImageSetup.txt", OpenMode.Input)
Do Until LineInput(1) = "**ADImageLocation**"
Loop
Dim ADImageLocation As String = LineInput(1)
FileClose(1)

FileOpen(1, "C:\Program Files\ADImage\ADImageSetup.txt", OpenMode.Input)
Do Until LineInput(1) = "**ADImageConnection**"
Loop
Dim ADImageConnection As String = LineInput(1)
FileClose(1)

UniqueID = UniqueIDBox.Text

CN = New ADODB.Connection
CN.Open(ADImageConnection)
RS = New ADODB.Recordset
RS.Open("DELETE FROM ImageData WHERE DateTimeIdentifier_UniqueSystemID = '" & UniqueID & "' ", CN)
' CN.Close()
Try
CN = New ADODB.Connection
CN.Open(ADImageConnection)
RS = New ADODB.Recordset
RS.Open("select * from ImageData ", CN, 1, 2)
RS.AddNew()
RS.Fields("DateTimeIdentifier_UniqueSystemID").Val ue() = UniqueID
RS.Fields("Date").Value = DateBox.Text
RS.Fields("Initials").Value = InitialsBox.Text
RS.Fields("PartID").Value = PartIDBox.Text
RS.Fields("LotNumber").Value = LotNumberBox.Text
RS.Fields("Process").Value = ProcessBox.Text
If Thecontrolbox.Text = "Controlled" Then
RS.Fields("Control").Value = "YES"
ElseIf Thecontrolbox.Text = "Uncontrolled" Then
RS.Fields("Control").Value = "NO"
End If

RS.Fields("Area").Value = AreaBox.Text
RS.Fields("Stage").Value = StageBox.Text
RS.Fields("Comments").Value = CommentsBox.Text
RS.Fields("ImageName").Value = UniqueID & ".Jpeg"
RS.Fields("Keywords").Value = UniqueIDBox.Text & " , " & DateBox.Text & " , " & InitialsBox.Text & " , " & PartIDBox.Text & " , " & LotNumberBox.Text & " , " & ProcessBox.Text & Thecontrolbox.Text & " , " & AreaBox.Text & " , " & StageBox.Text & " , " & CommentsBox.Text
RS.Update()
CN.Close()
'if the emergency form isnt open yet, open it!
Catch
MsgBox("There has been a connection error. Please contact your network administrator and try again later.")
End Try
If CurrentErrorNumber = 1 Then
Dim StartEmergency As New Emergency
StartEmergency.Show()
ADILogo.Image = System.Drawing.Image.FromFile("C:\Program Files\ADImage\networkdown.bmp")
End If ' end the 'start emergency if its not open

UniqueIDBox.Text = ""
DateBox.Text = ""
InitialsBox.Text = ""
PartIDBox.Text = ""
LotNumberBox.Text = ""
ProcessBox.Text = ""
Thecontrolbox.Text = Nothing
'AreaBox.Text = ""
StageBox.Text = ""
CommentsBox.Text = ""
ImageDisplay.Image.Dispose()
ImageDisplay.Image = Nothing

'Indicating to the user that the image has been updated.
MsgBox("Image data has been updated", MsgBoxStyle.OkOnly, "Image Data Updated")

End Sub

[BumbelingIntern]

Hey,

I believe I figured this one out. Just needed to create a temporary table.

Private Sub GetImageButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetImageButton.Click
 
        FileOpen(1, "C:\Program Files\ADImage\ADImageSetup.txt", OpenMode.Input)
        Do Until LineInput(1) = "**ADImageLocation**"
        Loop
        Dim ADImageLocation As String = LineInput(1)
        FileClose(1)
 
        FileOpen(1, "C:\Program Files\ADImage\ADImageSetup.txt", OpenMode.Input)
        Do Until LineInput(1) = "**ADImageConnection**"
        Loop
        Dim ADImageConnection As String = LineInput(1)
        FileClose(1)
        Dim Area As String
        Dim Controller As String
        Controller = "No"
        Area = AreaBox.Text
        CN = New ADODB.Connection
        CN.Open(ADImageConnection)
        temp = New Global.ADODB.Recordset
        Try
            temp.Open("SELECT * FROM ImageData WHERE Area = '" & Area & "' and Control = '" & Controller & "' ", CN)
 
            DateBox.Text = temp.Fields("Date").Value
            InitialsBox.Text = temp.Fields("Initials").Value
            PartIDBox.Text = temp.Fields("PartID").Value
            LotNumberBox.Text = temp.Fields("LotNumber").Value
            ProcessBox.Text = temp.Fields("Process").Value
            AreaBox.Text = temp.Fields("Area").Value
            StageBox.Text = temp.Fields("Stage").Value
            UniqueIDBox.Text = temp.Fields("DateTimeIdentifier_UniqueSystemID").Value
            CommentsBox.Text = temp.Fields("Comments").Value
            If temp.Fields("Control").Value = "YES" Then
                Thecontrolbox.Text = "Controlled"
            Else
                Thecontrolbox.Text = "Uncontrolled"
            End If
 
        Catch
            MsgBox("Record Not Found")
 
        End Try
 
        'Get Next rs
 
        UniqueID = UniqueIDBox.Text
        Try
            ImageDisplay.Image = System.Drawing.Image.FromFile(ADImageLocation & UniqueID & ".jpeg")
            ImageDisplay.Visible = True
            ImageDisplay.SizeMode = PictureBoxSizeMode.StretchImage
        Catch
            MsgBox("Image Not Found")
        End Try
 
    End Sub
 
Private Sub UpdateRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateRecordButton.Click
        Dim Area As String
        Dim Controller As String
        Controller = "No"
        Area = AreaBox.Text
        'get connection strings from setup file
        FileOpen(1, "C:\Program Files\ADImage\ADImageSetup.txt", OpenMode.Input)
        Do Until LineInput(1) = "**ADImageLocation**"
        Loop
        Dim ADImageLocation As String = LineInput(1)
        FileClose(1)
 
        FileOpen(1, "C:\Program Files\ADImage\ADImageSetup.txt", OpenMode.Input)
        Do Until LineInput(1) = "**ADImageConnection**"
        Loop
        Dim ADImageConnection As String = LineInput(1)
        FileClose(1)
 
        UniqueID = UniqueIDBox.Text
 
        'Check to see if record is in the database - this way it won't crash on blank or broken entry.
        Dim EntryExists As Boolean = True
        Try
            CN = New ADODB.Connection
            CN.Open(ADImageConnection)
            RS = New ADODB.Recordset
            RS.Open("SELECT * FROM ImageData WHERE DateTimeIdentifier_UniqueSystemID = '" & UniqueID & "' ", CN)
        Catch
            'tell user record isn't in database, turn off errorvariable
            MsgBox("Record not found")
            EntryExists = False
        End Try
        CN.Close()
 
        'if it was there, delete it from the database and write a new entry with the new info.  
        'If EntryExists Then
 
        CN = New ADODB.Connection
        CN.Open(ADImageConnection)
        RS = New ADODB.Recordset
        RS.Open("DELETE FROM ImageData WHERE DateTimeIdentifier_UniqueSystemID = '" & UniqueID & "' ", CN)
        CN.Close()
        Try
            CN = New ADODB.Connection
            CN.Open(ADImageConnection)
            RS = New ADODB.Recordset
            RS.Open("select * from ImageData ", CN, 1, 2)
            RS.AddNew()
            RS.Fields("DateTimeIdentifier_UniqueSystemID").Value() = UniqueID
            RS.Fields("Date").Value = DateBox.Text
            RS.Fields("Initials").Value = InitialsBox.Text
            RS.Fields("PartID").Value = PartIDBox.Text
            RS.Fields("LotNumber").Value = LotNumberBox.Text
            RS.Fields("Process").Value = ProcessBox.Text
            If Thecontrolbox.Text = "Controlled" Then
                RS.Fields("Control").Value = "YES"
            ElseIf Thecontrolbox.Text = "Uncontrolled" Then
                RS.Fields("Control").Value = "NO"
            End If
 
            RS.Fields("Area").Value = AreaBox.Text
            RS.Fields("Stage").Value = StageBox.Text
            RS.Fields("Comments").Value = CommentsBox.Text
            RS.Fields("ImageName").Value = UniqueID & ".Jpeg"
            RS.Fields("Keywords").Value = UniqueIDBox.Text & " , " & DateBox.Text & " , " & InitialsBox.Text & " , " & PartIDBox.Text & " , " & LotNumberBox.Text & " , " & ProcessBox.Text & Thecontrolbox.Text & " , " & AreaBox.Text & " , " & StageBox.Text & " , " & CommentsBox.Text
            RS.Update()
            CN.Close()
            'if the emergency form isnt open yet, open it!
        Catch
            MsgBox("There has been a connection error.  Please contact your network administrator and try again later.")
        End Try
        If CurrentErrorNumber = 1 Then
            Dim StartEmergency As New Emergency
            StartEmergency.Show()
            ADILogo.Image = System.Drawing.Image.FromFile("C:\Program Files\ADImage\networkdown.bmp")
        End If ' end the 'start emergency if its not open
 
        UniqueIDBox.Text = ""
        DateBox.Text = ""
        InitialsBox.Text = ""
        PartIDBox.Text = ""
        LotNumberBox.Text = ""
        ProcessBox.Text = ""
        Thecontrolbox.Text = Nothing
        'AreaBox.Text = ""
        StageBox.Text = ""
        CommentsBox.Text = ""
        ImageDisplay.Image.Dispose()
        ImageDisplay.Image = Nothing
 
        'Indicating to the user that the image has been updated.
        MsgBox("Image data has been updated", MsgBoxStyle.OkOnly, "Image Data Updated")
 
        temp.MoveNext()
 
        DateBox.Text = temp.Fields("Date").Value
        InitialsBox.Text = temp.Fields("Initials").Value
        PartIDBox.Text = temp.Fields("PartID").Value
        LotNumberBox.Text = temp.Fields("LotNumber").Value
        ProcessBox.Text = temp.Fields("Process").Value
        AreaBox.Text = temp.Fields("Area").Value
        StageBox.Text = temp.Fields("Stage").Value
        UniqueIDBox.Text = temp.Fields("DateTimeIdentifier_UniqueSystemID").Value
        CommentsBox.Text = temp.Fields("Comments").Value
        If temp.Fields("Control").Value = "YES" Then
            Thecontrolbox.Text = "Controlled"
        Else
            Thecontrolbox.Text = "Uncontrolled"
        End If
        UniqueID = UniqueIDBox.Text
        Try
            ImageDisplay.Image = System.Drawing.Image.FromFile(ADImageLocation & UniqueID & ".jpeg")
            ImageDisplay.Visible = True
            ImageDisplay.SizeMode = PictureBoxSizeMode.StretchImage
        Catch
            MsgBox("Image Not Found")
        End Try
End Sub