ASP.NET Dynamic Gridview Question

[Bishop]

I have a number of simple select queries that a user needs to be able to
execute and display the data on the screen in a gridview. My thought was
that I could use a Gridview set to dymanically generate columns and bound to
a SQLDataSource. Then the user can click buttons (or whatever) and it
changes the SelectCommand property of the SQLDataSource and re-binds the
GridView.

That worked great until I enabled paging. When paging is clicked, it always
reverts back to the default SelectCommand that's embeded with the control.

Any thoughts on what I can do to make this work?

[clickon]

Have you tried using the GridView.Filter Expression propety rather than
changing the SelectCommand, this will do what you want, it uses the syntax

GridView.Filter Expression = "FieldName = Value";

You can also add parameters by using the GridView.Filter Parameters collection.

Below is some code from a page that i have written that updates the
datasource of a GridView based on which option is selected in a DropDownList
control.

ControlParamete r cprViewState = new ControlParamete r();
cprViewState.Co ntrolID = ddlSelectView.I D.ToString();
cprViewState.Pr opertyName = "SelectedValue" ;
cprViewState.Na me = "ViewState" ;
cprViewState.Ty pe = TypeCode.Int32;
SqlDataSource1. FilterExpressio n = "Status = {0}";
SqlDataSource1. FilterParameter s.Add(cprViewSt ate);

the {0} in the filter expression represents the first item in the
FilterParameter s collection. {1}, {2} etc would work for the second and
third items etc.

"Bishop" wrote:

I have a number of simple select queries that a user needs to be able to
execute and display the data on the screen in a gridview. My thought was
that I could use a Gridview set to dymanically generate columns and bound to
a SQLDataSource. Then the user can click buttons (or whatever) and it
changes the SelectCommand property of the SQLDataSource and re-binds the
GridView.

That worked great until I enabled paging. When paging is clicked, it always
reverts back to the default SelectCommand that's embeded with the control.

Any thoughts on what I can do to make this work?

[Bishop]

I had not looked into that yet. When looking at it for the sqlDataSource it
looks like it's good for changing parts of the query such as the where
clause but in this case I need to change the entire query. Although it will
always be a select query, the fields I choose and the table I query could be
entirly different.
"clickon" <cl*****@discus sions.microsoft .com> wrote in message
news:1B******** *************** ***********@mic rosoft.com...

Have you tried using the GridView.Filter Expression propety rather than
changing the SelectCommand, this will do what you want, it uses the syntax

GridView.Filter Expression = "FieldName = Value";

You can also add parameters by using the GridView.Filter Parameters
collection.

Below is some code from a page that i have written that updates the
datasource of a GridView based on which option is selected in a
DropDownList
control.

ControlParamete r cprViewState = new ControlParamete r();
cprViewState.Co ntrolID = ddlSelectView.I D.ToString();
cprViewState.Pr opertyName = "SelectedValue" ;
cprViewState.Na me = "ViewState" ;
cprViewState.Ty pe = TypeCode.Int32;
SqlDataSource1. FilterExpressio n = "Status = {0}";
SqlDataSource1. FilterParameter s.Add(cprViewSt ate);

the {0} in the filter expression represents the first item in the
FilterParameter s collection. {1}, {2} etc would work for the second and
third items etc.

"Bishop" wrote:

I have a number of simple select queries that a user needs to be able to
execute and display the data on the screen in a gridview. My thought was
that I could use a Gridview set to dymanically generate columns and bound
to
a SQLDataSource. Then the user can click buttons (or whatever) and it
changes the SelectCommand property of the SQLDataSource and re-binds the
GridView.

That worked great until I enabled paging. When paging is clicked, it
always
reverts back to the default SelectCommand that's embeded with the
control.

Any thoughts on what I can do to make this work?

[Bishop]

I found one solution that looks like it might work. Set a session variable
to the current select string, then in the form load event set the
SelectCommand to the current select string. It seems to work but isn't the
most elegent. Any other ideas on this?

"Bishop" <no****@nospam. com> wrote in message
news:e3******** ********@TK2MSF TNGP12.phx.gbl. ..

I had not looked into that yet. When looking at it for the sqlDataSource
it looks like it's good for changing parts of the query such as the where
clause but in this case I need to change the entire query. Although it
will always be a select query, the fields I choose and the table I query
could be entirly different.
"clickon" <cl*****@discus sions.microsoft .com> wrote in message
news:1B******** *************** ***********@mic rosoft.com...

Have you tried using the GridView.Filter Expression propety rather than
changing the SelectCommand, this will do what you want, it uses the
syntax

GridView.Filter Expression = "FieldName = Value";

You can also add parameters by using the GridView.Filter Parameters
collection.

Below is some code from a page that i have written that updates the
datasource of a GridView based on which option is selected in a
DropDownList
control.

ControlParamete r cprViewState = new ControlParamete r();
cprViewState.Co ntrolID = ddlSelectView.I D.ToString();
cprViewState.Pr opertyName = "SelectedValue" ;
cprViewState.Na me = "ViewState" ;
cprViewState.Ty pe = TypeCode.Int32;
SqlDataSource1. FilterExpressio n = "Status = {0}";
SqlDataSource1. FilterParameter s.Add(cprViewSt ate);

the {0} in the filter expression represents the first item in the
FilterParameter s collection. {1}, {2} etc would work for the second and
third items etc.

"Bishop" wrote:

I have a number of simple select queries that a user needs to be able to
execute and display the data on the screen in a gridview. My thought
was
that I could use a Gridview set to dymanically generate columns and
bound to
a SQLDataSource. Then the user can click buttons (or whatever) and it
changes the SelectCommand property of the SQLDataSource and re-binds the
GridView.

That worked great until I enabled paging. When paging is clicked, it
always
reverts back to the default SelectCommand that's embeded with the
control.

Any thoughts on what I can do to make this work?

[sunrei]

Bishop,

Let me know if you find another solution. I'm having the same problem and because I'm building the select statement based on the user selection of a form. Depending on the options selected, I may be querying different database tables. For now, I'm going to try to implement the solution you suggested using session variables.

Regards,