2 Replies - 3000 Views - Last Post: 19 May 2007 - 01:06 PM Rate Topic: -----

#1 Videege  Icon User is offline

  • rÍvant.toujours
  • member icon

Reputation: 6
  • View blog
  • Posts: 1,413
  • Joined: 25-March 03

DropdownLists and SQL Queries

Posted 17 May 2007 - 08:13 AM

Hey all you ASP.NET and SQL gurus, I've got a quick question for you.

So I've got some DropdownLists and a SqlDataSource. By default, these controls are bound to specific columns in the database and make available all the options within that column. However, what if I want to filter the choices available in these lists as the user selects options in one of them. For example, let's say I have a dropdown that lets you select authors and another that lets you select books. Well, if the user selects Frank Herbert as an author, how do I filter the book list to only show books by Herbert? I know how to change a dataview/gridview/whatever and make programmatic SQL queries via my SqlDataSource, but I haven't been able to figure this one out. Thanks for any help!

Is This A Good Question/Topic? 0
  • +

Replies To: DropdownLists and SQL Queries

#2 JellyBean  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 60
  • Joined: 25-April 07

Re: DropdownLists and SQL Queries

Posted 19 May 2007 - 02:21 AM

View PostVideege, on 17 May, 2007 - 04:13 PM, said:

For example, let's say I have a dropdown that lets you select authors and another that lets you select books. Well, if the user selects Frank Herbert as an author, how do I filter the book list to only show books by Herbert? I know how to change a dataview/gridview/whatever and make programmatic SQL queries via my SqlDataSource, but I haven't been able to figure this one out. Thanks for any help!

One way to achieve this would be to make the first drop-down (author) do a post-back when it's value is changed. In your code-behind Page_Load event you could check for a post-back, and if you have one, then you can modify the SQL query for the second drop-down (books) to select only books by the specified author. You will need to do a DataBind after modifying the control's data-source.
Was This Post Helpful? 0
  • +
  • -

#3 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: DropdownLists and SQL Queries

Posted 19 May 2007 - 01:06 PM

An alternative method is to use two DataAdapters. The first data adapter is bound to your drop-down list, which will fill it with the values from your bound dataset.

Set-up a parameter to store the value returned from the selection in the first drop-down list and then use it in the SQL statement that will will fill the second dataset of the second data adapter.

The second data adapter retrieves the data for the value selected in the first drop-down list.

Example of the necessary SQL for each data adapter. This assumes a @AuthorID parameter has been created.

First data adapter SQL.
SELECT AuthorID, AuthorName FROM Authors ORDER BY AuthorName

Then store the AuthorID in the @AuthorID parameter when it is selected from the drop-down list.

Next execute the second data adapter SQL.
SELECT BookID, BookName FROM Books
WHERE AuthorID = @AuthorID


Two data adapters and two datasets. One dataset to contain the authors, the other to contain the books that is returned based on the author selection.

Hopefully that will give you a pretty good idea on how to do it.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1