none
Dynamically change selectcommand from tableadapter RRS feed

  • Question

  • I am trying to change the select statement that I generated by using the dataset wizard.  I am doing a search screen with a lot of search criteria and I am trying to build the where clause instead of using the Filter on the binding source.  I can see the new sql when I look at in the debug window but when it does the fill command, it goes back to the original query.  Any help would be appreciated

    Code:

    StringBuilder sqlText = new StringBuilder();
    sqlText.Append("select b.custnum, case when a.casualname is null then b.custname else a.casualname   end as custname, ordnum, ");
    sqlText.Append("c.ordstatdesc, orddate, shipdate, a.ordseq from orders a, customers b, ordstattypes c ");
    sqlText.Append("where a.custid = b.custid and a.ordstatid = c.ordstatid ");
    sqlText.Append(whereCondition.ToString());

     SqlCommand cmd = new SqlCommand(sqlText.ToString());
     this.orderSearchTableAdapter.Adapter.SelectCommand = cmd;
     this.orderSearchTableAdapter.Fill(salesDataSet.OrderSearch);

    I have also tried

     salesDataSetTableAdapters.OrderSearchTableAdapter ordsearch = new MobileSales.salesDataSetTableAdapters.OrderSearchTableAdapter();
    ordsearch.Adapter.SelectCommand = cmd;


    Tuesday, October 7, 2008 12:23 AM

All replies

  • I can of think 2 possibilities

     

    a) The TableAdapter.Fill is not using the TableAdapter.Adapter instance that you set the SqlCommand on.

        It might just be a shortcut in the your post, but where is the connection on your new SqlCommand being set?

    b) there is an TableAdapter.Adapter.RowUpdating event that is replacing your sql query.

     

    The TableAdapter is generated code, you should be able to step into

    this.orderSearchTableAdapter.Fill

    Friday, October 10, 2008 1:40 AM
    Moderator