Using GridView to Save and Retrieve Data with AJAX - Passing Parameters to the SELECT Query
(Page 4 of 4 )
Sometimes you do not want to display all the records in a table. For example, you might want to have users select a product from your grid and display the order details for it in a second grid on the current page. To do this, you’ll need a way to select a product as well as a way to pass the ID of the selected product to the second grid. The Select buttons are already in place from the previous example, so all you need to do now is pass the ID of the selected product to the second grid.
To keep the downloadable source code clear, copy the previous example, AWProductData to a new web site, AWProductDataOrderDetails.
See Appendix A for details about how to copy a web site.
You need to create a second GridView, which will be used to display the order details. From the Toolbox, drag the second GridView onto the page below the first, and then drag the Label and TextBox inside the UpdatePanel. Open the Smart Tag for the UpdatePanel. As you did earlier in the chapter, create a new data source (name it AdventureWorksOrderDetails), but use the existing connection string. Choose the SalesOrderDetail table, select the desired columns (for this example, SalesOrderID, CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount, and LineTotal), and then click the Where button, as shown in Figure 4-23.
A WHERE clause is a SQL language keyword used to narrow the set of data returned by the SELECT statement. In other words, you’re saying, “Get me all the records from this table, where this condition is true.” The condition could be defined any number of ways—where the amount in inventory is less than 10, where the customer name is “Smith,” or where the copyright date is after 1985. It all depends on the types of information you have stored in your columns.
When you click the WHERE button, the Add WHERE Clause dialog opens, which you can see in Figure 4-24. First, you pick the column you want to match on, in this case ProductID. Next, pick the appropriate operator for your condition statement. Your choices include among others, equal to, less than/greater than, like, and contains. For this exercise, use the default (=).
The third drop-down lets you pick the source for the ProductID—that is, where you will get the term you want to match on. You can pick from any one of several objects in the menu or choose None if you’ll be providing a source manually. In this case, you’ll obtain the source of the ProductID from the first GridView, so choose Control.
Figure 4-23. Configuring the SalesOrderDetail table SELECT statement is similar to the way you set up the first DataSource, but this time, you’ll add a WHERE clause.
When you choose Control, the Parameter properties panel of the dialog wakes up. You are asked to provide the ID of the Control containing the target parameter. Select GridView1. Once you’ve made all your choices, the screen will resemble Figure 4-24.
Click Add. When you do, the upper portion of the dialog returns to its initial (blank) state and the WHERE clause is added to the WHERE Clause window. You could add additional WHERE clauses at this point, to further restrict the data, but we won’t for this example.
Click OK to return to the Configure Select Statement dialog box. While you are at it, sort the results by the SalesOrderID column by clicking on the Order By button. The Add ORDER BY Clause dialog with the SalesOrderID column selected is shown in Figure 4-25. The ORDER BY clause is another SQL keyword, and this one does just what its name implies—it sorts the results using the selected field for sort order.
Click OK until the Configure Data Source Wizard is finished.
Switch to Source view and again fix the name of the tables in the SQL statements that were auto-generated. The markup for the second GridView and its associated SqlDataSource is shown in Example 4-4, with the corrected table names highlighted.
Figure 4-24. Add a Where clause to your SELECT statement with the Add WHERE Clause dialog. You select the column, the operator, and the source here.
Figure 4-25. Add an ORDER BY clause to sort the results of your SELECT statement.
Example 4-4. Order detail grid withSqlDataSource
<asp:GridView ID="GridView2" runat="server"
DataSourceID="AdventureWorksOrderDetails">
</asp:GridView>
<asp:SqlDataSource ID="AdventureWorksOrderDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
SelectCommand="SELECT [SalesOrderID], [CarrierTrackingNumber],
[OrderQty], [UnitPrice], [UnitPriceDiscount], [LineTotal]
FROM [Sales].[SalesOrderDetail]
WHERE ([ProductID] = @ProductID)
ORDER BY [SalesOrderID]">
<SelectParameters>
<asp:ControlParameter
ontrolID="GridView1"
Name="ProductID"
PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Also highlighted in Example 4-4 are the results of the WHERE and ORDER BY buttons from the Configure Select Statement Wizard.
The SELECT statement now has a WHERE clause that includes a parameterized value (@ProductID). In addition, within the definition of the SqlDataSource control is a definition of the SelectParameters. This includes one parameter of type asp: ControlParameter, which is a parameter that knows how to get its value from a control (in our example, GridView1). In addition, a second property, PropertyName, tells it which property in the GridView to check. A third property, Type, tells it that the type of the value it is getting is of type Int32, so it can properly pass that parameter to the SELECT statement.
You may now reformat your grid and edit the columns as you did for the first grid, and then try out your new page, which should look something like Figure 4-26.
The AdventureWorks database has no order details for any of the entries with ProductIDs below 707. The first entry with details is on page 22 of the grid, so be sure to move to page 22 (or later) to see product details. If you select a product that does not have any order details, the second grid will not appear.
Please check back next week for the conclusion to this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter four of Learning ASP.NET 2.0 with AJAX: A Practical Hands-on Guide, written by Jesse Liberty, Dan Hurwitz and Brian MacDonald (O'Reilly, 2007; ISBN: 0596513976). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 8 
