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: 25 August 2007 10:32
Last Updated: 15 December 2010 15:32
Posted by: Mikesdotnetting
Total Views to date: 112083

Comments

09 January 2009 20:52 from Khaled

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

20 January 2009 04:07 from 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.

23 January 2009 09:02 from Jake

Thank you so so much :-)

29 January 2009 18:55 from 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

27 February 2009 21:16 from 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

27 February 2009 21:16 from Jayquest

19 August 2009 23:56 from 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?

20 August 2009 20:36 from Mikesdotnetting

@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.

14 February 2010 15:03 from Janosch

Awesome Mate!

I need exactly this

22 April 2010 19:47 from Steve

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

I'd been struggling with this for days!

03 May 2010 05:57 from 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.

11 May 2010 07:31 from Mikesdotnetting

@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);

24 August 2010 03:59 from 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

24 August 2010 04:49 from Mikesdotnetting

@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.

24 August 2010 15:21 from 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.

26 August 2010 11:01 from Mikesdotnetting

@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

27 October 2010 22:00 from Nikhil

Thank you very much!

29 November 2010 04:53 from 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!

14 December 2010 13:49 from Shyamendra Shah

Thanks

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

17 December 2010 13:11 from Rupa

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

09 January 2011 17:31 from 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.

09 January 2011 17:41 from Mikesdotnetting

@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.

22 February 2011 15:56 from Jono

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

06 May 2011 19:36 from Gary

beautiful, thank you very much great example/teching

11 May 2011 10:16 from zld

Thanks man, this was incredibly helpful!!!

16 March 2012 05:58 from Suryakanta Khuntia

thanks yar

18 July 2012 18:45 from 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!

07 November 2012 16:37 from Rel

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

13 November 2012 11:06 from MD. RUBAIYAT HASAN

helped me in Client side work

28 February 2013 14:26 from 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 (English is best)

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

Your name:

Your email address:

Your comments:

4 + 8

Mikesdotnetting

Tags

My Book

Blogs

Bind Data From a SqlDataSource to a Label

Comments