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: 179266

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.
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 I end up deleting quite a lot. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Requests to fix your code (post a question to forums.asp.net instead, please)
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam

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

hosein ey 2/25/2015 1:56 PM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
tnx for this article do you think next version of entityframework support's the sql server and ?...

Saywer Ford 2/25/2015 5:15 AM
In response to Optimising ASP.NET Web Pages Sites - Bundling And Minification
Great Article. I did everything right and working fine. How about page specific js files. Lets I...

Saravanan 2/24/2015 10:54 AM
In response to Optimising ASP.NET Web Pages Sites - Bundling And Minification
Hi, Great article about the Bundling and minification. Regards the caching as you mentioned the...

Justin 2/24/2015 10:43 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
I'm having the same problem as Ingmar. Everything is working fine locally in Visual Studio, but I it...

Logan Mudlo 2/23/2015 4:59 PM
In response to WebMatrix - A First Application
Is there a way to prevent the automatic close on a Database.Open() call?...

Mog0 2/23/2015 11:16 AM
In response to ASP.NET MVC 5 with EF 6 - Working With Files
Probably a silly question but why did you disable cascade delete and implement it yourself?...

Jose-Maria 2/19/2015 3:29 PM
In response to Migrating From Razor Web Pages To ASP.NET MVC 5 - Views and Controllers
Just great ! Many thanks Mike....

Satyabrata Mohapatra 2/19/2015 12:02 PM
In response to Migrating From Razor Web Pages To ASP.NET MVC 5 - Views and Controllers
This is great!!...

Ryan Helmoski 2/19/2015 10:24 AM
In response to Conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value
Thank you!...

Harsha 2/19/2015 8:27 AM
In response to iTextSharp - Adding Text with Chunks, Phrases and Paragraphs
Hi, Can text area be created so that I can type text in the area in pdf document? We have Add text...