Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB and SQL Server 7

Posted on 2002-04-29
9
Medium Priority
?
302 Views
Last Modified: 2008-03-10
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
Comment
Question by:hcougar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 2

Expert Comment

by:JamesT
ID: 6978657
You can use objRS.MoveNext and RS_objRS.MovePrevious
0
 

Author Comment

by:hcougar
ID: 6978685
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
 
LVL 2

Expert Comment

by:JamesT
ID: 6978724
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:hcougar
ID: 6978738
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
 
LVL 2

Expert Comment

by:JamesT
ID: 6978766
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
 

Author Comment

by:hcougar
ID: 6979109
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6979114
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
 

Expert Comment

by:pponvannan
ID: 6979781
I Think The data object is in declared within the commond button. try to declare at general on form.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 800 total points
ID: 6980629
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question