Adding a counter in vb.net for an sql-query output

Question

Right now I'm working on a simple program in vb.net. Choose 2 dates in the daytimepicker, press a button for an sql-query and save the result somewhere. This works fine, but now I want to add an counter to the query. One column just has the numbers 1,2,3 in it and I want to count each one. Little example with an "output" `

x x x 1 -> count 1
x x x 1 -> count 2
x x x 2 -> count 1
x x x 1 -> count 3
x x x 2 -> count 2
x x x 3 -> count 1

` This counter then should be added to the datatable which is the result of the query, as a new column. Basicly an bill number.

I've tried to do this in the read-function of the sql, but I'm just getting errors and I dont know why..still beta I guess.

    Do While myReader.Read()
        results = results & myReader.GetString("0") & ";" &
            myReader.GetString("1") & ";" &
            myReader.GetString("2") & ";" &
            myReader.GetDecimal("3") & ";" &
            myReader.GetInt32("4") & ";" &
            myReader.GetInt32("5") & ";" &
 ** thats basicly my reader, which puts an ; between everything because im saving it as .csv

        If myReader.GetInt32("5") = 1 Then
            nmb1 = nmb1 + 1
        **put nmb1 in the output"
        ElseIf myReader.GetInt32("5") = 2 Then
            nmb2 = nmb2 + 1
        **put nmb1 in the output"
        ElseIf myReader.GetInt32("5") = 3 Then
            nmb3 = nmb3 + 1
        **put nmb1 in the output"
        Else
            MsgBox("u failed")
        End If '& vbLf
    Loo

p

I'll hope you understand my question, because I'm kinda new to programming. If you need any further explonation, please ask.

you could do it with analytical function if your database support it. — the_lotus, Jan 26 '18 at 15:43
can we see the query part of the code. how you retrieve the data — Andam, Jan 26 '18 at 15:46

Mary · Answer 1 · 2018-01-29T20:27:36.247

0

Turn on OptionStrict. It will help to avoid type mismatches and odd bugs.

Dim results String = ""
Dim nmb1 As Integer
Dim nmb2 As Integer
Dim nmb3 As Integer

Do While ...
...

        Select Case myreader.GetInt32("5")
            Case 1
                nmb1 += 1
                results &= $"; {nmb1}"
            Case 2
                nmb2 += 1
                results &= $"; {nmb2}"
            Case 3
                nmb3 += 1
                results &= $"; {nmb3}"
            Case Else
                MessageBox.Show("u failed")
        End Select
        'do you wand to put a hard return between records? 
        results &= vbCrLf
Loop

edited Jan 29 '18 at 20:27

answered Jan 26 '18 at 22:29

Mary

14,926
3
18
27

How do I add this to my existing myReader-function? The results I'm getting are kinda odd right now.. `` – AVK Jan 29 '18 at 10:56
@AVK this would go in in place of your If...End If – Mary Jan 29 '18 at 20:26

Adding a counter in vb.net for an sql-query output

1 Answers1