How Do I Use The Count Function To Determine Occurence In A Value In A Column Using Vb.Net

Question

1.00/5 (1 vote)

See more:

VS2013

i have an access database with a particular column which contains the words "male" and "female", and would like to know the number of times each word reoccurs. and display the value in a label. thanks @all

Posted 15-Aug-14 21:58pm

kallymore

Updated 15-Aug-14 22:00pm

v2

Add a Solution

4 solutions

Solution 1

SQL

SELECT COUNT(CASE WHEN col='MALE' THEN 1 ELSE 0 END) AS Males, COUNT(CASE WHEN col='FEMALE' THEN 1 ELSE 0 END) AS Females FROM MyTable

Posted 15-Aug-14 23:08pm

OriginalGriff

Comments

kallymore 16-Aug-14 6:23am

please note that i am a beginner please i need to know how to read the column and extract the number of times the word "male" is entered and the number of times the word "female" is entered in the column. e.g.
i have a database "Student_DB" with a table "tblstdnt" which contains a column "gender" the column gender contains words like "male" and "female" of like 100 rows. kindly help me please i need to submit this soonest thanks.

OriginalGriff 16-Aug-14 6:33am

That is what that query *does* - it returns exactly that.
All you have to do is include that as the SELECT statement in your existing code...

kallymore 16-Aug-14 7:43am

thanks for your answer i think i need to be spoonfed. still didnt get it

OriginalGriff 16-Aug-14 7:54am

Start by running the query: either via a DataReader or a DataAdapter - whichever you are more familiar with.
You can then use the returned row as two columns - "Males" and "Females" - which contains the numbers.
Then just set the label.Text property:
myLabel.Text = string.Format("{0} males and {1} females", malesCount, femalesCount)

kallymore 16-Aug-14 10:23am

thanks for your answer i think i need to be spoonfed. still didnt get it

OriginalGriff 16-Aug-14 10:29am

OK: how would you normally read a value (any value) from your database?

kallymore 16-Aug-14 10:21am

thanks for your answer i think i need to be spoonfed. still didnt get it

kallymore 16-Aug-14 10:56am

Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Kallymore-PC\Documents\Visual Studio 2012\Projects\Log_In\Log_In\Loginthings.accdb")
Dim cmd As New OleDbCommand("select * from UserInfo where Matric=?", cn)
cmd.Parameters.AddWithValue("Matric", Matric2.Text)
cn.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader
If reader.HasRows = True Then
reader.Read()

OriginalGriff 16-Aug-14 11:00am

OK - that has a SELECT statement in it, so all you need to do is replace the SELECT with the new version, and remove the Parameter line.
The reader will then be able to retrieve the counts as two columns named "Males" and "Females"

kallymore 16-Aug-14 11:59am

am i supposed to replace "col" with the column name? cos thats what i did

OriginalGriff 16-Aug-14 13:31pm

Yes - and your table name probably isn't "MyTable" either!

Solution 2

Another approach

select count(col) male, 0 female from myTable where col = 'male'
union  
select 0 male, count(col) female from myTable where col = 'female

Posted 15-Aug-14 23:48pm

Abhinav S

v2

Comments

kallymore 16-Aug-14 7:48am

please am just a beginner in vb.net programming how do i display the result in a label and please does the word "col" mean that i should replace with corresponding column name?

Solution 4

thanks everyone

VB

<pre lang="vb"> 
Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Kallymore-PC\Documents\Visual Studio 2012\Projects\count\count.accdb")
        Dim cmd As New OleDbCommand("select count (Gender) as i from userinfo where Gender = 'male'", cn)
        cn.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader
        If reader.HasRows = True Then
        End If
        reader.Read()
        Label59.Text = reader(0).ToString()

this did the trick

Posted 17-Aug-14 4:59am

kallymore

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kgmmurugesh · Accepted Answer · 2014-08-16T00:25:00

Solution 3

select count(field_name) as malecount from myTable where filed_name = 'male'

Posted 16-Aug-14 0:25am

kgmmurugesh