none
How to set a TableAdapter FillBy query CommandText property RRS feed

  • Question

  • In a VisualBasic2008 WindowsForms project I have a DataSet with multiple related DataTables. The DataSet was created by the DataSet Wizard based off of an SQLCE database previously setup with the DataBase Explorer tool in Visual Studio.

    Within the primary table TableAdapter I added a FillBy query so I would have a SELECT command to work with for filtering by adding a programmatically defined WHERE clause.

    For the default Fill query the command text is available as follows:

    Me.T1TableAdapter.Adapter.SelectCommand.CommandText

     

     

     

    I can also find the command text for Delete, Insert and Update query commands similarly. Yet, I am not able to find an Adapter property for the added FillBy command.

    In the DataSet.Designer.vb I can see that the FillBy command is added to a CommandCollection property of the TableAdapter but trying the following in the main form code -

    Me.RidesTableAdapter.CommandCollection(1).CommandText = ...

     

     

     

     

    - errs stating CommandCollection is unavailable since it is declared as Protected in DataSet.Designer.vb.

    The question is, how do I access the command text of the FillBy query I added for this purpose?

    Thanks,
    Eric

    Tuesday, March 9, 2010 4:22 AM

Answers

  • OK, now I have three replies in a row.

    What I have now appears to be working.

    This can be considered the answer if the answer to "How do you access the CommandText property of the FillBy query in the default TableAdapter" is "You can’t".

    I have the following in the form prior to the declarations.

    Imports System.Data.SqlServerCe

    I have the following in the declarations section of the form.

    Dim SelectCmdTxt As String = "SELECT * FROM T1"
    Dim fT1TableAdapter As New SqlCeDataAdapter

    I have the below in the procedure that generates the filtered view which is subsequently applied as the table BindingSource.DataSource.

    Dim fT1Table As New DBDataSet.T1DataTable
    Dim fSelectCmdTxt = SelectCmdTxt & " WHERE T1ID > 1"
    Using DBconn As New SqlCeConnection(My.Settings.DBConnectionString)
        fT1TableAdapter.SelectCommand = New SqlCeCommand(fSelectCmdTxt, DBconn)
        fT1TableAdapter.Fill(fT1Table)
    End Using
    SelectedFilter.View = fT1Table.AsDataView
    

    This allowed me to prove out a method of applying an SQL WHERE string as a filter.

    Next I will code the generation of the WHERE string from the user’s inputs (not posted).

    Eric


    • Marked as answer by Eric-67220 Thursday, March 11, 2010 7:43 PM
    Thursday, March 11, 2010 7:40 PM

