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

ExecuteScalar returns the first row, first column value. Your first column is CandidateNumber and the first row is the first row entered in your table. Obviously not what you want. This is why it isn't working. — Mary, Dec 19 '18 at 21:07

b.pell · Answer 1 · 2018-12-20T02:17:54.037

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.

thanks so much for your inquiry but to answer your question i want to show the total votes of the selected candidate so only one record (per candidate)..and also i wanted to display it on a textbox so i add novtextbox.text = totalVotes to your code but the result is 0..Here is the link to the pic : [link](https://imgur.com/a/n6QQDgr) — Alief Achmad, Dec 19 '18 at 18:23
@AliefAchmad Where is your CandidateNumber being selected? Just substitute a variable containing the selected number for the 1 as the parameter value. — Mary, Dec 19 '18 at 21:16

count the specific record from access in vb.net

1 Answers1