none
TableAdapter Select Command

    Question

  • Hi,

    I have a form  that shows room availabilty for a number of hotels. I'm using a tableadapter called AvailabilityTableAdapter to fill a datagrid view.

    Is it possible to change the select command of the tableadapter programaticly to a string that I create dynamically?

    Thanks

    Mark

    Friday, December 16, 2005 11:55 AM

Answers

All replies

  • yes, tableadapter.selectcommand.text = "my new string"

     

    Happy Holidays

    Joe

    Saturday, December 17, 2005 2:37 PM
  • If i create the dataset using data designer, the table adapters are also created. But , i am not able to change the sql command of that table adapter.

    A workaround that i have found out is that i create a new untyped table adapter and a new untyped sql command in my code with the new command text that i want and attach this command to this new table adapter.

    Is this the only way? Does .NET not allow the changing of SQL data adapter commands programmatically? I have ensured that the modifier to the table adapter is public.

    Thanks!!

    Monday, February 06, 2006 9:55 PM
  •  Jose Fuentes wrote:

    yes, tableadapter.selectcommand.text = "my new string"

     

    Happy Holidays

    Joe



    A year later, but...the selectcommand property of a tableadapter is private and cannot be modified directly. You need to extend the tableadapter with a partial class. I'm trying to find a working sample that is in VB and not ASP.NET or C++ and am getting frustrated.
    Saturday, December 02, 2006 1:37 AM
  • And that's exactly my problem too. Now, my question is that, as we cannot modify the SelectCommand of the TableAdapter generated by the wizard, but the dataset generated provides a type-safe data binding, is it a good idea to use the same Dataset generated by the wizard but create a new adapter for selecting data dynamically??????
    Sunday, December 03, 2006 9:05 AM
  • I got really tired of people giving the wrong answers, or answers that do not work or are unclear, so here is the the step by step method using VB.NET

    1) Right click on the dataset in Solution Explorer and select View Code.
    2) Place the following code:

    Namespace myDataSetTableAdapters
        Partial Class myTableAdapter

            Public ReadOnly Property selectcommand() As System.Data.SqlClient.SqlCommand
                Get
                    Return Me.CommandCollection(0)
                End Get

            End Property

        End Class
    End Namespace

    Replace "My" with the name of the dataset and datatable respectively.

    In your code, you can now set the select command via the following line:

    Me.myTableAdapter.selectcommand.CommandText = "select * from table"

    It works, I have tested it, I hope it answers your question. And yes to every mofo on the MSDN forums that answered this wrong I have clicked "No" to "was this answer helpful to you?". The tableadapter selectcommand property is private and not exposed!
    • Proposed as answer by Hugo Vale Thursday, August 04, 2011 11:18 AM
    Monday, December 04, 2006 10:21 PM
  • binarysins - Excellent - Thanks.  
    Wednesday, January 03, 2007 6:23 PM
  • it works fine thank you

    iit is delightfull to find right information which works immediatly

    Friday, January 26, 2007 10:22 AM
  • Thank you sir. Appreciated.
    Saturday, January 27, 2007 3:27 AM
  •  more details

    the property code is readonly and can't be used to set the value as indicated.  the code is written fot sql server

    for odbc and read/write it looks like (dataset  : miseaugreen,tableadapter : classes,clientsproduits)

    Namespace miseaugreenDataSetTableAdapters

    Partial Class classestableadapter,produitsclients

    Public Property selectcommand() As System.Data.Odbc.OdbcCommand

    Get

    Return Me.CommandCollection(0)

    End Get

    Set(ByVal value As System.Data.Odbc.OdbcCommand)

    Me.CommandCollection(0) = value

    End Set

    End Property

    End Class

    Partial Class produitsclientsTableAdapter

    Public Property selectcommand() As System.Data.Odbc.OdbcCommand

    Get

    Return Me.CommandCollection(0)

    End Get

    Set(ByVal value As System.Data.Odbc.OdbcCommand)

    Me.CommandCollection(0) = value

    End Set

    End Property

    End Class

    End Namespace

     

     

    Saturday, January 27, 2007 9:52 AM
  • thank u ,it works fine!
    Tuesday, May 22, 2007 3:29 AM
  •  binarysins wrote:
    I got really tired of people giving the wrong answers, or answers that do not work or are unclear, so here is the the step by step method using VB.NET

    1) Right click on the dataset in Solution Explorer and select View Code.
    2) Place the following code:

    Namespace myDataSetTableAdapters
        Partial Class myTableAdapter

            Public ReadOnly Property selectcommand() As System.Data.SqlClient.SqlCommand
                Get
                    Return Me.CommandCollection(0)
                End Get

            End Property

        End Class
    End Namespace

    Replace "My" with the name of the dataset and datatable respectively.

    In your code, you can now set the select command via the following line:

    Me.myTableAdapter.selectcommand.CommandText = "select * from table"

    It works, I have tested it, I hope it answers your question. And yes to every mofo on the MSDN forums that answered this wrong I have clicked "No" to "was this answer helpful to you?". The tableadapter selectcommand property is private and not exposed!



    I know this thread is old, but its exactly what I am looking for.  I did exactly as described above, but I get "CommandCollection is not a member of __DataSetTableAdapters.__TableAdapter"

    Does anyone have this still working in VS2005 SP1?


    Wednesday, October 29, 2008 5:30 AM
  • Thank you all, it works perfectly !!!

    Adapted for MySQL :

     

     

    Namespace sensorsalesDataSetTableAdapters
    
        Partial Class SalesresultsByRefYmoinsUnTableAdapter
    
            Public Property selectcommand() As MySql.Data.MySqlClient.MySqlCommand
                Get
                    Return Me.CommandCollection(0)
                End Get
    
                Set(ByVal value As MySql.Data.MySqlClient.MySqlCommand)
    
                    Me.CommandCollection(0) = value
    
                End Set
    
            End Property
    
        End Class
    
        Partial Class SalesresultsByRefYTableAdapter
    
            Public Property selectcommand() As MySql.Data.MySqlClient.MySqlCommand
                Get
                    Return Me.CommandCollection(0)
                End Get
    
                Set(ByVal value As MySql.Data.MySqlClient.MySqlCommand)
    
                    Me.CommandCollection(0) = value
    
                End Set
    
            End Property
    
        End Class
    
    End Namespace


     


    Schneider Electric Industries France
    • Edited by J-Luc Tuesday, November 08, 2011 1:51 PM
    Tuesday, November 08, 2011 1:50 PM
  • This article more fully describes the issue, and provides for alternate solutions than just returning the entire select command from the command collection:

    http://www.codeproject.com/KB/database/TableAdapter.aspx


    The problem with changing the entire select command is that it is ugly and potentially dangerous. If you change the select clause then you could wreak havoc with the other designer generated code. This would be especially important if you had say two datagridview controls on a form (window forms) that both used the same instance of the data source say each with different filters.

    Better to do what this article says (combined with the comment by SleepyBoBos) and create a separate Fill function for when you want dynamic sql. Then you only need to worry about mucking with the select command if you are in a multi-threaded app.

    Also on that page is my contribution to the solution. It allows filters, like using the filter property of the bindingsource, on not only the WHERE clause of the query, but also the HAVING clause, and also allows you to change the ORDER BY clause, all without hacking the sql string directly. Check it out at the link above.

    • Edited by Quickdraw Saturday, December 17, 2011 2:48 AM
    Saturday, December 17, 2011 2:46 AM
  • Hey thanks so much. I have WASTED so much time and no help to tell you this was a protected property. Works PURFECTLY, thank you :)
    Wednesday, March 14, 2012 10:20 AM
  • Sorry, it doesn't work for me.

    Namespace ContactsDataSetDataSetTableAdapters
        Partial Class ContactsDataSetTableAdapter
            Public ReadOnly Property selectcommand() As System.Data.SqlClient.SqlCommand
                Get
                    Return ContactsDataSet.CommandCollection(0)
                End Get
            End Property
        End Class
    End Namespace

    Error:

    Error 1 'CommandCollection' is not a member of 'WindowsApplication1.ContactsDataSet'.

    Thursday, August 01, 2013 2:15 PM
  • To use a DataTableAdapter for custom commands you just create an INSTANCE of the DataTableAdapter instead of placing the component on the form. This is valid for table on the DataSet that contain a TableAdapter. If you need a table without a TableAdapter to fill with temporary data to fill a report contact me for examples ;P

    if your DataSet is called MyDataSet and the table Clients

    just type MyDataSetTableAdapters.ClientsTableAdapter clientsTA = new MyDataSetTableAdapters.ClientsTableAdapter();

    then just

    clientsTA.Adapter.SelectCommand = new System.Data.SqlClient.SqlCommand("sql command", Connection);

    VERY EASY! Not much people know that they have a TableAdapter namespace where all of their TableAdapter´s reside. This way you just create an instance of the DataTableAdapter you need.

    And to add rows with custom data from several sources just do this magic trick:

    MyDataSet.ClientsRow clientsRow = MyDataSet.Clients.NewClientsRow();

    type clientsRow and press the dot and feel the magic! HOPE this help you and others

    Friday, April 25, 2014 4:22 PM