I am trying to add a coloumn from database into a listbox in VB6.
The following code works. The selected item from this listbox is used to find and add records to the ListBox2.

Using Oracle 11g Express Edition and Visual Basic 6.

Working code: (Displays the artist column from a music database)

Private Sub Command3_Click()
Dim con As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    Dim ConnStr As String
    Dim SQL As String
    ConnStr = "DSN=MusicDB"
    SQL = "Select artist FROM music"
    con.Open ConnStr, "SYSTEM", "PASSWORD"
    rec.Open SQL, con, adOpenStatic, adLockOptimistic
    Do While Not rec.EOF
        List1.AddItem rec.Fields("artist").Value
        rec.MoveNext
    Loop
    rec.Close
    con.Close

End Sub

I am using a string to store the item selected in above listbox

Private Sub List1_Click()
'Selected is a global string
Selected = List1.Text 
End Sub

The following code, which uses the string above to add items from database to ListBox 2, returns the Runtime error ORA 01410 : Invalid ROWID error while reaching the Do While Not loop. (Supposed to display titles which matches the artist selected).

Private Sub Command4_Click()
Dim i As String
i = Selected

Dim con As New ADODB.Connection
    Dim record As New ADODB.Recordset
    Dim ConnStr As String
    Dim SQL As String
    Set con = New ADODB.Connection
    SQL = "select title from music where artist = '" & i & "'"
    ConnStr = "DSN=MusicDB"
    con.Open ConnStr, "SYSTEM", "ChinkyFunky"
    Set record = New ADODB.Recordset
    record.Open SQL, con, adOpenDynamic, adLockOptimistic
     Do While Not (record.EOF)
    List2.AddItem record.Fields("title").Value
    record.MoveNext
    Loop
    record.Close
con.Close
End Sub

Can someone please tell me if I am doing something wrong ? I have been trying to debug this code for days and I still am stuck with the same error :(

Edited 2 Years Ago by Sudha Granger: Addition information added

Try adding the following between line 9 and 10:

record.MoveFirst

Other versions you can try:

record.Open SQL, con, adOpenDynamic, adLockOptimistic

record.MoveFirst

Do While record.EOF = False

    '...

    record.MoveNext
Loop

or

record.Open SQL, con, adOpenDynamic, adLockOptimistic

record.MoveFirst

Do Until record.EOF

    '...

    record.MoveNext
Loop

Edited 2 Years Ago by cgeier

This article has been dead for over six months. Start a new discussion instead.