Bind Data From a SqlDataSource to a Label

Label controls in ASP.NET don't have a smart tag that allows you to select a data source, so at first glance, it is not easy to see how to bind a value returned from a SqlDataSource or AccessDataSource control to the label. Here's how to programmatically access the data returned by such a DataSource and apply it to a non-databound control.

To programmatically access the contents of a SqlDataSource or AccessDataSource control you need to explicitly call its Select() method. This method accepts a single input parameter of type DataSourceSelectArguments. This parameter can contain information regarding the filters to apply or the column to Order By. For example, when working with a sortable GridView, sorting a column calls the Select() method, and passes in a DataSourceSelectArguments instance with its SortExpression property set to the column name the user chose to sort by. If you don't want the DataSource to sort or filter, you pass in DataSourceSelectArguments.Empty.

Depending on the DataSourceMode of the DataSource control, one of two objects are returned when the Select() method is called. If the DataSourceMode is set to DataReader, a DataReader object is returned. The type of DataReader (SqlDataReader, OleDbDataReader, OdbcDataReader etc) that is returned depends entirely on the provider type used - in other words, whether you are using the OleDb provider, SqlClient provider etc. It has nothing to do with the type of DataSource control. The examples below both query an Access database, but one uses the AccessDataSource control, and the other uses the SqlDataSource control. Both return OleDbDataReaders, because it is the OleDbProvider library that is used for the connection.

If the DataSourceMode is set to Dataset, or not set at all (which means that the default setting of Dataset is used) the object that is returned is a DataView. A DataView is like a DataTable on steroids. It exposes methods that allow you to filter and sort data, for example, and bind it. A DataView contains a collection of DataRowView objects, which represent each row in the returned results.

So, with a DataReader, you would access the values during the DataReader.Read() operation, in very much the same way as if you are using plain ADO.NET code, whereas with the DataSet, you would need to create an object of the appropriate type - DataView, then iterate the DataRowView collection to access the values. In this, the code is remarkably similar to accessing values directly from a DataSet's table collection using plain ADO.NET.

The code below shows the contents of an aspx file, which contains two label controls, and two SqlDataSource controls. Each SqlDataSource control has its DataSource mode set to alternative values - DataSet and DataReader, and both of them have an OnSelecting event defined in which the value of the EmployeeID parameter is assigned:

<asp:Label ID="Label1" runat="server" /> <asp:Label ID="Label2" runat="server" />

<asp:SqlDataSource 
	ID="SqlDataSource1" 
	runat="server" 
	ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
	ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
	DatasourceMode="DataSet"
	SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = ?)" 
	OnSelecting="SqlDataSource1_Selecting">
    <SelectParameters>
        <asp:Parameter Name="EmployeeID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

<asp:SqlDataSource
	ID="SqlDataSource2" 
	runat="server"
	ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
	ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
	DatasourceMode="DataReader"
	SelectCommand="SELECT [LastName], [FirstName] FROM [Employees] WHERE ([EmployeeID] = ?)" 
	OnSelecting="SqlDataSource2_Selecting">
    <SelectParameters>
        <asp:Parameter Name="EmployeeID" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

The following code snippet shows the aspx.cs file contents, where the parameter values are set in the Selecting event handler. In the Page_Load method, the data returned by each of the Sql DataSource controls is accessed and a value consigned to a label. The method of access depends on the DataSource mode, but is identical for both SqlDataSource and AccessDataSource:

[C#]
protected void Page_Load(object sender, EventArgs e)
{

    DataView dvSql = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
    foreach (DataRowView drvSql in dvSql)
    {
        Label1.Text = drvSql["FirstName"].ToString();
    }

    OleDbDataReader rdrSql = (OleDbDataReader)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
    while (rdrSql.Read())
    {
        Label2.Text = rdrSql["LastName"].ToString();

    }
    rdrSql.Close();
}



protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["EmployeeID"].Value = 2;
}

protected void SqlDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    e.Command.Parameters["EmployeeID"].Value = 2;
}
[VB]
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

Dim dvSql As DataView = 
    DirectCast(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
    For Each drvSql As DataRowView In dvSql
        Label1.Text = drvSql("FirstName").ToString()
    Next

Dim rdrSql As OleDbDataReader = 
    DirectCast(SqlDataSource2.Select(DataSourceSelectArguments.Empty), OleDbDataReader)
    While rdrSql.Read()

        Label2.Text = rdrSql("LastName").ToString()
    End While
    rdrSql.Close()
End Sub

Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, 
    ByVal e As SqlDataSourceSelectingEventArgs)
    e.Command.Parameters("EmployeeID").Value = 2
End Sub

Protected Sub SqlDataSource2_Selecting(ByVal sender As Object, 
    ByVal e As SqlDataSourceSelectingEventArgs)
    e.Command.Parameters("EmployeeID").Value = 2
