Visual Basic Group by

Question

Need help...

Table Name - cashbook
Field name - pname & amount

Examble

pname              amount
Antony              1500
Jose                3000
Antony              3500

need to display in msflexgrid like belo

Antony - 5000
Jose   - 3000

below my code is not working....can anybody help me

Rec.open "select  from pname,sum(amount) as amt from cashbook group by pname order by pname,var,adopendynamic,adlockoptimist"

While rec.eof=false
    Pymnt.rows=pymnt.rows+1
    Pymnt.textmatrix(pymnt.rows -1,0)=rec!pname
    Pymnt.textmatrix(pymnt.rows -1,1)=amt
    Rec.movenext
Wend
Rec.close

That's not VB.NET, the question isn't about VB at all but rather SQL, and I just had to do quite a bit of work to clean up your post. Not a good start, I'm afraid. Also, saying that something doesn;t work is never adequate. You should spend some time in the Help Center to learn how to write a good question. — jmcilhinney, Nov 14 '19 at 05:04
I can't remember that far back, but is your Rec.open correct? Should it just be the sql that is quoted, rather than the options? — TomC, Nov 14 '19 at 06:10
Please clarify "not working". There is an example with MSHFlexGrid (Microsoft Hierarchical FlexGrid) at https://stackoverflow.com/questions/58236410/how-to-get-records-by-using-foreign-key-in-one-table-using-primery-key-another-t — Smith, Nov 14 '19 at 08:07

score 1 · Answer 1 · answered Nov 14 '19 at 12:28

I made a few fixes to your code and came up with the following:

Public Sub Test()
   rec.Open "select pname, sum(amount) as amt from cashbook group by pname order by pname", var, adOpenDynamic, adLockOptimistic

   While Not rec.EOF
      pymnt.Rows = pymnt.Rows + 1
      pymnt.textmatrix(pymnt.Rows - 1, 0) = rec.Fields("pname").Value
      pymnt.textmatrix(pymnt.Rows - 1, 1) = rec.Fields("amt").Value
      rec.MoveNext
   Wend

   rec.Close
End Sub

The fixes I made include:

Constructing a proper SQL statement
Supplying the correct parameters to the Open method
Assuming var is an open Connection object
Correcting the spelling of adLockOptimistic
Changing how the data is accessed in the loop

Visual Basic Group by

1 Answers1