ASP.NET SqlDataSource Part 5: Write Custom SELECT Statement To Display Category, And Supplier Name

Instead of select the columns on your GridView using the GridView's "Edit Columns" wizard, you can use a custom SELECT statement that you write to display the appropriate columns.  In this blog we will write our own custom SELECT statement to display the category name, and supplier company on our GridView control.

To specify a custom SELECT statement perform the following steps:

1.  Click on the ">" icon on the SqlDataSource1 control, then click on the "Configure Data Source"

SQL Server: The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'.

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'. The statement has been terminated. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'. The statement has been terminated.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1789294 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5340642 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +275 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1421 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +208 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +163 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +378 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +568 System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +84 System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +930 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +974 System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +201 System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +9703566 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724 Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34009

ASP.NET SqlDataSource Part 4: Enable Pagination, Sorting, Selection

You will notice that when you first create the GridView control all the records are displayed in one page.  You have to scroll just to see all the records.  This can be overwhelming for your users if there are a lot of records.  Also you can not sort or select each record on the GridVidew.

ASP.NET GridView : Enable Pagination, Sorting, Selection

You will notice that when you first create the GridView control all the records are displayed in one page.  You have to scroll just to see all the records.  This can be overwhelming for your users if there are a lot of records.  Also you can not sort or select each record on the GridVidew.

ASP.NET SqlDataSource Part 2: Display Less Columns

Usually when you use the Query Builder feature to build your query you leave the "*" in the select statement.  Th problem with the "*" select statement is that it selects all the columns in the table.  This can sometimes make your GridView very big with all the columns in the table being displayed.

In this tutorial we will display only the columns that we want our users to see.  This tutorial assumes that you've created a GridView with the "*" selected on the "Northwind", "Products" table.

1.  Click on the ">" icon on the right hand side of the GridView control, the "GridView Tasks" panel will appear

ASP.NET GridView : Display Less Columns With "Edit Columns"

Usually when you use the Query Builder feature to build your query you leave the "*" in the select statement.  Th problem with the "*" select statement is that it selects all the columns in the table.  This can sometimes make your GridView very big with all the columns in the table being displayed.

In this tutorial we will display only the columns that we want our users to see.  This tutorial assumes that you've created a GridView with the "*" selected on the "Northwind", "Products" table.

1.  Click on the ">" icon on the right hand side of the GridView control, the "GridView Tasks" panel will appear

ASP.NET SqlDataSource Part 1: Bind The SqlDataSource To A GridView Without Writing A Single Line Of Code

The SqlDataSource control is a data source control that is wizard driven to give you a quick and easy way to connect to the database.  It is a great tool to use if you need something quick to show your boss or if you need to set up a quick demo as a proof of concept.  Most .NET beginners like to use this data source control because it requires very little coding if any at all.  However, if you want to build a real world application you might want to think about creating a data access layer and a business layer to communicate with the database.  Imagine if your application gets bigger and each of your GridView uses an SqlDataSource control.  Each SqlDataSource has it's own query to maintain.  It would be a maintenance nightmare.

The following is a step by step instruction of how to bind a SqlDataSource control to a GridView:

1.  Create a new project Web project in Visual Studio
2.  Create a new .aspx page
3.  Drag the "GridView" control from the "Toolbox" pane in the left hand side to the .aspx design surface

ASP.NET RadioButtonList : Bind RadioButtonList To A List Of Objects

The RadioButtonList displays a collection of radio buttons on a web page. There can only be one selection in a group of choices at a time. In this tutorial we will create a RadioButtonList and then bind it to the Categories table of the Northwind database using a DataTable.

To create a RadioButtonList control do the following:

1.  Select the "RadioButtonList" control under the "Standard" control in the "Toolbox" pane on the left.

ASP.NET DropDownList Part 4: Bind DropDownList To A List Of Objects

In this part of the tutorial we will bind a DropDownList control to a list of objects.  This common practice if you are working with business objects that are mapped to the database table.  We will create a business object call "Category" and create a list call "categories" in our .aspx page.


One of the most commonly used server controls in ASP.NET is the DropDownList control.  In this blog I will show you how to bind a list of objects to a DropDownList control.

1. Create a .aspx page
2. Click on the "Toolbox" panel

ASP.NET RadioButtonList : Bind RadioButtonList Control To A DataTable

The RadioButtonList displays a collection of radio buttons on a web page. There can only be one selection in a group of choices at a time. In this tutorial we will create a RadioButtonList and then bind it to the Categories table of the Northwind database using a DataTable.

To create a RadioButtonList control do the following:

1.  Select the "RadioButtonList" control under the "Standard" control in the "Toolbox" pane on the left.

ASP.NET DropDownList Part 3: Use The DropDownList Control To Populate GridView

If you work on the DropDownList control, eventually you will be asked to populate some sort of data grid based on the selection on the list. The most common control you have to populate is the GridView control. This tutorial builds from part 1 and part 2 about DropDownList control. So make sure you go through those blogs before you start this one. In this tutorial we will learn how to populate the GridView via a DropDownList selection.

 1. Select the "GridView" control from the "Toolbox" pane on the left

ASP.NET DropDownList Part 2: Setting Default Value on First Page Load

In part one DropDownList Part 1: Bind a DataTable to A DropDownList we learned how to bind a DataTable to a DropDownList.  In this tutorial we will learn to set the default value of the DropDownList control on the page's first load that is before there is a post back to the page.  This behavior is commonly used when you want to default the drop down list to a particular selection.  Such as the "United States" in a list of countries drop down list.

To set a default value in the DropDownList control follow the steps below.

1. Add the SetDefaultSelectItem method to the code behind file.

protected void SetDefaultSelectItem(string txtSelect)
{
   DropDownList1.Items.FindByText(txtSelect).Selected = true;
}

2. Add a condition to only set the default value on the first page load

protected void Page_Load(object sender, EventArgs e)
{
    if(!Page.IsPostBack)
    {
        BindCategoriesList();
        SetDefaultSelectItem("Seafood");
    }
}

3. If you run the application you will now see that "Seafood" is the default selection on the DropDownList control, when the page first load

1. Bind a DataTable To A DropDownList
2. (DropDownList) Setting Default Value on First Page Load
3. Use The DropDownList Control To Populate GridView
4. Bind DropDownList To A List Of Objects

ASP.NET DropDownList Part 1: Bind a DataTable To A DropDownList

One of the most commonly used server controls in ASP.NET is the DropDownList control.  In this blog I will show you how to bind a DataTable to a DropDownList control.

1. Create a .aspx page
2. Click on the "Toolbox" panel

Create A Blank Solution in Visual Studio 2013

When you are starting a new project, you might not want to use the templates that Microsoft provided for you. There are times when you want to start off the project with a clean slate.  You can create a blank solution in Visual Studio.  However, the option to create a blank solution is kind of hidden.

Follow the steps below to create a blank solution:

1. Open Visual Studio 2013, and then click on "File", then select "New", then  "Project"

4.  Your blank solution will be displayed in "Solution Explorer"