SQLDataSource query

Discussion in 'ASP .Net' started by karlman, Sep 25, 2007.

  1. karlman

    karlman Guest

    Not sure if this is a SQL question or ASP.Net control question.
    Probably can do it either way I imagine.

    I have a simple page that uses a SqlDataSource, DropDownList, and a
    GridView. I have the DropDownList post back and is a ControlParameter
    for the SqlDataSource. It works great when selecting individual items
    in the DropDownList but I want to also be able to query for all in the
    list.

    I add an extra ListItem into the DropDownList in the Page_Load event
    but I am not sure what to put for value.

    Example:

    SELECT * FROM [Company] WHERE Company_ID = @CompanyID

    @CompanyID is a control parameter for the DropDownList

    What value can I set for the 'ALL' list item so it would return all
    records and basically ignore the WHERE clause.

    One idea I may try is to have another SqlDataSource that does not have
    the where clause and change the GridView datasource on Page_Load if it
    is set to 'ALL'...

    Thanks
    Karl
     
    karlman, Sep 25, 2007
    #1
    1. Advertising

  2. On Sep 25, 11:36 pm, karlman <> wrote:
    > Not sure if this is a SQL question or ASP.Net control question.
    > Probably can do it either way I imagine.
    >
    > I have a simple page that uses a SqlDataSource, DropDownList, and a
    > GridView. I have the DropDownList post back and is a ControlParameter
    > for the SqlDataSource. It works great when selecting individual items
    > in the DropDownList but I want to also be able to query for all in the
    > list.
    >
    > I add an extra ListItem into the DropDownList in the Page_Load event
    > but I am not sure what to put for value.
    >
    > Example:
    >
    > SELECT * FROM [Company] WHERE Company_ID = @CompanyID
    >
    > @CompanyID is a control parameter for the DropDownList
    >
    > What value can I set for the 'ALL' list item so it would return all
    > records and basically ignore the WHERE clause.
    >
    > One idea I may try is to have another SqlDataSource that does not have
    > the where clause and change the GridView datasource on Page_Load if it
    > is set to 'ALL'...
    >
    > Thanks
    > Karl


    Hi Karl, check the following link
    http://peterkellner.net/2006/10/14/showallingridviewfromddl/
     
    Alexey Smirnov, Sep 25, 2007
    #2
    1. Advertising

  3. karlman

    karlman Guest

    On Sep 25, 2:48 pm, Alexey Smirnov <> wrote:
    > On Sep 25, 11:36 pm, karlman <> wrote:
    >
    >
    >
    >
    >
    > > Not sure if this is a SQL question or ASP.Net control question.
    > > Probably can do it either way I imagine.

    >
    > > I have a simple page that uses a SqlDataSource, DropDownList, and a
    > > GridView. I have the DropDownList post back and is a ControlParameter
    > > for the SqlDataSource. It works great when selecting individual items
    > > in the DropDownList but I want to also be able to query for all in the
    > > list.

    >
    > > I add an extra ListItem into the DropDownList in the Page_Load event
    > > but I am not sure what to put for value.

    >
    > > Example:

    >
    > > SELECT * FROM [Company] WHERE Company_ID = @CompanyID

    >
    > > @CompanyID is a control parameter for the DropDownList

    >
    > > What value can I set for the 'ALL' list item so it would return all
    > > records and basically ignore the WHERE clause.

    >
    > > One idea I may try is to have another SqlDataSource that does not have
    > > the where clause and change the GridView datasource on Page_Load if it
    > > is set to 'ALL'...

    >
    > > Thanks
    > > Karl

    >
    > Hi Karl, check the following linkhttp://peterkellner.net/2006/10/14/showallingridviewfromddl/- Hide quoted text -
    >
    > - Show quoted text -


    Interesting. I actually got this to work by having two SqlDataSource
    controls (one with WHERE and one without). Then I just added this code
    to the page_load.

    Dim li As New System.Web.UI.WebControls.ListItem("(All)",
    "-1")

    If Not IsPostBack Then
    CompanyID.Items.Insert(0, li)
    End If

    If CompanyID.SelectedItem.Value = -1 Then
    GridView1.DataSourceID = "SqlDataSource2"
    Else
    GridView1.DataSourceID = "SqlDataSource1"
    End If

    I will comapre and see which method I like the best.

    Thank you very much!
    Karl
     
    karlman, Sep 25, 2007
    #3
  4. karlman

    karlman Guest

    On Sep 25, 2:57 pm, karlman <> wrote:
    > On Sep 25, 2:48 pm, Alexey Smirnov <> wrote:
    >
    >
    >
    >
    >
    > > On Sep 25, 11:36 pm, karlman <> wrote:

    >
    > > > Not sure if this is a SQL question or ASP.Net control question.
    > > > Probably can do it either way I imagine.

    >
    > > > I have a simple page that uses a SqlDataSource, DropDownList, and a
    > > > GridView. I have the DropDownList post back and is a ControlParameter
    > > > for the SqlDataSource. It works great when selecting individual items
    > > > in the DropDownList but I want to also be able to query for all in the
    > > > list.

    >
    > > > I add an extra ListItem into the DropDownList in the Page_Load event
    > > > but I am not sure what to put for value.

    >
    > > > Example:

    >
    > > > SELECT * FROM [Company] WHERE Company_ID = @CompanyID

    >
    > > > @CompanyID is a control parameter for the DropDownList

    >
    > > > What value can I set for the 'ALL' list item so it would return all
    > > > records and basically ignore the WHERE clause.

    >
    > > > One idea I may try is to have another SqlDataSource that does not have
    > > > the where clause and change the GridView datasource on Page_Load if it
    > > > is set to 'ALL'...

    >
    > > > Thanks
    > > > Karl

    >
    > > Hi Karl, check the following linkhttp://peterkellner.net/2006/10/14/showallingridviewfromddl/-Hide quoted text -

    >
    > > - Show quoted text -

    >
    > Interesting. I actually got this to work by having two SqlDataSource
    > controls (one with WHERE and one without). Then I just added this code
    > to the page_load.
    >
    > Dim li As New System.Web.UI.WebControls.ListItem("(All)",
    > "-1")
    >
    > If Not IsPostBack Then
    > CompanyID.Items.Insert(0, li)
    > End If
    >
    > If CompanyID.SelectedItem.Value = -1 Then
    > GridView1.DataSourceID = "SqlDataSource2"
    > Else
    > GridView1.DataSourceID = "SqlDataSource1"
    > End If
    >
    > I will comapre and see which method I like the best.
    >
    > Thank you very much!
    > Karl- Hide quoted text -
    >
    > - Show quoted text -


    Actually, there was an easier answer than both. I basically just
    simplified the other option.

    Simply add an extra listitem for 'ALL' and a value of -1. Next modify
    the WHERE portion like this.

    .... WHERE (Company_ID = @CompanyID Or @CompanyID = -1)

    Works great and light on the code. Not sure why I didn't see it.

    Karl
     
    karlman, Sep 26, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Hrvoje Vrbanc

    SqlDataSource INSERT query and TextBox text

    Hrvoje Vrbanc, Dec 8, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    4,421
    Hrvoje Vrbanc
    Dec 8, 2005
  2. mharness
    Replies:
    0
    Views:
    480
    mharness
    Jan 30, 2007
  3. Paul

    SQLDataSource and Query Speed

    Paul, Jan 21, 2009, in forum: ASP .Net
    Replies:
    1
    Views:
    398
    Cowboy \(Gregory A. Beamer\)
    Jan 21, 2009
  4. Paul
    Replies:
    0
    Views:
    515
  5. RBeach1955
    Replies:
    0
    Views:
    891
    RBeach1955
    Feb 11, 2011
Loading...

Share This Page