conversion from vb to vb.net

[tomhil]

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

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

 

[jmcilhinney]

Forget the code you currently have. Explain what it is that you want to accomplish and we can suggest the best way to do that. Maybe it will resemble the old code and maybe it won't. It doesn't really matter. What matters is that the new code does what you want to do and as efficiently as possible.

 

[tomhil]

thank you for you reply. and you are right .. it does make more sense to explain what i'm trying to do rather than show the old code. Basically what i'm trying to do is count the number of locations and regions for each st_id and store them in separate dictionary objects in the formation of dictionary(ID, Number of locations). I'm just beginning to learn VB.net so i am not sure how to go about it.

 

[cjard]

Looks like you need a query like:

SELECT
  all.st_id,
  ct.regLoc,
  COALESCE(ctr.ct, 0)
FROM
(SELECT st_id FROM sch_reg_loc WHERE class = 0) all
LEFT OUTER JOIN
(SELECT CASE class WHEN 1 THEN 'Reg' ELSE 'Loc' END as regLoc, st_id, count(*) as ct FROM sch_reg_loc WHERE class IN (1,2) GROUP BY class, st_id) ctr
ON all.st_id = ctr.st_id

Doing all the counting in the database is a) using the database for what it was designed, b) massively more performance efficient than running multiple queries

Read the DW4 link in my signature, section on Creating a Simple Data App - it teaches how to connect to a db and download data to a local container. Once you read the tutorial, the following advice makes sense:

if you make st_id the key column of your DataTable, then you can invoke the method: Dim myRo as SOMENAMEDataRow = myDatatable.FindByst_id(PUT_THE_STID_HERE)
This works like a dictionary, returns you a DataRow, that you can ask whether its a Reg or a Loc: If myRo.RegLoc = "Reg" Then... and you can use the count

If you use access database it may not understand ansi sql like COALESCE.. Google for alternatives, e.g. coalesce alternative in Access SQL - Stack Overflow