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 :(