locked
TableAdapter SQL RRS feed

  • Question

  • Hello everyone,

    I had a question with regard to the new VS 2005 TableAdapters.  They allow you to build "canned" SQL Queries and remane them FillBy1 or whatever. I also see that you can pass in parameters.

    So you push a button to run the query. I don't want to do that. I want to pass a complete, already built, Query to the TableAdapter and have it run it. We generate a complete SQL query using a sophisticated search engine. I want to be able to get to the CommandText property and put in my own SQL, but I don't seem to be able to bring up and use that property.  It is literally a showstopper for us if we can't put in our own SQL.


    I have exhausted documentation and spent a lot of time searching the Web and I don't see this discussed.

    Many Thanks


    Fritz Klein
    Director Medical Education IT
    UNC Medical School
    Chapel Hill NC

    Tuesday, April 25, 2006 11:35 AM

Answers

  • Hello everyone,


    Here is the Solution to being able to run complete Queries generated at Runtime
    from a complex Search Engine via a TableAdapter.


    The key is to put your Search Engine generated runtime query into one of the "Fillby" routines. The FillBy routines exist as a collection. The Property is .CommandCollection() and the various "FillBy's that the programmer has designed, exist as an array. So your first defined Fillby would be .CommandCollection(2), the second .CommandCollection(3) etc.
    Evidently .NETS FillBy routine is .CommandCollection(1).


    First I built a partial class of the TableAdapter to extend the functionality of the
    TableAdapter. Next a Public routine MyCode() was written to expose the TableAdapter's CommandCollection. In the command collection is an item 2 which is my FillByMySQL routine.

    The Mycode() routine places my SQL into the .CommandText. Later the MyCode() can be set up to have the Search Engine SQL passed into it.

     

    Namespace BioProcDBDataSetTableAdapters
    Partial Public Class bioTableAdapter


    Public Sub MyCode()


          Me.CommandCollection(2).CommandText = "Select * FROM Bio WHERE lname = 'Smith' "


    End Sub


    End Class
    End Namespace

     

    To actually run the Query we execute as Follows:


      ' Sets the .CommandText in the FillByMySQL routine.
           Call Me.BioTableAdapter.MyCode()
      
     ' Runs the Query.
          Me.BioTableAdapter.FillByMySQL(Me.BioProcDBDataSet.bio)

     

     This seems to work nicely and solves the original problem.

     

    Fritz Klein

     

     

     

     

     

     

     

    Friday, April 28, 2006 7:24 PM

