ADO.NET ExecuteScalar in SqlCommand Object

The ExecuteScalar() method in the SqlCommand Object is specifically designed to retrieve a single value from the database after executing an SQL statement or stored procedure. It is commonly used when you expect a result set with a single value, such as retrieving a count or sum from a database table.


When ExecuteScalar() is called, it executes the SQL statement or stored procedure and returns the value found in the first column of the first row of the result set. If the result set contains multiple columns or rows, ExecuteScalar() will only consider the value in the first column of the first row and discard all other values. If the result set is empty, meaning there are no rows returned, ExecuteScalar() will return a Null reference.

This method is particularly useful when you are interested in retrieving a single piece of information, such as the maximum value in a column or the total number of records in a table. By utilizing ExecuteScalar(), you can efficiently retrieve and work with a single value without having to process or navigate through a full result set.

It is very useful to use with aggregate functions like Count(*) or Sum() etc. When compare to ExecuteReader() , ExecuteScalar() uses fewer System resources.

Full Source VB.NET
Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connetionString As String Dim cnn As SqlConnection Dim cmd As SqlCommand Dim sql As String connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" sql = "Your SQL Statement Here like Select Count(*) from product" cnn = New SqlConnection(connetionString) Try cnn.Open() cmd = New SqlCommand(sql, cnn) Dim count As Int32 = Convert.ToInt32(cmd.ExecuteScalar()) cmd.Dispose() cnn.Close() MsgBox(" No. of Rows " & count) Catch ex As Exception MsgBox("Can not open connection ! ") End Try End Sub End Class

sql = "Your SQL Statement Here like Select Count(*) from product"

You have to replace the string with your realtime variables.

connetionString = "Data Source = ServerName; Initial Catalog = DatabaseName; User ID = UserName; Password = Password"