Home Library Learn Downloads Support Community
Sign in | United States - English | |
1 out of 4 rated this helpful Rate this topic

SqlCommand.ExecuteScalar Method

.NET Framework 4

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Namespace:  System.Data.SqlClient
Assembly:  System.Data (in System.Data.dll)
C#
C++
F#
VB
Copy 
public override Object ExecuteScalar()

Return Value

Type: System.Object
The first column of the first row in the result set, or a null reference (Nothing in Visual Basic) if the result set is empty. Returns a maximum of 2033 characters.

Implements

IDbCommand.ExecuteScalar
Exception Condition
SqlException

An exception occurred while executing the command against a locked row. This exception is not generated when you are using Microsoft .NET Framework version 1.0.

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader.

A typical ExecuteScalar query can be formatted as in the following C# example:

Copy 
 cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
 Int32 count = (Int32) cmd.ExecuteScalar();

The following example creates a SqlCommand and then executes it using ExecuteScalar. The example is passed a string representing a new value to be inserted into a table, and a string to use to connect to the data source. The function returns the new Identity column value if a new row was inserted, 0 on failure.

C#
VB
Copy 

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

.NET Framework

Supported in: 4, 3.5, 3.0, 2.0, 1.1, 1.0

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows XP SP2 x64 Edition, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.
Did you find this helpful?
(2000 characters remaining)
Community Content Add
Annotations FAQ
XML results truncated to 2033 characters
Note that if your query is a FOR XML EXPLICIT query with a result longer than 2033 characters, SQL breaks it up into multiple rows of 2033 characters each. If you use ExecuteScalar, you will only get the first 2033 characters of your xml. Use either executeXMLReader instead, or return a table and loop through the rows to rebuild your complete XML results.
History
error

Specified cast is not valid.

History
SqlException condition
Is a locked row the only cause of the SqlException here? Won't it be thrown in numerous other cases? For example, it is thrown when the underlying SQL raises an exception.
History