Get the number of rows in a SQL Server table in VB.NET

Question

There are 10 rows in primary_student_table .

When I execute the following code, the result is -1.

 Dim count As Int16 con.Open() query = "SELECT COUNT(roll) AS rollcount FROM primary_student_table WHERE admityear = 2011 AND batch = 1 " cmd = New SqlCommand(query, con) count = cmd.ExecuteNonQuery MsgBox(count) con.Close() 

What is the problem in the above code?

Answer 1

The solution is to replace

 count = cmd.ExecuteNonQuery 

with

 count = cmd.ExecuteScalar 

As Robert Boubien said in his comments

Answer 2

You should use ExecuteScalar() , not ExecuteNonQuery() , because you are retrieving the value.

 count = Convert.ToInt16(cmd.ExecuteScalar()) MsgBox(count.ToString()) 

• SqlCommand.ExecuteScalar Method

For proper coding

• use using to properly delete an object
• use try-catch block to handle exceptions correctly

Code example:

 Dim connStr As String = "connection string here" Dim query As String = "SELECT COUNT(roll) AS rollcount FROM primary_student_table WHERE admityear = 2011 AND batch = 1" Using conn As New SqlConnection(connStr) Using comm As New SqlCommand() With comm .Connection = conn .CommandText = query .CommandType = CommandType.Text End With Try Dim count As Int16 = Convert.ToInt16(cmd.ExecuteScalar()) MsgBox(count.ToString()) Catch(ex As SqlException) ' put your exception here ' End Try End Using End Using 

Answer 3

  MysqlConn = New MySqlConnection MysqlConn.ConnectionString = "server=localhost;userid=root;password=1234;database=dblms" Dim READER As MySqlDataReader Try MysqlConn.Open() Dim Query As String Query = "Select * from dblms.accounts" COMMAND = New MySqlCommand(Query, MysqlConn) READER = COMMAND.ExecuteReader Dim count As Integer count = 0 While READER.Read count = count + 1 End While MysqlConn.Close() Catch ex As MySqlException MessageBox.Show(ex.Message) Finally MysqlConn.Dispose() End Try 

the value in count will be the number of rows in the table :) hope this helped