Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Tuesday, October 03, 2006

Filtering Database Data with Parameters - Accessing and Updating Data in ASP.NET 2.0

Filtering Basics
When filtering results there are two components of interest: the columns being filtered on, and the value being filtered by. In the example in the Introduction, the UnitPrice column is being filtered on, and 15.00 is the value being filtered by. Typically the value to filter by is not a hard-coded value, but depends on some sort of user input. To allow for a variable filter by value, SQL statements typically use parameters that indicate placeholders where a value can later be inserted. Parameters typically have the format @ParameterName or ?, depending on the underlying database being used. For example, when using parameterized queries with Microsoft Access databases, use ?; when using parameterized queries for Microsoft SQL Server, use @ParameterName. Rewriting the earlier SQL query to use parameters would result in:

Getting Started with Filtering with the SqlDataSource
To start, create an ASP.NET page and go to the Design view. Next, drag on a SqlDataSource control from the Toolbox. As we saw in Part 2, you can configure the database to connect to and the SQL SELECT query to execute by clicking on the "Configure Data Source" link in the SqlDataSource control's smart tag. Recall that there are two screens for specifying the SELECT query:

  • Specify columns from a table or view - here you chose the table or view from a drop-down list and check those columns you want to return, or
  • Specify a custom SQL statement or stored procedure - with this option you can either pick a stored procedure from a drop-down list of manually type in your SQL query (or use the Query Builder)


Filtering Database Data with Parameters - Accessing and Updating Data in ASP.NET 2.0

Post a Comment