Solved

VB and SQL Server 7

Posted on 2002-04-29
9
297 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 200 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Designer 19 39
string fuctions 4 25
Increment column based of a FK 8 20
SQL - Update field defined as Text 6 16
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

776 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