How to change TableAdapter CommandText at runtime

[SWheaties]

I have a GridView, which is bound to an ObjectDataSource. The
ObjectDataSource.TypeName points to a TableAdapter, and the
SelectMethod points to one of the methods. My goal is to be able to
change the SQL statement at runtime so I don't have to write a
bazillion queries to handle all the parameters I need to pass. So,
after much searching and experimenting I was finally able to kludge my
way through to extend the TableAdapter to allow me to set a SQL command
at runtime.

Both SetSQL and SetSQL2 in the following code appear to work - the
problem now is that they work one time only. I believe the issue is
related to my setting the DataSourceID - If I don't don't set it
to null, it crashes at runtime, complaining that both DataSourceID and
DataSource cannot be set.

Thanks for any help!


Here is the code:

//----------------------------------------------------------------
// This extends the tableadapter class

namespace DataSet1TableAdapters
{
public partial class ProductsTableAdapter
{

public void SetSQL2(DataSet1TableAdapters.ProductsTableAdapter
t, string SQLCmd)
{
t.CommandCollection[0].CommandText = SQLCmd;
}

public int SetSQL(DataSet1.ProductsDataTable t, string SQLCmd)
{
this.Adapter.SelectCommand = new
System.Data.OleDb.OleDbCommand(SQLCmd, this.Connection);

if (this.ClearBeforeFill)
t.Clear();

return this.Adapter.Fill(t);

}
}
}

//----------------------------------------------------------------
// The following is called when a when the form (GridView) is loaded

// use this if calling SetSQL
DataSet1.ProductsDataTable t = new DataSet1.ProductsDataTable();
DataSet1TableAdapters.ProductsTableAdapter p = new
DataSet1TableAdapters.ProductsTableAdapter();
p.SetSQL(t,basequery);
GridView1.DataSourceID = null;
GridView1.DataSource = t;



// OR use this if calling SetSQL2
/*
DataSet1TableAdapters.ProductsTableAdapter p = new
DataSet1TableAdapters.ProductsTableAdapter();
p.SetSQL2(p, basequery);
GridView1.DataSourceID = null;
GridView1.DataSource = p.GetProducts();
*/

 

[Cor Ligthert [MVP]]

Hi,

I have had this problem to. In the now generated Strongly Typed DataSets are
all access properties placed private. The only thing I could do was cast it
as a non strongly one and than use that.

Maybe will the bottom of this sample help you.

http://www.vb-tips.com/dbpages.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

Cor