Solved

VB and SQL Server 7

Posted on 2002-04-29
9
295 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
Comment Utility
You can use objRS.MoveNext and RS_objRS.MovePrevious
0
 

Author Comment

by:hcougar
Comment Utility
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
Comment Utility
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
 

Author Comment

by:hcougar
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 2

Expert Comment

by:JamesT
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now