In this article I will explain how to get scalar value return from a Stored Procedure in ADO.Net using ExecuteScalar method of the SqlCommand class in C# and VB.Net.
SqlCommand ExecuteScalar is a handy function when you want to just need one Cell value i.e. one column and one row.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Stored Procedure
The following stored procedure inserts a record in the Employees table and returns the ID of the inserted record using SCOPE_IDENTITY in the SELECT statement.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddEmployeeReturnID]
@FirstName varchar(50),
@LastName varchar(50),
@BirthDate datetime,
@City varchar(50),
@Country varchar(50)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Employees (FirstName, LastName, BirthDate, City, Country)
VALUES (@FirstName, @LastName, @BirthDate, @City, @Country)
SELECT SCOPE_IDENTITY()
END
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Get Scalar value returned from Stored Procedure using ExecuteScalar method
The following event handler is executed when the Insert button is clicked. Inside the event handler, the Stored Procedure is executed using the ADO.Net ExecuteScalar method and the retuned value of the Employee ID is fetched and displayed in a Label control.
C#
protected void Insert(object sender, EventArgs e)
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("AddEmployeeReturnID", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim();
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim();
cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim();
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();
cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim();
con.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
string id = o.ToString();
lblMessage.Text = "Record inserted successfully. ID = " + id;
}
con.Close();
}
}
}
VB.Net
Protected Sub Insert(sender As Object, e As EventArgs)
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("AddEmployeeReturnID", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim()
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim()
cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim()
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim()
cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim()
con.Open()
Dim o As Object = cmd.ExecuteScalar()
If o IsNot Nothing Then
Dim id As String = o.ToString()
lblMessage.Text = Convert.ToString("Record inserted successfully. ID = ") & id
End If
con.Close()
End Using
End Using
End Sub
Screenshot
The following screenshot displays the scalar value returned from the Stored Procedure.
![Get Scalar value returned from Stored Procedure using ExecuteScalar method in C# and VB.Net]()
Downloads
Download Code
