We help IT Professionals succeed at work.

VB6 - Use recordset to populate dropdown

lakhi
lakhi asked
on
I have an application that I'm adding another form to and in the process, I stumbled across code that worked at one time and now does not. Grrrr...
I'm selecting usernames from a table and using them (or trying to use them)  to populate a ComboBox(the style is set to 2 Dropdown List). Every time I run the code, the reordcount is -1
If I take the same query and run it against the actual table, the records are there.
What am I doing wrong? I can pull data from the database in other areas of the application.
I even tried breaking out the code into a standalone with just the dropdown and still the recordcount is -1
I'm posting my code. I'm connecting to an Oracle database and the connection is already open.
thanks in advance for help
Public Sub Set_Prod_Control_Values()
  Dim i As Integer
  Dim strSQL As String  
  Dim strCat As String
  strCat = "PC"

  Dim rsPC As ADODB.Recordset
  Set rsPC = New ADODB.Recordset
  strSQL = "Select * from USERS WHERE Category = '~'"
  strSQL = Replace(strSQL, "~", strCat)
 
  rsPC.Open strSQL, gDC, adOpenForwardOnly, adLockReadOnly, adCmdText
  If Not rsPC.EOF Then
    Me.cboProdControl.AddItem "Select Name"
    For i = 1 To rsPC.RecordCount-1
      Me.cboProdControl.AddItem (rsPC.Fields!User_Name), i
      rsPC.MoveNext
    Next i
  End If

  rsPC.Close
  Set rsPC = Nothing
End Sub

Open in new window

Comment
Watch Question

Software Engineer
Commented:
Microsoft says that 'Forward only cursors do not return a RecordCount.'
http://support.microsoft.com/kb/194973

Use their solution or try:
Public Sub Set_Prod_Control_Values()
  Dim i As Integer
  Dim strSQL As String
  Dim strCat As String
  strCat = "PC"

  Dim rsPC As ADODB.Recordset
  Set rsPC = New ADODB.Recordset
  strSQL = "Select * from USERS WHERE Category = '~'"
  strSQL = Replace(strSQL, "~", strCat)
 
  rsPC.Open strSQL, gDC, adOpenForwardOnly, adLockReadOnly, adCmdText
  If Not rsPC.EOF Then
    Me.cboProdControl.AddItem "Select Name"
    i = 1
    Do While Not rsPC.EOF
       Me.cboProdControl.AddItem (rsPC.Fields!User_Name), i
       rsPC.MoveNext
       i = i + 1
    Loop
  End If
  rsPC.Close
  Set rsPC = Nothing
End Sub

Open in new window

Author

Commented:
I have tried every setting I know of. This was the original:
rsPC.Open strSQL, gDC,adOpenKeyset, adLockOptimistic, adCmdText

Author

Commented:
Problem was the recordset cursor needed to be set to "adUseClient"
RobinSoftware Engineer

Commented:
Did you even read Microsoft's solution?

#6 mentions:

'Change the CursorLocation to adUseClient and experiment with the different CursorTypes. In all cases the correct record count returns.'
RobinSoftware Engineer

Commented:
See my last comment..
GrahamSkanRetired
Top Expert 2012
Commented:
You could avoid using Recordcount completely
Public Sub Set_Prod_Control_Values()
  Dim i As Integer
  Dim strSQL As String
  Dim strCat As String
  strCat = "PC"

  Dim rsPC As ADODB.Recordset
  Set rsPC = New ADODB.Recordset
  strSQL = "Select * from USERS WHERE Category = '~'"
  strSQL = Replace(strSQL, "~", strCat)
 
  rsPC.Open strSQL, gDC, adOpenForwardOnly, adLockReadOnly, adCmdText
  If Not rsPC.EOF Then
    Me.cboProdControl.AddItem "Select Name"
      Do Until rsPC.EOF
        Me.cboProdControl.AddItem (rsPC.Fields!User_Name)
        rsPC.MoveNext
     Loop
  End If

  rsPC.Close
  Set rsPC = Nothing
End Sub

Open in new window

RobinSoftware Engineer

Commented:
@Grahamskan, that was my solution too. :)