Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

VB and SQL Server 7

I am bringing in information from SQL Server 7 to my Visual Basic application.  I am currently having no problem bringing in the data but would like to go to the next record or the previous record in the table.  How do I do that?  Here is what I have for opening up the table and getting my information:

Private Sub cmdOpen_Click()

   'Use the standard Windows icon
   Me.Icon = Nothing

   'Declare database objects
   Dim objConn As New ADODB.Connection
   Dim objRS As New ADODB.Recordset
   Dim objErr As ADODB.Error

   'Establish a database connection using SQL Server Authentication
   objConn.Open "DSN=Jamie;UID=sa;PWD="

   'Check the state of the connection to ensure we are connected
   If objConn.State = adStateOpen Then

      'Open a recordset
      objRS.Open "Sysusers", objConn, adOpenForwardOnly, _
                 adLockReadOnly, adCmdTable
      If Not objRS.EOF And Not objRS.BOF Then
         ProductName.Text = objRS!Uid
         ProductID.Text = objRS!Status
      End If
      objRS.Close

   Else

      'Display all errors
      For Each objErr In objConn.Errors
         Debug.Print objErr.Description
      Next

   End If

   'De-reference the database objects
   Set objRS = Nothing
   objConn.Close
   Set objConn = Nothing
   
End Sub
0
hcougar
Asked:
hcougar
  • 3
  • 3
  • 2
  • +1
1 Solution
 
JamesTCommented:
You can use objRS.MoveNext and RS_objRS.MovePrevious
0
 
hcougarAuthor Commented:
What else do I have to add to get it to move next or previous?  I know I can't just add that one line.  I'm a little new at this.
0
 
JamesTCommented:
Well, do you want a button to move to next and previous? If so you have to put the move next and previous button on the form. I have four buttons on a form: Move First, Move Next, Move Previous, and Move Last. Here are their click event subs:

Private Sub cmdFirst_Click()
    DataEnvironment1.rsTBL_COPY.MoveFirst
End Sub

Private Sub cmdLast_Click()
    DataEnvironment1.rsTBL_COPY.MoveLast
End Sub

Private Sub cmdNext_Click()
    With DataEnvironment1.rsTBL_COPY
        .MoveNext
        If (.EOF) Then
          .MoveFirst
        End If
    End With
End Sub

Private Sub cmdPrevious_Click()
    With DataEnvironment1.rsTBL_COPY
        .MovePrevious
        If (.BOF) Then
          .MoveLast
        End If
    End With
End Sub

Does that help?

By the way, we all were a little new with something that's why questions are ok.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hcougarAuthor Commented:
What does this represent: DataEnvironment1.rsTBL_COPY

Are you declaring DataEnvironment1 at the top of the page?  What is rsTBL_COPY?

Yes, I have put buttons on my application.

Thanks.
0
 
JamesTCommented:
Sorry, that code is specific to what I am doing. You would be using the Recordset you created:

objRS.MoveNext

So it would be like this:

Private Sub cmdNext_Click()
   With .objRS
       .MoveNext
       If (.EOF) Then
         .MoveFirst
       End If
   End With
End Sub

Something like that. I am using a data environment so it may differ from how you are doing it. It should be fairly similar I would think. I kinda new to this too. You may have issues with Persistance from where you put the openrecordset command. You have it written to execute all that code at once including closing the recordset. You would most likely want to put the open recordset bit on the form open event and then dereference the recordset when the form is closed. Otherwise, with the code you have above, it will open the recordset, close and dereference the recordset and you can't control movement with movenext and moveprevious.
0
 
hcougarAuthor Commented:
I've tried everything.  Here is my code so far:

Private Sub cmdNext_Click()
         
Dim objConn As New ADODB.Connection
Dim objRS As New ADODB.Recordset
Dim objErr As ADODB.Error

   'Establish a database connection using SQL Server Authentication
   objConn.Open "DSN=Jamie;UID=sa;PWD="

   'Check the state of the connection to ensure we are connected
   If objConn.State = adStateOpen Then

      'Open a recordset
      objRS.Open "Sysusers", objConn, adOpenForwardOnly, _
                 adLockReadOnly, adCmdTable
                 
    With .objRS
        .MoveNext
            If Not (.EOF) Then
                .MoveFirst
            End If
    End With
    End If
   
End Sub

I get the following error:

Invalid or unqualified reference.  

It is refering to .objRS  before the .MoveNext
0
 
Anthony PerkinsCommented:
If all you want to do is cycle through all your rows then the following will work:

'Open a recordset
objRS.Open "Sysusers", objConn, adOpenForwardOnly, _
                adLockReadOnly, adCmdTable
Do While Not objRS.EOF
   Debug.Print objRS!Uid, objRS!Status
   objRS.MoveNext
Loop
objRS.Close

This will give you the best performance, however one problem you are going to encounter if you attempt to go to the previous record is that you have defined the recordset as adOpenForwardOnly.  So in order to be able to do a MovePrevious, you must change the the CusrorType to something other than adOpenForwardOnly.

Anthony

0
 
pponvannanCommented:
I Think The data object is in declared within the commond button. try to declare at general on form.
0
 
Anthony PerkinsCommented:
Change your code as follows:

Private Sub cmdNext_Click()
       
Dim objConn As ADODB.Connection        ' Remove the New
Dim objRS As ADODB.Recordset           ' Remove the New
Dim objErr As ADODB.Error

Set objConn = New ADODB.Connection
 'Establish a database connection using SQL Server Authentication
objConn.Open "DSN=Jamie;UID=sa;PWD="

'Check the state of the connection to ensure we are connected
' No need for this, it will error out if it cannot open(unless you are using On Orror Resume Next, which is not a good thing)
'  If objConn.State = adStateOpen Then

   'Open a recordset
  'Change the CursorType to something other than adOPenForwardOnly
   objRS.Open "Sysusers", objConn, adOpenDynamic, _
                adLockReadOnly, adCmdTable
               
   With objRS     'Change this
       .MoveNext
           If Not .EOF Then
               .MoveFirst
           End If
   End With
'   End If
   
End Sub

Also, look at my previous post.
Anthony
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now