SQLDataSource query

[karlman]

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

 

[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

Hi Karl, check the following link
http://peterkellner.net/2006/10/14/showallingridviewfromddl/

 

[karlman]

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]

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