count the specific record from access in vb.net

Question

So i am making a voting program but i am stuck right now. I have searched for solutions and i have followed them precisely but still no result.

Basically i want to count the total occurrence for each specific record from access in visual basic.

For example i have the candidate number 1 voted by three persons and candidate 2 by 7 persons, i want to show this voting result in a textbox but somehow it always shows me the wrong number

So here is my code:

Dim TotalVotes As Integer
myCommand = New OleDbCommand("SELECT CandidateNumber, COUNT (*) FROM Student_Voting GROUP BY CandidateNumber", dbconn)
TotalVotes = myCommand.ExecuteScalar
NovTextBox.Text = TotalVotes

myCommand.Dispose()
myReader.Close()

This query here gives a result of the first candidate number not the total votes for selected candidate number:

SELECT CandidateNumber, COUNT (*) FROM Student_Voting GROUP BY CandidateNumber

I have tried this too but still wrong result:

SELECT COUNT(CandidateNumber) AS NoVotes FROM Student_Voting GROUP BY CandidateNumber

I don't know what's the problem here, it's suppose to be simple but yet.

If anyone could help i'd very much appreciate it.

Thanks in advance

Answer 1

First of all, the initial query you're running would return more than one record if you had more than one candidate. ExecuteScaler returns only the first column of the first row.

The question is, do you want all of the records or do you just want one person's record? If you just want one person's record you'll need add a WHERE clause to your sql statement to specify on that candidate.

If you want all of the records it would look something like this:

    Using myCommand = New OleDbCommand("SELECT CandidateNumber, COUNT (*) AS CountValue FROM Student_Voting GROUP BY CandidateNumber", dbconn)
        Using dr = myCommand.ExecuteReader
            ' Loops over all the canidate counts one by one.
            While dr.Read
                Dim totalVotes As Integer = CInt(dr("CountValue"))
                Dim candidateNumber As String = dr("CandidateNumber")
            End While
        End Using
    End Using

If you want only one record then you can use ExecuteScaler something like this:

    Using myCommand = New OleDbCommand("SELECT COUNT (*) AS CountValue FROM Student_Voting WHERE CandidateNumber = @CandidateNumber GROUP BY CandidateNumber", dbconn)
        myCommand.Parameters.Add("@CandidateNumber", OleDbType.VarChar).Value = "1"
        Dim totalVotes = CInt(myCommand.ExecuteScalar)
    End Using

I don't know the actual types in your database so you would need to tweak the OleDbType to fit your table setup and perhaps some of the casts.