All replies

  • Zeigon,

    Thanks for the well targeted references.
    I'm fairly familiar with creating a query in the designer to add to the TableAdapter and with parameterized queries and hope I haven't passed over the information I'm looking for in those references.

    What I would like to have access to is the command text of the FillBy query so I can build the SELECT statement with a WHERE clause for filtering programmatically. I'm building a filter builder that the user can use to focus on a subset of the data. The records contain multiple data types including DateTime that the user may want to specify in various periods like month, quarter, year . . . I also want my filter builder to be independent of the actual data so if I want to use this feature in another project the filter builder just needs to have access to the table and can gather all the column names, data types and relations from there. The output of the filter builder is a WHERE string. So, I don’t believe there is a predefined query or set of predefined parameterized queries to be built to handle the design intent.

    I started off with the notion of using the .Filter property of the BindingSource but was foiled by the lack of Date functions for the construction of date related periods like DatePart("q",DateColumn). There is a separate thread on that which proposed use of LINQ commands or methods yet these don’t yield the flexibility of generating a WHERE string that can be appended to a SELECT query. Also, using a query to fill the DataSet helps limit the amount of data that needs to be read from the DB.

    So, my question still stands. How do I get access to the CommandText of the FillBy query. This seems like a reasonable thing since CommandText is exposed as a property of Adapter for the Fill (Select), Insert, Update and Delete commands of the TableAdapter.

    Alternative question:

    Is there a way to add and remove TableAdapter queries at run time.

    New Information:
    I thought I was on to something when I ran across this:
    http://msdn.microsoft.com/en-us/library/bh8kx08z.aspx
    But when I try declaring an object as SqlDataAdapter in form level code I get -
    Type 'SqlDataAdapter' is not defined.
    I tried adding Import statements for System.Data and System.IO but this didn't help.
    What am I doing wrong?


    Thanks,
    Eric

    • Edited by Eric-67220 Tuesday, March 9, 2010 9:26 PM New information
    Tuesday, March 9, 2010 5:32 PM
  • Hi Eric,

    I see what you are trying to achieve but I still recommend the same method if you want to stick to a TableAdapter. What I was thinking but forgot to mention is that you could create with the query designer either a method that accepts as parameters all the fields that you want to filter by, or different versions of methods with different filter parameters. The second option would be sensible if we were talking about only using a few filter parameters, but I don't think that's the case.
    For a method with all the parameters you could easily pass default values for those parameters that you don't want to filter by.


    About LINQ, I've seen examples of dynamically generated Where clauses that looked pretty simple. And you can always use a DataContext and the deferred execution feature to effectively use the Where clauses on the database directly (instead of on the cache) to avoid retrieving all the records of a table, as you noted.


    Now, if you want to use a SqlDataAdapter everything gets even easier since the query here is created in the "traditional" way.
    The SqlDataAdapter class belongs to the SqlClient namespace: System.Data.SqlClient, that's what you were missing.

    You can check this article if you're not sure about how to use the SqlDataAdapter, DataSet and DataTable classes: http://msdn.microsoft.com/en-us/library/bh8kx08z.aspx

    Wednesday, March 10, 2010 12:36 PM
  • Zeigon,

    Thanks for hanging in there with me. Your insight is appreciated.

    I’ve attempted my first use of an SqlDataAdapter but am running into an issue. I’m sure I’m missing something in setting things up to use the adapter.

    As background, the project is already using the DataSet and TableAdapters built by the Wizard in VisualStudio.

    What I’ve added to use the DataAdapter is as follows:

    In the form Declarations section:


    Dim SelectCmdTxt As String = "SELECT * FROM T1"
    Dim fT1TableAdapter As New SqlDataAdapter
    Dim DBconn As SqlConnection = New SqlConnection(My.Settings.DBConnectionString)
    


    In the procedure where I create a filtered DataView of one of the Tables in the DataSet:


    Dim fT1Table As DBDataSet.T1DataTable = DBDataSet.T1
    Dim fSelectCmdTxt = SelectCmdTxt & " WHERE T1ID > 1"
    Dim fSqlCommand As SqlCommand = New SqlCommand(fSelectCmdTxt, DBconn)
    fT1TableAdapter.SelectCommand = fSqlCommand
    Using DBconn
        fT1TableAdapter.Fill(fT1Table)
    End Using
    SelectedFilter.View = fT1Table.AsDataView
    


    The above procedure gets the following error after a brief timeout on the .Fill statement.

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    In the debugger I can see that there is a connection defined (it is not null and many of the properties are filled in).

    Any idea on what I’m doing wrong here?

    Thanks,
    Eric






    Wednesday, March 10, 2010 6:38 PM
  • This look like a connect issue rather than the query issue. You should use the same connection with normal query to test out and confirm that this is not because the code you have here.

    John
    John Chen -- See my team blog: http://blogs.msdn.com/vsdata. All my posts are provided "AS IS" with no warranties, and confer no rights.
    Thursday, March 11, 2010 6:58 AM
  • Yes, it looks like the connection string is not correct. Check the values of the parameters in it, specially the server name. If you think they look correct try to use them to connect to the server with sql server management studio.
    Thursday, March 11, 2010 9:43 AM
  • Thanks John and Ziagon,

    I agree it looks like a connection issue.

    I'm wondering what could be wrong with the connection since the string came from My.Settings as shown above.  The rest of the application has no trouble getting to the DB although that is all through design time elements defined by the Wizard that showed up in the ToolBox.

    I'm using Visual Studio Express on an XP Professional 32bit machine and the DB server is SQL Server 2008 CE on this same machine.

    Added information:
    Following are some of the salient items from the connection after the error has occurred.

    ConnectionString = "Data Source=|DataDirectory|\DB.sdf"
    Database = ""
    DataSource = "|DataDirectory|\DB.sdf"
    ServerVersion = {"Invalid operation. The connection is closed."}
    ---Within ServerVersion
    ---StackTrace = "   at System.Data.SqlClient.SqlConnection.GetOpenConnection()    at System.Data.SqlClient.SqlConnection.get_ServerVersion()"
    Site = Nothing
    StatisticsEnabled = False

    I also trapped prior to the exception and the connection information shown by the debugger looked the same.

    Eric
    • Edited by Eric-67220 Thursday, March 11, 2010 2:16 PM added information
    Thursday, March 11, 2010 1:17 PM
  • Why don't you check the application's data directory (where DataDirectory is pointing) to see if the DB is really there. Copied from another post in the forums :
    --
    To set the DataDirectory property, call the AppDomain.SetData method. If you do not set the DataDirectory property, the following default rules will be applied to access the database folder:
    For applications that are put in a folder on the user's computer, the database folder uses the application folder.
    For applications that are running under ClickOnce, the database folder uses the specific data folder that is created.

    --
    Also, notice that you have 2 Data Source parameters in the connection string. The correct one (according to this in MSDN ) is "Data Source" with a space in between.
    Thursday, March 11, 2010 2:44 PM
  • Zeigon,

    Thanks again for taking the time to think about this and give helpful suggestions.

    I added a statement to assess the AppDomain object in the procedure prior to the statement which causes the error then set a break point so I could examine it using the debugger.

    Dim AD = AppDomain.CurrentDomain

    There is no DataDirectory property listed so the default must by in play as documented by the MSDN text you posted.
    I am running in debug mode so this is not an installed application.
    What I have found is that the .sfd file is copied to the debug directory for use in debug mode and this is where the BaseDirectory of the CurrentDomain object is pointing.

    BaseDirectory = "C:\Documents and Settings\Eric\My Documents\Visual Studio 2008\Projects\P1\P1\bin\Debug\"


    The .sdf file is indeed there.

    I think I'm probably just missing something very basic as to date I've only used the data objects created for me in the Data Sources window or Toolbox.  Explicitly declaring an instance of a DataAdapter is new to me.

    I also notice there is no DataProvider listed in the connection string whereas books I have on ADO.Net usually include a data provider clause in the string.  If one is needed to specify SQL Server CE 3.5 why is it not present in My.Settings.ConnectionString?  I don't know what the format of that clause in the string would be for SQL Server CE.

    Relative to the comment about Data Source with a space, the confusion is probably coming from the way text is displayed in this editor.
    I pasted seven properties and their values copied from the debugger's viewer which appears when the mouse pointer is hovered over an object name.
    In the string value for the ConnectionString property (the first property listed) there is a space between Data and Source.  Two properties later there is a property named DataSource with no space in the name.  Also note that I did not create the connection string manually.  It was created using the Visual Studio tools for adding a DataSource to the project and those tools saved the connection string in My.Settings.DBConnectionString.

    Eric

    • Edited by Eric-67220 Thursday, March 11, 2010 4:54 PM missing word string
    Thursday, March 11, 2010 4:51 PM
  • Sorry for two replies in a row.

    Did you know there ate two different namespaces and two different sets of objects for SQLClient and for SQLServerCE? I examined the code in DataSet.Designer.vb and saw that it creates an SQLCeConnection and instances of SQLCeDataAdapter (s) for each table.

    So, I added an imports System.Data.SQLServerCE statement at the top of the form code then changed the type of the connection, adapter and command objects to their respective CE versions and it runs. At the moment however, it appears to select all rows so the WHERE clause doesn’t seem to be working.

    All of this would be unnecessary if I could get to the CommandText property of the FillBy query I added to the already existing TableAdapter. Could someone on the forum please answer that question even if the answer is no, it can’t be done.

    Thanks,
    Eric

    Thursday, March 11, 2010 5:47 PM
  • OK, now I have three replies in a row.

    What I have now appears to be working.

    This can be considered the answer if the answer to "How do you access the CommandText property of the FillBy query in the default TableAdapter" is "You can’t".

    I have the following in the form prior to the declarations.

    Imports System.Data.SqlServerCe

    I have the following in the declarations section of the form.

    Dim SelectCmdTxt As String = "SELECT * FROM T1"
    Dim fT1TableAdapter As New SqlCeDataAdapter

    I have the below in the procedure that generates the filtered view which is subsequently applied as the table BindingSource.DataSource.

    Dim fT1Table As New DBDataSet.T1DataTable
    Dim fSelectCmdTxt = SelectCmdTxt & " WHERE T1ID > 1"
    Using DBconn As New SqlCeConnection(My.Settings.DBConnectionString)
        fT1TableAdapter.SelectCommand = New SqlCeCommand(fSelectCmdTxt, DBconn)
        fT1TableAdapter.Fill(fT1Table)
    End Using
    SelectedFilter.View = fT1Table.AsDataView
    

    This allowed me to prove out a method of applying an SQL WHERE string as a filter.

    Next I will code the generation of the WHERE string from the user’s inputs (not posted).

    Eric


    • Marked as answer by Eric-67220 Thursday, March 11, 2010 7:43 PM
    Thursday, March 11, 2010 7:40 PM
  • Your solution is fine. But I wanted to solve the problem relying solely on the 'native' tableadapter capability.

    So, it is possible to retrieve and set the FillBy Command text for any of the FillBy statements in a tableadapter. Here is how:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/b2ebf1fb-f54c-46bc-bd07-669f37c066e4
    • Proposed as answer by DreamU Thursday, March 18, 2010 5:37 PM
    Thursday, March 18, 2010 5:02 PM
  • Your solution is fine. But I wanted to solve the problem relying solely on the 'native' tableadapter capability.

    So, it is possible to retrieve and set the FillBy Command text for any of the wav FillBy statements in a tableadapter. Here is how:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/b2ebf1fb-f54c-46bc-bd07-669f37c066e4

    It's very useful, Now I have a more clear idea about it, I just start to learn it.
    Thursday, August 12, 2010 1:31 AM