300 Data Sources... Am I doing something wrong?

In brief, here is my quandary… I have a GridView on a page with some radio buttons, a couple of check boxes, some drop downs etc. And depending on what the user selects the Grid View needs to show different columns from the database…

The easiest way I have found to do this is to have a separate Data Object for each "view", i.e. each variation of the checkboxes and radio buttons (which determine which dropdowns to use for the variables) and then bind the GridView to the specific Data Source at runtime…

It seemed like a good idea at the time, but now I have over 7 different data connections cluttering up the design view, and more to come and I feel like I am missing a better way to achieve the same results…

Does anyone have some advice for a major novice?

Here's the beginning of a large set of nested ifs, if this is the way it's done that's fine too, I'll just keep creating data sources, I just get the feeling there must be an easier way to do this..

protected void Button1_Click1(object sender, EventArgs e)

{

if (RadioButtonList1.SelectedValue == "Vendor") {

if (CheckBox1.Checked == true) {

GridView2.DataSourceID = "ObjectDataSource4";

GridView2.DataBind();

TextBox1.Text = "Search";

} else {

GridView2.DataSourceID = "ObjectDataSource1";

GridView2.DataBind();

TextBox1.Text = "Search";

}

} else if (RadioButtonList1.SelectedValue == "Store Number")

{ GridView2.DataSourceID = "ObjectDataSource2";

GridView2.DataBind();}

else {

GridView2.DataSourceID = "ObjectDataSource3";

GridView2.DataBind();

TextBox1.Text = "Search";

}

}

Any advice for a major novice would be greatly appreciated! Thanks!

Hi Maccampbell,

I don't blame you for coming up with this solution.

Its just that the VS.NET IDE team wants everything in one click that has caused you to design like this.

Well you should also know how its done the classical way.

You should have a stored procedure which would take all your related input parameters like radiobutton value etc. Based on values in the parameters you select the desired rows and pass them to the GridView.

From the gridview you just have to call the stored procedure and pass the parameters.

Check ADO.NET related articles.

Happy programming,
Anton

|||

Thanks for the response!

I have followed some of the online tutorials, and have created a Data Access Layer, which I have bound to my ObjectDataSource and then onto my GridView, and the point and click methodology works very well for using Filters, determined by a page control, what I haven't been able to find an example / tutorial about is how to use the same process but have it affect returned columns as opposed to returned results... does that make sense?

So in my Query Builder for filtering you have something like:

Select **** from **** where id = @.id

You bind the @.id to a control ex: DropDownList1.SelectedValue and this works very well... What I can't figure out how to do is:

Select Coulmn1, Column2, @.VariableColumn, from **** where id = @.id

When I try something like this I get no option to bind @.VariableColumn in the Designer, and if I try to do it on the code of the ASP Page it unbinds the ObjectDataSource from the DAL...

Is there a good tutorial out there on this? Any further suggestions? Thanks again.

Mac

|||

You can't use variables as either column names or table names.

Two approaches to this problem would be to use a dynamic query with EXEC or the equivalent stored procedure.

The other approach would involve unraveling your result like:

SELECT Column1, Column2,'LastName',LastName FROM ...

UNION ALL

SELECT Column1, Column2,'FirstName',FirstName FROM ...

UNION ALL

...

With that you can now query against it, like:

SELECT Column1,Column2,MAX(WHENFieldName=@.Column3 THEN Value ELSE '' END) AS Column3,MAX(WHENFieldName=@.Column4 THEN Value ELSE '' END) AS Column4

FROM vw_TheViewFromAbove

GROUP BY Column1,Column2

However, the second approach would be horrendously long, and slow.

|||

You could also do one of these approaches as well:

Ask for all the columns, create your gridview with all the columns bound, then selectively set the columns you don't want to visibile=false.

Databind only the key fields that don't change (Or none at all), and catch the gridview row databinding event, and fill the columns how you want in there (Much harder, but probably more efficient).

Use a combination of the above. The dynamic query with selectively setting columns to invisable or removing them should work well I would think.

|||

Motley,

Thanks very much... that would explain why I haven't seen it in any tutorials, if it can not be done!

I will attempt one of these alternate approaches instead... Thanks.