Click here to Skip to main content
15,790,723 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi all,

How to find the number of items in a row for a condition in table.

ex:- "select count(EmployeeName) from employee where group = '"& Trim(Combobox1.text) &"'"

this is not giving the number of employees with group specified in combo box.

please help

Member 9989624 13-Jun-13 0:27am    
I use the below code, this says error Index was outside the bounds of the array.. Please help

Dim connString As String = "Provider=SQLOLEDB.1;Password=$impadmins123;Persist Security Info=True;User ID=simp;Initial Catalog=Employee Information;Data Source=SRAVI6"
Dim conn As New OleDb.OleDbConnection(connString)
Dim sql As String = "SELECT count(EmployeeName) FROM AgentShiftRoster1 where Group1 ='" & Trim(ComboBox1.Text) & "'"
Dim cmd As New OleDb.OleDbCommand(sql, conn)
Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader()
While reader.Read()
If reader(7) = Date.Today Then
TextBox1.Text = reader(2).ToString()
End If
End While
_Maxxx_ 13-Jun-13 0:31am    
That's a completely different question.
YOu index an array
and it is telling you 7 is outside the bounds of the array
So your sql isn't returning 8 columns.
Look at your sql - it is only returning one column (i.e. count(EmployeeName))and, indeed, only one row
What are you trying to achieve?
Ron Beyer 13-Jun-13 0:31am    
Of course it is... you are only selecting one column from the database, and then you are trying to read columns #7 and #1, which don't exist. If you want to select all the rows, then don't do the count in the same statement, it should be two, one for the count, and the other for the data.
_Maxxx_ 13-Jun-13 0:28am    
What IS it giving?
select count(*) from table where condition should give you a row count (I think if you had employee names of Null your count might differ)
Have you tried hard-coding the SQL (or just running what you think should be run) to see the output
e.g. execute "Select count(*) from employee where group = 'YourGroupName'"
If that sql works, then there's probably something different being passed by your code.
If that doesn't work, then look at your table - does that group ectually exist in that column?
Member 9989624 13-Jun-13 0:55am    
In Sql i get the correct value.

SELECT Count(*) AS EmpCount FROM employee where group = '" & Combobox1.SelectedItem.ToString() & "'"

Comboboxes are confusing, the .Text property may not be what is actually selected, so use the SelectedItem. SelectedItem is an object so you have to convert it to a string.

Also note that the "=" operation in SQL is case sensitive, so if the case does not match exactly, then the count will not be right. To do a case insensitive search, use "LIKE".

BTW, (as my comment says) you should learn about SQL injection attacks, and how to use parameters in SQL statements to avoid them.
Share this answer
Ron Beyer 13-Jun-13 0:26am    
You should also learn how to use parameters in SQL statements to avoid SQL injection attacks.
change that combo.text to combo.selecteditem
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900