All replies

  • I have received no replies on my question so I have attempted a solution. This is a critical question for us and I would really appreciate help with it. The goal is to be able to hand the TableAdapter a complete SQL that we have built at runtime using a Search Engine. I had though that such an SQL would be placed directly in the .CommandText of the TableAdapter. 

    I have attempt some work on a solution for this but still do not know exactly what to do. I built a partial class which does expose the private property .CommandText.  I get a runtime error which says: "Object Reference Not set to an Instance of an Object"

    I am also not sure how to call the the SQL to fill the dataset once I have set CommandText.

    Namespace BioProcDBDataSetTableAdapters

    Partial Public Class bioTableAdapter

    Public Sub MyCode(ByVal SQL As String)

    Me.Adapter.SelectCommand.CommandType = CommandType.Text

    Me.Adapter.SelectCommand.CommandText = "SELECT * From Bio"

    End Sub

     End Class

    End Namespace

    Thanks so much

    Fritz Klein

     

     

     

     

     

     

     

    Thursday, April 27, 2006 5:30 PM
  • If you want to pass in your own CommandText, then why not forget the TableAdapter and just use a Command and a DataAdapter.

    Tony

    Thursday, April 27, 2006 7:20 PM
  • Hi Tony,

    Thanks for your comment.

    I wanted to use the TableAdapter because of its new helpful wizards.  Also the TableAdapter works against a strongly typed dataset.  It was  not clear to me that I could substitute the DataAdapter and have it talk directly to the dataset that the TableAdapter wizard built. If I can't reach and set the .CommantText of the TableAdapter then I will very likely have to use the DataAdapter. I am relatively new to .NET and do not know it really well so there my be some naivete here.

    Fritz

     

    Thursday, April 27, 2006 7:40 PM
  • DataAdapters were an integral part of .Net 1.x data access, but can still be used in 2.0.  Check out this link:

    http://msdn2.microsoft.com/en-us/library/zc6tc8ew.aspx

    Tony

    Thursday, April 27, 2006 7:49 PM
  • A TableAdapter is a strongly-typed equivalent of the standard DataAdapter. That means you cannot define queries dynamically.
    Friday, April 28, 2006 12:16 PM
  • Hello everyone,


    Here is the Solution to being able to run complete Queries generated at Runtime
    from a complex Search Engine via a TableAdapter.


    The key is to put your Search Engine generated runtime query into one of the "Fillby" routines. The FillBy routines exist as a collection. The Property is .CommandCollection() and the various "FillBy's that the programmer has designed, exist as an array. So your first defined Fillby would be .CommandCollection(2), the second .CommandCollection(3) etc.
    Evidently .NETS FillBy routine is .CommandCollection(1).


    First I built a partial class of the TableAdapter to extend the functionality of the
    TableAdapter. Next a Public routine MyCode() was written to expose the TableAdapter's CommandCollection. In the command collection is an item 2 which is my FillByMySQL routine.

    The Mycode() routine places my SQL into the .CommandText. Later the MyCode() can be set up to have the Search Engine SQL passed into it.

     

    Namespace BioProcDBDataSetTableAdapters
    Partial Public Class bioTableAdapter


    Public Sub MyCode()


          Me.CommandCollection(2).CommandText = "Select * FROM Bio WHERE lname = 'Smith' "


    End Sub


    End Class
    End Namespace

     

    To actually run the Query we execute as Follows:


      ' Sets the .CommandText in the FillByMySQL routine.
           Call Me.BioTableAdapter.MyCode()
      
     ' Runs the Query.
          Me.BioTableAdapter.FillByMySQL(Me.BioProcDBDataSet.bio)

     

     This seems to work nicely and solves the original problem.

     

    Fritz Klein

     

     

     

     

     

     

     

    Friday, April 28, 2006 7:24 PM
  • Very useful information. Thank you!
    Friday, April 28, 2006 8:01 PM
  •      Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you Thank you.

         This was almost a 'show stopper' for me as well, because in my situation, we don't know what the table name is going to be until run time.

         For the most part I love VB in VS, but the thing I find frustrating is that.. yeah, everything is easy to code, IF you can find the code. Finding the code is next to impossible sometimes. Here is what seems to be working for me so far in my project (but I have MUCH more to do...)

     

    ' in my form.vb under for example a 'go' button click event:
    SpecificTableAdapter.setsql("SELECT * FROM [" & tablename & "]")

    ' and at the bottom of the form.vb:


    Namespace MyDataSetTableAdapters
       Partial Public Class SpecificTableAdapter
          Public Sub setsql(ByVal thestring As String)
             Me.CommandCollection(1).CommandText = thestring
          End Sub
       End Class
    End Namespace

    Wednesday, October 25, 2006 6:02 PM
  • Hi Ryan,

    Aren't you just wonderful for your kind acknowledgement. I thank you.

    Yes this was an absolute show stopper for us.  I had to claw my way thought it all.

    I have signed up to be part of the Visual Studio Service Pack 1 group.

    I will be "Begging" them on bended knees to please include in the TableAdapter

    a sane "reachable" property so we can pass in complete queries.  As it stands now

    you an do either canned queries or parameter queries, not very useful when you have

    a Search Engine with 50 different fields. Try the permutations and combinations of 50 fields and

    you get lots and lots a parameter queries.

    So be it

    Fritz Klein

     

     

     

    Wednesday, October 25, 2006 6:38 PM
  •      Well I didn't get much further. When I try to edit the table ( by calling myTableAdapter.Update(myDataSet.mytable.GetChanges) ), I get 'concurrency error: 0 of 1 expected records affected'. I modified my setsql() sub to change the Me.Adapter.DeleteCommand.CommandText, insert and update texts, but still this problem. It works fine with the initial table, but as soon as I pull the tablecloth out from underneath, I have this problem. I'm guessing there's something else (other than the fill, fillby, and 4 command texts) in the tableadapter that has to be manually changed when you change the table it's pointing to, even though in my situation, the table layout is always the same, just the name (and data) changes. If I can't figure this out, I have to change the entire way I'm storing my data (which I can do, but it would be a lot of work). Any suggestions? (Thanks in advance for any help you can offer.)

    -Ryan.

     

    UPDATE: Never mind... it was a syntax thing. It is working now. ON to the next hurdle ... heh heh... "You tried to assign the Null value to a variable that is not a Variant data type."

     

    -Ryan.

    Monday, October 30, 2006 6:46 PM