SqlDataSource Control 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"

2.  Click the "Next" button until you reach the "Configure the Select Statement" screen, select the "Specify a custom SQL statement or stored procedure" radio button.  Then click the "Next" button

3. On the "Define Custom Statements or Stored Procedures" screen select the "SELECT" tab, and then type in the following SELECT statement to get the CategoryName field in the Categories table, and the CompanyName field in the Suppliers table.  Then click on the "Next" button.

     SELECT ProductName,
  Categories.CategoryName AS Category,
  Suppliers.CompanyName AS Supplier,
  QuantityPerUnit,
  UnitPrice,
  UnitsInStock,
  UnitsOnOrder,
  ReorderLevel,
  Discontinued
 FROM Products,Categories,Suppliers 
 WHERE Products.CategoryID = Categories.CategoryID
 AND Products.SupplierID = Suppliers.SupplierID

4.  On the "Test Query" screen click on the "Test Query" button see the results that the query will bring back.  Notice that on columns Category, and Supplier the data from the CategoryName and CompanyName fields are displayed, respectively.  Click "Finish"

5.  Now the GridView is populated with the columns specified in the custom SELECT statement

SQL Server 2014 : Creating a Sysadmin Using a Windows 8.1 Account

The one of thing that Windows 8 forces you to do is to sign in with an e-mail account.  I am not here to debate if it's a good thing or a bad thing.  But I just wanted to say, Microsoft why do you make our lives so complicated.  I just wanted Windows 7 with a touchscreen.  I digress :(   Anyways, if you install SQL Server on Windows 8 there is a little quirk that you have to deal with.  When you search for an account to add to your dba user login, you have to search on the entire username including the stuff after the @ sign.  Once again, I digress :(

Anyways here is how you add a dba to SQL Server 2014 on a Windows 8 machine.

1.  Connect to your instance of SQL Server, then expand the "Logins" node

2. Right click on "Logins" node, and then select "New Login"

3. Click on the "Search" button

4. Type in the account you want to add in the "Enter the object name to select" text box.  That is my e-mail, feel free to e-mail me.  The only gripe I have with this whole process is that, before we were able to do a partial search.  For example I could have just typed in "tech.junkie.jh" and SQL Server would be smart enough to figure out I am close enough.  Now I have to type in the whole account.  It feels like Microsoft is moving backward.  Anyways click on "Check Names"

5.  On the left hand side you will see a navigation panel, that says "Select a page", you want to select "Server Roles".  Check everything, and the kitchen sink.

  

6. Click "OK" then you have yourself another sysadmin, or what we like to call a db god.  DB God:  I shall grant you INSERT if you bring me donuts and coffee in the morning.....evil laugh sold separately

SQL Server 2014 : Creating a Sysadmin Using a Windows 8.1 Account

The one of thing that Windows 8 forces you to do is to sign in with an e-mail account.  I am not here to debate if it's a good thing or a bad thing.  But I just wanted to say, Microsoft why do you make our lives so complicated.  I just wanted Windows 7 with a touchscreen.  I digress :(   Anyways, if you install SQL Server on Windows 8 there is a little quirk that you have to deal with.  When you search for an account to add to your dba user login, you have to search on the entire username including the stuff after the @ sign.  Once again, I digress :(

Anyways here is how you add a dba to SQL Server 2014 on a Windows 8 machine.

1.  Connect to your instance of SQL Server, then expand the "Logins" node

2. Right click on "Logins" node, and then select "New Login"

3. Click on the "Search" button

4. Type in the account you want to add in the "Enter the object name to select" text box.  That is my e-mail, feel free to e-mail me.  The only gripe I have with this whole process is that, before we were able to do a partial search.  For example I could have just typed in "tech.junkie.jh" and SQL Server would be smart enough to figure out I am close enough.  Now I have to type in the whole account.  It feels like Microsoft is moving backward.  Anyways click on "Check Names"

5.  On the left hand side you will see a navigation panel, that says "Select a page", you want to select "Server Roles".  Check everything, and the kitchen sink.

  

6. Click "OK" then you have yourself another sysadmin, or what we like to call a db god.  DB God:  I shall grant you INSERT if you bring me donuts and coffee in the morning.....evil laugh sold separately

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

This error occurs when you tried to use the default "DeleteStatement" generated by SQL Builder in Visual Studio 2013 for the "Products" table in the "Northwind" database.  What it says basically is that the query is trying to delete a product that is being reference by another table call the "Order Details" table.  The solution to the problem is to delete the child records in the "Order Details" table before you delete the record in the "Products" table.
In this example the scenario is that we added a SqlDataSource control to our .aspx page and we used the auto-generated DELETE statements generated by Visual Studio. We click on the "Delete" link on our GridView and received the above error.


If you select the following checkboxes, you will get auto generated SQL statements for INSERT, UPDATE, and DELETE.  The problem is the DELETE statement that were generated by Visual Studio only deletes the record in the Products table.

This is the code that is being generated:

DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID" 

If you look at the Order Details table in SQL Server Studio you will see that the ProductID field in the Order Details table is referencing the ProductID in the Products table

The easiest way to fix this issue is to modify the delete statement that was generated by Visual Studio.  What you want to do is delete the records from the lowest child level.  Since the "Order Details"  table is referencing the "Products" table we want to delete all the records associated with the record in the "Order Details" table first.

So here is the original DELETE statement

DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID" 

We want to change it to

DeleteCommand="DELETE FROM [Order Details] WHERE [ProductID] = @ProductID; DELETE FROM [Products] WHERE [ProductID] = @ProductID"

Now you can delete the "Products" record with no issue using the SqlDataSource control and a GridView control.  There are more elegant ways of resolving this issue, however since this is a blog, we will implement the sitcom 30 minutes solution.  The main purpose of this blog is to make the point that the reason the error occur is because there's a foreign key constraint violation.  Which is a good thing, not all exceptions are a bad thing.  You just have to handle it.

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

This error occurs when you tried to use the default "DeleteStatement" generated by SQL Builder in Visual Studio 2013 for the "Products" table in the "Northwind" database.  What it says basically is that the query is trying to delete a product that is being reference by another table call the "Order Details" table.  The solution to the problem is to delete the child records in the "Order Details" table before you delete the record in the "Products" table.
In this example the scenario is that we added a SqlDataSource control to our .aspx page and we used the auto-generated DELETE statements generated by Visual Studio. We click on the "Delete" link on our GridView and received the above error.


If you select the following checkboxes, you will get auto generated SQL statements for INSERT, UPDATE, and DELETE.  The problem is the DELETE statement that were generated by Visual Studio only deletes the record in the Products table.

This is the code that is being generated:

DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID" 

If you look at the Order Details table in SQL Server Studio you will see that the ProductID field in the Order Details table is referencing the ProductID in the Products table

The easiest way to fix this issue is to modify the delete statement that was generated by Visual Studio.  What you want to do is delete the records from the lowest child level.  Since the "Order Details"  table is referencing the "Products" table we want to delete all the records associated with the record in the "Order Details" table first.

So here is the original DELETE statement

DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID" 

We want to change it to

DeleteCommand="DELETE FROM [Order Details] WHERE [ProductID] = @ProductID; DELETE FROM [Products] WHERE [ProductID] = @ProductID"

Now you can delete the "Products" record with no issue using the SqlDataSource control and a GridView control.  There are more elegant ways of resolving this issue, however since this is a blog, we will implement the sitcom 30 minutes solution.  The main purpose of this blog is to make the point that the reason the error occur is because there's a foreign key constraint violation.  Which is a good thing, not all exceptions are a bad thing.  You just have to handle it.

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.

 In this tutorial we will enable "Paging", "Sorting", and "Selection" on the GridView

1.  Click on the ">" icon on the GridView

2.  The "GridView Tasks" panel will be displayed

3.  Check the "Enable Paging", "Enable Sorting", and "Enable Selection" checkboxes

4.  Type Ctrl + F5  to run the application to see the changes

SqlDataSource Control Part 3: 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.

 In this tutorial we will enable "Paging", "Sorting", and "Selection" on the GridView

1.  Click on the ">" icon on the GridView

2.  The "GridView Tasks" panel will be displayed

3.  Check the "Enable Paging", "Enable Sorting", and "Enable Selection" checkboxes

4.  Type Ctrl + F5  to run the application to see the changes

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.

 In this tutorial we will enable "Paging", "Sorting", and "Selection" on the GridView

1.  Click on the ">" icon on the GridView

2.  The "GridView Tasks" panel will be displayed

3.  Check the "Enable Paging", "Enable Sorting", and "Enable Selection" checkboxes

4.  Type Ctrl + F5  to run the application to see the changes

SqlDataSource Control Part 3: 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.

 In this tutorial we will enable "Paging", "Sorting", and "Selection" on the GridView

1.  Click on the ">" icon on the GridView

2.  The "GridView Tasks" panel will be displayed

3.  Check the "Enable Paging", "Enable Sorting", and "Enable Selection" checkboxes

4.  Type Ctrl + F5  to run the application to see the changes

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

2.  Click on the "Edit Columns" link

3.  Select the "SupplierID" field in the "Selected fields" list box then click on the "X" button

4.  Select the "CategoryID" field in the "Selected fields" list box then click on the "X" button

5.  Select the "ProductID" field in the "Selected fields" list box then click on the "X" button

6.  Now the "Selected fields" will have not have the "ProductID,CategoryID,SupplierID".  The remaining fields are the fields that will be displayed on the GridView.  Noticed the "Available Fields" list box, it still contain all the fields that you've selected.  That means you can perform functions on those fields, the fields just won't be displayed anymore.

7.  Click "OK"

8.  As you can see the GridView only has the fields that you've selected in the "Selected fields" list box

9.  Type Ctrl+F5 to run the application and see the new GridView with less columns