OK, this on has been bothering me for a few days, and I cannot find anything related to it in the forums.
I have a need to query a database table for distinct values, then using each value, build another query to get some specific information from the same table. My first recordset looks great, returning about 30 rows. I query the table a second time, and I get the data expected. However…when I step to the next record of the first recordset I encounter EOF. If I don’t open the second recordset, I don’t get EOF until 30 records later in my loop. Here is the actual test code (apologies for the rather cryptic SQL SELECT statements, but I wanted to show valid code):
-------------- START OF CODE -------------------
’ Test nested recordsets
Dim strQuery As String
Dim sLine As String
Dim rsQC As RecordSet
Dim rsLines As RecordSet
'Connect to the ODBC database
app.dbcQC = New ODBCDatabase
app.dbcQC.DataSource = “ColorQC”
If not app.dbcQC.Connect Then
’ Get the list of line numbers
strQuery = “SELECT DISTINCT lineno FROM color_qc ORDER BY lineno”
rsLines = app.dbcQC.SQLSelect(strQuery)
If (not app.dbcQC.Error) Then
If rsLines <> Nil Then
While Not rsLines.EOF sLine = app.trimNulls(rsLines.IdxField(1).StringValue.Trim) ' Get the info for the specified line strQuery = "SELECT etos(color_qc.dtstart), etos(color_qc.dtstop), color_qc.spinlot, color_qc.color, color_qc.colornum, etos(color_qc.pounds), etos(color_qc.letdown), etos(color_qc.islabelchange), etos(color_qc.ismakeup), etos(color_qc.isapproved), etos(color_qc.iscos), color_qc.stage FROM color_qc WHERE color_qc.lineno = '" + sLine + "' ORDER BY dtstart" rsQC = app.dbcQC.SQLSelect(strQuery) rsQC.Close rsLines.MoveNext wend end
’ Close the connection
----------------------- END OF CODE ------------------
The database in question is a DB2 variant called Recital running on Solaris 10. Recital Software supplies the ODBC driver. I thought it might be the ODBC driver, so I ginned up a quick test using VB6 and the nested loops worked flawlessly.
I may be missing something in the documentation, but I haven’t seen anything that even hints at this. I tried using two different connections, and while messy, it worked fine on Windows 8 and Windows XP; it crashes when opening the second connection on Windows 7. (Pull hair here.)
Can someone please enlighten me on this finer point of recordset access? I have used this approach many times over the years in other languages, and would hate to give up my bad habits this late in life.