Introduction:
Description:
In
previous post I explained ExecuteNonQuery Example and differences between ExecuteNonQuery,
ExecuteReader and ExecuteScalar in asp.net. Now I will explain ExecuteScalar concept with one example in asp.net using C#.net, VB.NET.
ExecuteScalar
Execute
Scalar will return first row first column value i.e. it will return single
value and ignore other values on execution of SQL Query or Stored procedure
using command object. It’s very fast to retrieve single values from database.
Before
implement this example first design one table UserInformation in your database as shown below
Column Name
|
Data Type
|
Allow Nulls
|
UserName
|
varchar(50)
|
Yes
|
LastName
|
varchar(50)
|
Yes
|
Location
|
Varchar(50)
|
Yes
|
Once
table designed in database enter some dummy data to test after that write the
following code in your aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1"
runat="server">
<title>Example of ExecuteNonQuery in asp.net</title>
</head>
<body>
<form id="form1"
runat="server">
<div>
<asp:Button ID="btnSubmit"
runat="server"
Text="Submit"
onclick="btnSubmit_Click"
/><br
/>
<b>First Row First Column Value: </b><asp:Label ID="lblDetails" runat="server" />
</div>
</form>
</body>
</html>
|
Now add the following namespaces in
code behind
C# Code
using System;
using System.Data.SqlClient;
|
After add namespaces write the following code in code behind
protected void
Page_Load(object sender, EventArgs e)
{
}
protected void
btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection
con = new SqlConnection("Data Source=SureshDasari;Integrated
Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("Select
UserName,LastName,Location FROM UserInformation", con);
string result = (string)cmd.ExecuteScalar();
if (!string.IsNullOrEmpty(result))
{
lblDetails.Text = result;
}
else
{
lblDetails.Text =
"No value Selected" ;
}
con.Close();
}
}
|
VB.NET Code
Imports System.Data.SqlClient
Partial Class
ExecuteScalar
Inherits System.Web.UI.Page
Protected Sub
Page_Load(ByVal sender As Object, ByVal e As
EventArgs)
End Sub
Protected Sub
btnSubmit_Click(ByVal sender As Object, ByVal e As
EventArgs)
Using con As New SqlConnection("Data
Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("Select
UserName,LastName,Location FROM UserInformation", con)
Dim result As String = DirectCast(cmd.ExecuteScalar(),
String)
If Not String.IsNullOrEmpty(result) Then
lblDetails.Text = result
Else
lblDetails.Text =
End If
con.Close()
End Using
End Sub
End Class
|
Demo
 |
If
you observe above code I written select query like getting all the data from UserInfromation table but whenever we
click on button we are getting only one value (First Row First Column value)
and ignoring other row and column values.
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
3 comments :
You have missed out on the fact that executescalar also returns the 1st column of the first row if the query returns more than one value
cmd.ExecuteScalar() is return only value, not display the details
whether it works well for delete query?
Note: Only a member of this blog may post a comment.