Search code examples
excelvb.netfor-loopoledb

VB.net excel only fills in one item per row

I'm working on an automated compliancy tool and have bumped into the following problem:

I'm generating an excel sheet that currently looks like this (using dummy data because of company data):

enter image description here

but actually should look something like this (not actual data):

enter image description here

the information is being pulled out of a database with the following query in the function called getRightsForPath():

"SELECT DUMPSEC.rights 
 FROM DUMPSEC
 WHERE DUMPSEC.location ='" & sql2 & "'
 AND DUMPSEC.members = '" & sql1 & "'"

and this is the loop that should take care of the rights being added to the right cell:

For i = 1 To groupCounter Step +1
    Dim rights = getRightsForPath(sourceLocation, xlWorksheet.Cells(2, i + 1).Value, xlWorksheet.Cells(Row, 1).Value)

        Do While rights.Read()

            xlWorksheet.Cells(Row, i + 1) = rights("rights")
            //MessageBox.Show(locations("location") & ", " & groupOwnerOf("groupname") & ", " & rights("rights"))

         Loop

Next
Row += 1
Solution

  • okay, figured it out myself, I forgot to put it all in a loop and check the rows to be done (as seen in this code)

    For Row = 3 To locationCount Step +1
    For i = 2 To groupCounter + 1 Step +1
        Dim rights = getRightsForPath(sourceLocation, xlWorksheet.Cells(2, i).Value, xlWorksheet.Cells(Row, 1).Value)
        Do While rights.Read()
            xlWorksheet.Cells(Row, i) = rights("rights")
        Loop
    Next
Next