ExecuteScalar Example In Asp.Net C# VB

This example describes when and how to use SqlCommand ExecuteScalar Method in Asp.Net Using C# and VB

Other Methods like ExecuteNonQuery , Reader and XmlReader are also explained.

ExecuteScalar Executes the query and returns the first column of first row in result set returned by query, other columns and rows are ignored by this method.

It is best used for retrieving a single value eg record count, max,min,sum or getting the identity values of inserted record from database.

C# CODE

---

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strSelect = "SELECT COUNT(*) FROM Users WHERE Username = @Username AND Password = @Password";
        
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = strSelect;

        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);
        username.Value = txtUserName.Text.Trim().ToString();
        cmd.Parameters.Add(username);

        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        password.Value = txtPassword.Text.Trim().ToString();
        cmd.Parameters.Add(password);

        con.Open();
        int result = (Int32)cmd.ExecuteScalar();
        con.Close();

        if (result >= 1)
            Response.Redirect("Default.aspx");
        else
            lblMsg.Text = "Incorrect Username or Password";

VB.NET

---

Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim strSelect As String = "SELECT COUNT(*) FROM Users WHERE Username = @Username AND Password = @Password"

Dim con As New SqlConnection(strConnection)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = strSelect

Dim username As New SqlParameter("@Username", SqlDbType.VarChar, 50)
username.Value = txtUserName.Text.Trim().ToString()
cmd.Parameters.Add(username)

Dim password As New SqlParameter("@Password", SqlDbType.VarChar, 50)
password.Value = txtPassword.Text.Trim().ToString()
cmd.Parameters.Add(password)

con.Open()
Dim result As Integer = DirectCast(cmd.ExecuteScalar(), Int32)
con.Close()

If result >= 1 Then
 Response.Redirect("Default.aspx")
Else
 lblMsg.Text = "Incorrect Username or Password"
End If

To retrieve Identity of inserted record we can write code like mentined below

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string strInsert = "INSERT INTO Users (Username,Password) VALUES (@Username,@Password);"
                          + "SELECT CAST(scope_identity() AS int)";
        SqlConnection con = new SqlConnection(strConnection);
        SqlCommand cmd = new SqlCommand(strInsert, con);
        
        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);
        username.Value = txtUserName.Text.Trim().ToString();
        cmd.Parameters.Add(username);

        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);
        password.Value = txtPassword.Text.Trim().ToString();
        cmd.Parameters.Add(password);

        con.Open();
        int identity = (Int32)cmd.ExecuteScalar();
        con.Close();

Tweet

If you like this post than join us or share