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