How to count the number of items in SQL Table with VB.Net?

Question

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

Thanks

Posted 12-Jun-13 18:20pm

Member 9989624

Add a Solution

Comments

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)
conn.Open()
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
reader.Close()
conn.Close()

_Maxxx_ 13-Jun-13 0:31am

That's a completely different question.
YOu index an array
reader(7)
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.

2 solutions

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)

Ron Beyer · Answer 1 · 2013-06-12T18:24:00

VB

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.

stupid code maker · Answer 2 · 2013-06-14T20:39:00

Solution 2

change that combo.text to combo.selecteditem

Posted 14-Jun-13 20:39pm

stupid code maker