i've am converting the following code to vb.net
I tried nesting multiple datasets and it seems to work but the code gets really messy.
what is the best way to do this? Thanks
I tried nesting multiple datasets and it seems to work but the code gets really messy.
what is the best way to do this? Thanks
VB.NET:
Dim myRS As ADODB.Recordset
Dim myREG As ADODB.Recordset
Dim myLOC As ADODB.Recordset
Dim mySQL As String ' string for sql statement
Dim rStatus As String
Set myRS = New ADODB.Recordset
Set myREG = New ADODB.Recordset
Set myLOC = New ADODB.Recordset
myRS.CursorLocation = adUseClient
myREG.CursorLocation = adUseClient
myLOC.CursorLocation = adUseClient
mySQL = "SELECT st_id FROM sch_reg_loc WHERE class = 0;"
myRS.Open mySQL, m_objDBConn, adOpenKeyset, adLockOptimistic
If (Not myRS.EOF) And (Not myRS.BOF) Then
mySQL = "SELECT st_id, COUNT(*) AS regNum FROM sch_reg_loc WHERE class = 1 GROUP BY st_id;"
myREG.Open mySQL, m_objDBConn, adOpenKeyset, adLockOptimistic
mySQL = "SELECT st_id, COUNT(*) AS locNum FROM sch_reg_loc WHERE class = 2 GROUP BY st_id;"
myLOC.Open mySQL, m_objDBConn, adOpenKeyset, adLockOptimistic
myRS.MoveFirst
While Not myRS.EOF
myREG.Filter = "st_id = '" & myRS.Fields("st_id").Value & "'"
If Not myREG.EOF Then
myDicReg.Add CStr(myRS.Fields("st_id").Value), myREG.Fields("regNum").Value
Else
myDicReg.Add CStr(myRS.Fields("st_id").Value), "0"
End If
myLOC.Filter = "st_id = '" & myRS.Fields("st_id").Value & "'"
If Not myLOC.EOF Then
myDicLoc.Add CStr(myRS.Fields("st_id").Value), myLOC.Fields("locNum").Value
Else
myDicLoc.Add CStr(myRS.Fields("st_id").Value), "0"
End If
myRS.MoveNext
Wend
End If