End Sub

When using this technique with Sql Server - or more specifically the SqlClient provider, change OleDbDataReader to SqlDataReader in the above code.

 

Date Posted:
Last Updated:
Posted by:
Total Views to date: 194741

31 Comments

- Khaled

NEAT !! that's exactly what I was looking for.. a quick crash course !

- bradzmac

Let me try the code, I am intermediate programmer. hope this will help me. retirve a value to a database. a random value. i will modify the SQL syntax to fit my needs. thank you for the code.

- Jake

Thank you so so much :-)

- sameer

Thank you so much, I was almost dissapointed looking in google for the solution, everyone was just giving some answers for sake of answers, escpecially asp.net forums,

This is perfect answer, thanks again

- Jayquest

Wow! How freaking easy is that! Why is it that everyone else on the internet insists that you can't do this without using a formview or dataview. I was getting pissed. Thanks a billion for explaining the process so well. I will visit your site more often now that I found it.

JayQuest

- Jayquest

Wow! How freaking easy is that! Why is it that everyone else on the internet insists that you can't do this without using a formview or dataview. I was getting pissed. Thanks a billion for explaining the process so well. I will visit your site more often now that I found it.

JayQuest

- Doug Moore

Hey Mike, this is such a great article, but I have two questions. I've tried your code example in my project, and it works great, but would like to know why. - -

1)Why do we need a foreach here:
foreach (DataRowView drvSql in dvSql) when there is only one row that is returned? - - - -

2)Why do we need ToString() here:
Label1.Text = drvSql("FirstName").ToString()
Isn't "FirstName" already a string?

- Mike

@Doug

1. That's a fair point. You could reference it like this instead:

Label3.Text = dvSql[0][1].ToString();
or
Label3.Text = dvSql[0]["FirstName"].ToString();

2. No - "FirstName" isn't a string. It's an index. Just as 0 is in the dvSql[0][0] option. The actual datatype that's returned is an object. If you try leaving .ToString() off, the compiler will complain.

- Janosch

Awesome Mate!

I need exactly this

- Steve

Thank you very much - exactly what I've been looking for :D

I'd been struggling with this for days!

- New2CSharp

I am building my first C# site and am fluent in VB, this works just fine in Visual Basic, but not in C#. The reason for this is that when using the data to add new tags to the head (meta description, cannonical link, etc.) it adds a new tag for each record in a list. The C# code does not have a "next" at the end of it and thus creates a new tag for each record for example:

DataView product = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
foreach (DataRowView drvSql in product)
{
string subcategory = drvSql["videotype"].ToString();
HtmlMeta DescTag = new HtmlMeta();
DescTag.Attributes.Add("name", "description");
string metadesc = subcategory + " products";
DescTag.Attributes.Add("content", metadesc);
Header.Controls.Add(DescTag);

}


My Question is what is the C# equivalent of "next" so that I can do this without having a duplicate tags equal to the number of rows.

- Mike

@New2CSharp

There is no Next in C#, but you need to look at your logic. Foreach starts a loop. Now look at what you have within the loop: you have code which creates a meta tag, populates it, and then adds it to the Header.Controls colleciton. So it's logical that it will create, populate and add a tag for each row in your resultset.

What you want to do is to create one tag, and add it once, so those operations need to happen ouotside the loop:

HtmlMeta DescTag = new HtmlMeta();
DescTag.Attributes.Add("name", "description");
DataView product = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
foreach (DataRowView drvSql in product)
{
string subcategory = drvSql["videotype"].ToString();
string metadesc = subcategory + " products";
}
DescTag.Attributes.Add("content", metadesc);
Header.Controls.Add(DescTag);

- Mark

I'm a newbie struggling to implement your example in a VB based asp.net page. I cut and pasted the code into a default.aspx page, created a table called "Employees" with columns of FirstName, LastName and EmployeeID. I pasted the VB code into the Default.aspx.vb page but I get "error BC30456: 'SqlDataSource1_Selecting' is not a member of 'ASP.default_aspx'. I know I'm doing something really dumb but I don't know what. Can you please help. I really appreciate it. Thanks

- Mike

@Mark,

You need ot go into Design View, and select the SqlDataSource by clicking it once. Then hit F4 to bring up the properties window. Then click the lightning bolt, and find the Selecting event. Double click it, and you will get the right event handler.

- Mark

Thanks for your reply. I now get the following exception:
System.IndexOutOfRangeException was unhandled by user code
Message=An SqlParameter with ParameterName 'EmployeeID' is not contained by this SqlParameterCollection.


Also, it's not clear to me from the code how the sql data is being selected. I am trying to implement an example where I enter a value into a textbox which then returns other values from the same row in the sql table. I haven't gotten to that stage yet since I can't get your simple example to work. Thanks for your help.

