Get 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 was -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's the problem in the above code?

Answer 1

You should be using ExecuteScalar() rather than ExecuteNonQuery() because you are fetching a value.

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

• SqlCommand.ExecuteScalar Method

For proper coding

• use using statement for proper object disposal
• use try-catch block to properly handle exceptions

Example Code:

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 cmd As New SqlCommand()
        With cmd
            .Connection = conn
            .CommandText = query
            .CommandType = CommandType.Text
        End With
        Try
            conn.Open()
            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 2

The solution is to replace

count = cmd.ExecuteNonQuery

with

count = cmd.ExecuteScalar 

Like Robert Beaubien said in his comments