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: Saturday, August 25, 2007 10:32 AM
Last Updated: Wednesday, December 15, 2010 3:32 PM
Posted by: Mikesdotnetting
Total Views to date: 170746

31 Comments

Friday, January 9, 2009 8:52 PM - Khaled

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

Tuesday, January 20, 2009 4:07 AM - 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.

Friday, January 23, 2009 9:02 AM - Jake

Thank you so so much :-)

Thursday, January 29, 2009 6:55 PM - 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

Friday, February 27, 2009 9:16 PM - 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

Friday, February 27, 2009 9:16 PM - 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

Wednesday, August 19, 2009 11:56 PM - 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?

Thursday, August 20, 2009 8:36 PM - 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.

Sunday, February 14, 2010 3:03 PM - Janosch

Awesome Mate!

I need exactly this

Thursday, April 22, 2010 7:47 PM - Steve

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

I'd been struggling with this for days!

Monday, May 3, 2010 5:57 AM - 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.

Tuesday, May 11, 2010 7:31 AM - 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);

Tuesday, August 24, 2010 3:59 AM - 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

Tuesday, August 24, 2010 4:49 AM - 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.

Tuesday, August 24, 2010 3:21 PM - 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.

Thursday, August 26, 2010 11:01 AM - 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

Wednesday, October 27, 2010 10:00 PM - Nikhil

Thank you very much!

Monday, November 29, 2010 4:53 AM - 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!

Tuesday, December 14, 2010 1:49 PM - Shyamendra Shah

Thanks

Wednesday, December 15, 2010 11:21 AM - 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();
}
}

Friday, December 17, 2010 1:11 PM - Rupa

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

Sunday, January 9, 2011 5:31 PM - 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.

Sunday, January 9, 2011 5:41 PM - 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.

Tuesday, February 22, 2011 3:56 PM - Jono

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

Friday, May 6, 2011 7:36 PM - Gary

beautiful, thank you very much great example/teching

Wednesday, May 11, 2011 10:16 AM - zld

Thanks man, this was incredibly helpful!!!

Friday, March 16, 2012 5:58 AM - Suryakanta Khuntia

thanks yar

Wednesday, July 18, 2012 6:45 PM - 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!

Wednesday, November 7, 2012 4:37 PM - Rel

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

Tuesday, November 13, 2012 11:06 AM - MD. RUBAIYAT HASAN

helped me in Client side work

Thursday, February 28, 2013 2:26 PM - Venkat

Excellent.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Allen Michaels 12/17/2014 4:37 PM
In response to Cascading DropDownLists with jQuery and ASP.NET
Fantastic thank you so much!...

Emily 12/17/2014 12:36 PM
In response to Parameterized IN clauses with ADO.NET and LINQ
Thanks, very helpful!!!! Can i use this for multiple in's ????? IN (.....) and IN(...) and IN...

sss 12/16/2014 3:06 PM
In response to Solving the Operation Must Use An Updateable Query error
good...

Gjuro 12/15/2014 10:30 PM
In response to Examining the Edit Methods and Edit View
You have one fromr (and it should be from, I suppose). :-)...

Gjuro 12/15/2014 10:27 PM
In response to Adding Search
Hi, thnx for all this C#->VB translations. Yet, the following code block is (slightly) in error it a...

Scot 12/14/2014 1:39 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Thanks,Mike I found solution....

Gjuro 12/13/2014 10:52 PM
In response to Accessing Your Model's Data from a Controller
The article mentions "Creating an Entity Framework Data Model for an ASP.NET MVC Application" (at is...

Samuel 12/13/2014 8:40 AM
In response to Displaying The First n Characters Of Text
I have failed to use the extension because it throws an error that it doesn't recognise the chop be...

Ignas 12/12/2014 5:11 PM
In response to Cleaner Conditional HTML Attributes In Razor Web Pages
Any suggestions for Html Helper elements with HtmlAttributes, when you need to conditionally set it...

Gautam 12/11/2014 8:50 PM
In response to Validation In Razor Web Pages 2
Hi Mike Is this required for V3, non html helper input...