- Mike

@Mark

The SelectCommand is the SQL that get executed. I think you would be better advised spending a little time understanding the basics of ASP.NET datacontrols. http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/sqldatasource.aspx

- Nikhil

Thank you very much!

- Steve

This was incredibly helpful to me. Using your approach allowed me to pass a DateTime associated with an ImageButton within a Formview to my C# CodeBehind with the very simple code below:

DataView dvSql = (DataView)RecentDataSource.Select(DataSourceSelectArguments.Empty);
foreach (DataRowView drvSql in dvSql)
{
Calendar1.SelectedDate = DateTime.Parse(drvSql["Date Taken"].ToString());
}

Many thanks!

- Shyamendra Shah

Thanks

- Mark

Thanks! great solution. The following code fills a text box with a value from a table when the selected index change event is triggered.

protected void DropDownListIntName_SelectedIndexChanged(object sender, EventArgs e)
{
DataView dvSql = (DataView)SqlDataSource4.Select(DataSourceSelectArguments.Empty);

foreach (DataRowView drvSql in dvSql)
{
TextBoxExtName.Text = drvSql["ProductExtName"].ToString();
}
}

- Rupa

hey thanks.. very well mentioned ... I was missing DataSourceMode property hence not geeting the results..

- mr g

i have go with marks comment in august.

Message=An SqlParameter with ParameterName EmployeeID is not contained by this SqlParameterCollection

Seems I wasted to much time on just getting a simple label to bind.

my parameter was a queary string parameter and maybe that was the catch maybe an assembly.

this would be so helpful to use. i will try to come back and also check out the quick starts sqldatadapter section.

I am using .net 4 and i noticed 4 has sqldatasource filterparameters which pulls from cache.
It seems like over kill to put a data grid on a page just to pull a name that is in the main detailview below. Maybe slowly move to mvc.

I found this frustrating.

- Mike

@mr g

There are much cleaner ways to simply bind a value from your database to a Label control than the one illustrated here. For a start, I wouldn't recommend using the SqlDataSource control for anything except rapid prototyping of a web forms application. I haven't used one for years, but I moved across to MVC as soon as it became available. As a consequence, I've forgotten most of the foibles of the control itself...

One thing I do remember, FilterExpressions are complete and total evil. If you are using FilterParameters, you are definitely doing things wrong.

Use a SqlDataReader in your code behind and just get the data you need.

- Jono

Thank you!!! After hours of searching you finally solved my problem.

- Gary

beautiful, thank you very much great example/teching

- zld

Thanks man, this was incredibly helpful!!!

- Suryakanta Khuntia

thanks yar

- RS

Just came upon this link and it's exactly what I've been looking for. The comments were also helpful for describing a single row result set. THANK YOU!

- Rel

Just wanted to let you know, this was helpful to me!

- MD. RUBAIYAT HASAN

helped me in Client side work

- Venkat

Excellent.

Recent Comments

sandeep 8/28/2015 7:03 AM
In response to 7 C# 6.0 Features That Every ASP.NET Developer Should Know About
very good article, i like it........keep writing such quality article in future. thx Mike....

Hassan, MVC Learner 8/28/2015 6:37 AM
In response to Get The Drop On ASP.NET MVC DropDownLists
Great Help, simple, great and patiently explained article !...

Anvesh 8/28/2015 12:39 AM
In response to ASP.NET MVC DropDownLists - Multiple Selection and Enum Support
what if we are taking postback values from FormCollections instead of an array int[] category. How...

Ben 8/27/2015 10:50 PM
In response to Simple File Download Protection with ASP.NET
Is it possible to setup your project to publish files to outside of your root directory? I would to...

Fred 8/26/2015 12:50 AM
In response to WebMatrix Opens Wrong Version Of Visual Studio
I enjoyed many of your tutorials but the problem is none of the tutorials are combined like most be....

Muhammad Ashikuzzaman 8/25/2015 2:48 PM
In response to Managing Checkboxes And Radios In ASP.NET Razor Web Pages
That's a very good tips for razor...

Sergey 8/25/2015 8:32 AM
In response to More Flexible Routing For ASP.NET Web Pages
Hi. How I can set up my site to get urldata from link for default page? site.com/default/1-...

Tony Gray 8/25/2015 6:27 AM
In response to Adding Validation
Hi Mike, Really helpful article and series. Thanks. Small typo in 4th paragraph you have so...

amanda n 8/25/2015 12:38 AM
In response to Solving the Operation Must Use An Updateable Query error
Thank you very much. I'm a uni student and while solving coding problems is usually enjoyable and me...

salman 8/23/2015 9:25 AM
In response to How To Send Email In ASP.NET MVC
thanks ......