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

---

show source

01string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

02        string strSelect = "SELECT COUNT(*) FROM Users WHERE Username = @Username AND Password = @Password";

03 

04        SqlConnection con = new SqlConnection(strConnection);

05        SqlCommand cmd = new SqlCommand();

06        cmd.Connection = con;

07        cmd.CommandType = CommandType.Text;

08        cmd.CommandText = strSelect;

09 

10        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);

11        username.Value = txtUserName.Text.Trim().ToString();

12        cmd.Parameters.Add(username);

13 

14        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);

15        password.Value = txtPassword.Text.Trim().ToString();

16        cmd.Parameters.Add(password);

17 

18        con.Open();

19        int result = (Int32)cmd.ExecuteScalar();

20        con.Close();

21 

22        if (result >= 1)

23            Response.Redirect("Default.aspx");

24        else

25            lblMsg.Text = "Incorrect Username or Password";

VB.NET

---

show source

01Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

02Dim strSelect As String = "SELECT COUNT(*) FROM Users WHERE Username = @Username AND Password = @Password"

03 

04Dim con As New SqlConnection(strConnection)

05Dim cmd As New SqlCommand()

06cmd.Connection = con

07cmd.CommandType = CommandType.Text

08cmd.CommandText = strSelect

09 

10Dim username As New SqlParameter("@Username", SqlDbType.VarChar, 50)

11username.Value = txtUserName.Text.Trim().ToString()

12cmd.Parameters.Add(username)

13 

14Dim password As New SqlParameter("@Password", SqlDbType.VarChar, 50)

15password.Value = txtPassword.Text.Trim().ToString()

16cmd.Parameters.Add(password)

17 

18con.Open()

19Dim result As Integer = DirectCast(cmd.ExecuteScalar(), Int32)

20con.Close()

21 

22If result >= 1 Then

23 Response.Redirect("Default.aspx")

24Else

25 lblMsg.Text = "Incorrect Username or Password"

26End If

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

show source

01string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

02        string strInsert = "INSERT INTO Users (Username,Password) VALUES (@Username,@Password);"

03                          + "SELECT CAST(scope_identity() AS int)";

04        SqlConnection con = new SqlConnection(strConnection);

05        SqlCommand cmd = new SqlCommand(strInsert, con);

06 

07        SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar, 50);

08        username.Value = txtUserName.Text.Trim().ToString();

09        cmd.Parameters.Add(username);

10 

11        SqlParameter password = new SqlParameter("@Password", SqlDbType.VarChar, 50);

12        password.Value = txtPassword.Text.Trim().ToString();

13        cmd.Parameters.Add(password);

14 

15        con.Open();

16        int identity = (Int32)cmd.ExecuteScalar();

17        con.Close();

Tweet

If you like this post than join us or share