Count of Rows Returned
-
I have this: With Me.daNumCourses.SelectCommand .CommandText = "SELECT Section.Section_ID, Section.InstructorID, Instructor.ID FROM Section INNER JOIN Instructor ON Section.InstructorID = Instructor.ID AND Section.InstructorID = '" & Me.instr.ID & "'" intCount = .ExecuteNonQuery() End With Me.dsNumCourses.Clear() Me.daNumCourses.Fill(Me.dsNumCourses) Me.txtNumberCourse.Text = intCount I keep getting -1 so what am I doing wrong? Thanks
-
I have this: With Me.daNumCourses.SelectCommand .CommandText = "SELECT Section.Section_ID, Section.InstructorID, Instructor.ID FROM Section INNER JOIN Instructor ON Section.InstructorID = Instructor.ID AND Section.InstructorID = '" & Me.instr.ID & "'" intCount = .ExecuteNonQuery() End With Me.dsNumCourses.Clear() Me.daNumCourses.Fill(Me.dsNumCourses) Me.txtNumberCourse.Text = intCount I keep getting -1 so what am I doing wrong? Thanks
-
I just want to get the count of the classes that a certain instructor teaches. I tried the .count property and that counts all of the rows in a particular table. Thanks for your reply. If you have a suggestion on how to do that please post back.
-
I just want to get the count of the classes that a certain instructor teaches. I tried the .count property and that counts all of the rows in a particular table. Thanks for your reply. If you have a suggestion on how to do that please post back.
Why don't you have your SQL as:
SELECT COUNT(*) FROM ....
Also, the way you build your SQL is suscepatable to a SQL Injection Attack and you should try and use parameterised queries where ever possible. You can read more on how to improve the security of your application here: SQL Injection attacks and tips on how to prevent them[^]
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
-
Why don't you have your SQL as:
SELECT COUNT(*) FROM ....
Also, the way you build your SQL is suscepatable to a SQL Injection Attack and you should try and use parameterised queries where ever possible. You can read more on how to improve the security of your application here: SQL Injection attacks and tips on how to prevent them[^]
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
How do you return the number of rows from the query? In other words if I have a variable intCount how do I set intCount to the "count" of the query? Thanks for your reply. We've noticed a few security holes in our application in a few places and we are fixing those right now.
-
How do you return the number of rows from the query? In other words if I have a variable intCount how do I set intCount to the "count" of the query? Thanks for your reply. We've noticed a few security holes in our application in a few places and we are fixing those right now.
Dim cmd As SqlCommand
cmd = SqlCommand.New()
cmd.CommandText = "SELECT COUNT(*) FROM MyTable WHERE someColumn=@someValue"
cmd.Connection = myConnection
Dim numRows As Int
numRows = cmd.ExecuteScalar()numRowswill contain the number of rows that match the given filter (i.e. what is filtered in the WHERE clause).COUNT()is an aggregate function that returns a count, whenCOUNT(*)is used it returns the number of rows. If you useCOUNT(columnName)then it will count the number of rows where the column value is not null. Does this help?
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
-
Dim cmd As SqlCommand
cmd = SqlCommand.New()
cmd.CommandText = "SELECT COUNT(*) FROM MyTable WHERE someColumn=@someValue"
cmd.Connection = myConnection
Dim numRows As Int
numRows = cmd.ExecuteScalar()numRowswill contain the number of rows that match the given filter (i.e. what is filtered in the WHERE clause).COUNT()is an aggregate function that returns a count, whenCOUNT(*)is used it returns the number of rows. If you useCOUNT(columnName)then it will count the number of rows where the column value is not null. Does this help?
My: Blog | Photos "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
This is the correct way. ExecuteNonQuery should be used for Insert/Update/Delete statements. If the CommandText is "SELECT" statment, then it will always return -1. When it is DML statement, then it will return the number of rows affected by that DML statement.